union SQL programming

[SOS]

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

--

 

[Douglas J. Steele]

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.

 

[SOS]

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

 

[Douglas J. Steele]

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).

 

[SOS]

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?

 

[Douglas J. Steele]

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>

 

[SOS]

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?

 

[Douglas J. Steele]

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

 

[SOS]

Thank you very much. I will try it.
--

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