NEED HELP GETTING A TOTAL COUNT OF RECORDS OFF MULTIPLE TABLES

[HerickP]

Hi,<br><br>I have this database ( hi elizabeth, its me again) which I got multiple tables holding same type of info, except that each table is particular to a vendor. on each table, there is a field for a location where a Po # was shipped to, and since I have many tables, I cant figure out a way to find totals ( just a total count) for the POS in all tables....I could figure a total count for each table, but since I have many, is there a formula or code where I can get a total count for all table together???? and if possible, on each table there is the a date field, could I have the total count by&nbsp;&nbsp;a date range, like a week????? Thanks guys!!!

 

[kathryn]

Are your tables all of the exact same design?&nbsp;&nbsp;Same fields in the same order, although not necessarily exact same field names?&nbsp;&nbsp;You can do a union query (make sure to use the ALL keyword) and then do your counting/grouping from there.<br><br>Let me know if you need more info.

 

[HerickP]

How to do a Union Query??? Yea, All fields are exactly the same, just the tables name and data inside are different....Tahnks!!

 

[kathryn]

Open a new Query, in design view, but don't add any tables.&nbsp;&nbsp;Then go to SQL view and type the following:<br><br>****Begin SQL****<br>Table yourtable1<br><br>Union All<br><br>Table yourtable2<br><br>Union all<br><br>Table yourtable2&nbsp;&nbsp;etc....<br>****End SQL****<br>Don't forget to use the semicolon to end the SQL<br><br>If you only want a few fields for your counting grouping, use the following syntax<br><br>****Begin SQL****<br>Select PONumber, PODate from yourtable1<br><br>Union All<br><br>Select PO#, PODate from yourtable2<br><br>Union all<br><br>Select Field1, Field5 from your table3 etc...<br>****End SQL****<br><br>Access will give the resulting fields the names from your first table, in this case PONumber, PODate.<br><br>I'm guessing that you don't want the standard lecture on database design, telling you that all these tables should be one table with a field to identify the vendor???<br><br>Hope this helps.<br><br>Kathryn<br><br><br>

 

[HerickP]

I got a message &quot;The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect&quot;... There is a SELECT; on the top of the SQL view window, it was there when I first tried to design the query.....how can I spelled the statement right??

 

[kathryn]

You need to remove the first SELECT;<br><br>

 

[HerickP]

I did, then I get an error, how to end the SQL statement?

 

[elizabeth]

Hi HerickP, were my directions for combining the tables into one too difficult to follow? Maybe you could repost on that thread and see if someone could come up with a simpler method.

 

[kathryn]

Hi,<br><br>If you post the SQL you are using, I'm sure that we will see the problem.<br><br>Kathryn