 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / November 2006union SQL programming
Hi, Please help me for this query stuff. I have 3 different tables. I use query for each table to populate some fields so that 3 tables can have same fields. I use an union query to put 3 queries together. In access front end, user enter the critera on the fly. I put the critera in a string (strMyWhere). Then I apply the critera to the union string. The code is as below. qryUnion3 will be used in a form. .... select field1,field2.. from qryOne Union All select field1, field2.. from qryTwo Union All select field1, field2....from qryThree order in field1 intX=Dcount(id, qryUnion3,strMyWhere) Now, my questions are 1. If I put the critera in the qryOne, qryTwo, qryThree first before I union them, will make the program run faster? 2. if so, how do I do this? I should put the qryUnion3 in the code, right? How about the form whose data is coming from qryUnion3? Thanks for help -- Depending on what the criteria are (and whether the fields are indexed, what the cardinality of the fields are and how many rows are in the table), putting criteria in the individual queries might be slightly faster. You could try dynamically changing the SQL for qryUnion3 to accomplish this: Dim qdfCurr As DAO.QueryDef Dim strSQL As String If Len(strWhere) > 0 Then strSQL = "select field1,field2.. from qryOne " & _ "WHERE " & strWhere & " UNION ALL " & "select field1,field2.. from qryTwo " & _ "WHERE " & strWhere & " UNION ALL " & "select field1,field2.. from qryThree " & _ "WHERE " & strWhere Else strSQL = "select field1,field2.. from qryOne " & _ " UNION ALL " & "select field1,field2.. from qryTwo " & _ " UNION ALL " & "select field1,field2.. from qryThree " Else Set qdfCurr = CurrentDb.QueryDefs("qryUnion3") qdfCurr.SQL = strSQL Alternatively, you'd have to use the approach above to change the SQL for each of the 3 queries.  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi, > Please help me for this query stuff. [quoted text clipped - 20 lines] > How about the form whose data is coming from qryUnion3? > Thanks for help Thank you very much. I will try it. Besides using UNION to get the 3 tables together, is there any other way to do it? It is because I found out the union query used in the form will make the form protected. It works for the view function but not work for the maintenance function. In maintenance function, user can update the information in the form. I thought of creating a new table from these 3 tables for maintenance function. So when user update data on form, it will also update the new table but it won't update the old 3 individual tables. Am I right? Is there any better way to do this so I can cover both view and maintenance functions at the same time? Thanks in advance Signature> Depending on what the criteria are (and whether the fields are indexed, what > the cardinality of the fields are and how many rows are in the table), [quoted text clipped - 51 lines] > > How about the form whose data is coming from qryUnion3? > > Thanks for help Basically, you're right. A Union query is, by its nature, read-only. What I sometimes do is spawn a new form to allow me to work with the exact record of interest (in other words, the form that's bound to the Union query shows everything, but to change anything, you have to pop up a new form that shows just that one record to be changed). SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Thank you very much. I will try it. Besides using UNION to get the 3 > tables [quoted text clipped - 72 lines] >> > How about the form whose data is coming from qryUnion3? >> > Thanks for help Thank you. It sounds good. but I am not sure how to program it. Do I have to read this specific record one more time in the original table when the new form pop up? So that when user make any update, I can update the original table. Is it correct? > Basically, you're right. A Union query is, by its nature, read-only. > [quoted text clipped - 79 lines] > >> > How about the form whose data is coming from qryUnion3? > >> > Thanks for help That's one approach. Pass the ID of the current record as a filter when opening the second form. Another approach is to have the second form unbound. Open the form, then have the first form transfer the appropriate values to it. When you're closing the second form, check whether anything changed, and write an Update statement if so. The first approach is probably easier. <g> SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Thank you. It sounds good. but I am not sure how to program it. Do I have > to [quoted text clipped - 95 lines] >> >> > How about the form whose data is coming from qryUnion3? >> >> > Thanks for help Thank you very very much. Unfortunately, my client doesn't like the pop up 2nd screen. He wants to see the whole form open for update. In other words, he wants to update more than one records at the same time. In this way, do you have any good suggestion? > That's one approach. Pass the ID of the current record as a filter when > opening the second form. [quoted text clipped - 105 lines] > >> >> > How about the form whose data is coming from qryUnion3? > >> >> > Thanks for help Change clients? <g> I think your only options would be to limit them to being able to update only one of the 3 queries at a time, or else store the results of the union query in a temporary table, then do all sorts of complicated manipulations when they close the form to ensure the the correct table(s) get updated (in other words, not a great solution). BTW, if you are going to use a temporary table, consider storing it in a temporary database, rather than your existing one, so that the database doesn't grow as quickly. Tony Toews talks about this at http://www.granite.ab.ca/access/temptables.htm SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Thank you very very much. Unfortunately, my client doesn't like the pop > up [quoted text clipped - 132 lines] >> >> >> > How about the form whose data is coming from qryUnion3? >> >> >> > Thanks for help Thank you very much. I will try it. Signature> Change clients? <g> > [quoted text clipped - 145 lines] > >> >> >> > How about the form whose data is coming from qryUnion3? > >> >> >> > Thanks for help |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.