SQL String for union query on dynamic number of tables
in [Help]
|
Prev: Duplicate a sql case statement in Access
Next: using fsubNavigation form with only setting record source at gotfocus
From: Robin Riviere on 27 Apr 2010 14:43 On Apr 27, 1:55 pm, Robin Riviere <deltacompan...(a)gmail.com> wrote: > On Apr 27, 1:45 pm, Rich P <rpng...(a)aol.com> wrote: > > > > > Hi Robin, > > > I can kind of see what is going on here - in a general sense. You are > > trying to perform operations in an Integrated developement environment > > (Access) that would be better suited for a non integrated development > > environment (.Net). In the past I used to refer to this as enterprise > > operations in a non enterprise system (Access). But I think that > > Integrated vs Non Integrated development environment is more accurate. > > Anyway, it looks like you are trying to develop a sql string using the > > "Union" operator. > > > I am perceiving that you want to write a query that would look something > > like this -- general format: > > > select fld1, fld2, fld3, ... from tbl1 union all > > select fld1, fld2, fld3, ... from tbl2 union all > > select fld1, fld2, fld3, ... from tbl3 union all > > .. > > > I am guessing that the field names are all the same but that table names > > is where you are having your issue. Again, loop through the tabledef > > collection to get the names of your linked tables. YOu could even store > > these table names in a local table and then loop through this table to > > add the table names to your sql string: > > > Dim strSql As String, str1 As STring, str2 As String > > Dim DB As DAO.Database, RS As DAO.RecordSet > > Set DB = CurrentDB > > Set RS = DB.OpenRecordset("Select tableName From LocalTbl") > > strSql = "" > > str2 = "Select fld1, fld2, fld3, ... From " > > Do While Not RS.EOF > > str1 = RS!TableName > > RS.MoveNext > > If Not RS.EOF Then > > strSql = strSql & str2 & str1 & " Union All " > > Else > > strSql = strSql & str2 & str1 > > End If > > Loop > > > As for the non integrated thing, Querydefs work best in the integrated > > environment. For your purposes, I wouldn't use querydefs because your > > are working with tables that aren't local to your DB. It can be done, > > but you will end up with a bunch of spaghetti code to make it work. I > > say this respectfully. > > > Rich > > > *** Sent via Developersdexhttp://www.developersdex.com*** > > Rich, > > You summed it up perfectly. Let me play with what you've posted, and > I'll reply back with my results. > > Thanks for taking the time to help. > RR Rich and everyone else who replied, thanks. I got it to work. I really appreciate your help. The only annoyance is that the union query returns a blank record as the first record of the query. Any idea what this might be attributed to? RR
From: Rich P on 27 Apr 2010 16:13 > The only annoyance is that the union query returns a blank record asthe first record of the query. Any idea what this might be attributed to? RR < try hand writing your query. And it if is using a ton of tables then try hand writing with the just the first three or four tables and see what you get. If that does not cause a problem then try the next batch of tables until you see the problem occur again. If you don't see the problem, then the issue is in how you are programmatically generating your sql string. If you are still using the querydefs, then that could also be the issue. Rich *** Sent via Developersdex http://www.developersdex.com ***
From: Robin Riviere on 28 Apr 2010 11:22
On Apr 27, 4:13 pm, Rich P <rpng...(a)aol.com> wrote: > The only annoyance is that the union query returns a blank record asthe > first record of the query. Any idea what this might be attributed to? > > RR > < > > try hand writing your query. And it if is using a ton of tables then > try hand writing with the just the first three or four tables and see > what you get. If that does not cause a problem then try the next batch > of tables until you see the problem occur again. If you don't see the > problem, then the issue is in how you are programmatically generating > your sql string. If you are still using the querydefs, then that could > also be the issue. > > Rich > > *** Sent via Developersdexhttp://www.developersdex.com*** Rich, Thanks for the idea. I ran several "hand written" union queries across many combinations of tables and discovered that any combination of more than one table in the query produced the extra row as the first record of the query. However, if I only included one table (therefore a simple select query) no extra row was returned. My syntax was as follows: SELECT Table1.* FROM Table1; UNION SELECT Table2.* FROM Table2; Any ideas? Thanks, RR |