Mulitple Queries into 1

[vqcheese]

I have 10 queries, everyone has the same exact fields. Each one is named qry_0-100000, qry_100000-200000, ect all the way up to 1000000.

In each query i have trucknum, sumofparts, sumoflabor. What its doing is summing up all the parts and labor costs for each truck in the specified range of miles. What i am trying to do is create a query to pull all those amounts together for each truck, for each range of miles, and its not working for me. Please Help.

Thanks
Brian
Valley Queen Cheese Factory, Inc.

 

[DCrake]

Do a search on Union queries. This type of query allows you to sit one table on the top of another like pancakes. By using the matching columns from each table you can then interrogate the entirity in one go.

CodeMaster:

 

[vqcheese]

All i am finding is union queries where you union by table name, nothing about union multiple queries, can you give me a example please.

 

[DCrake]

queries are similar to table in the way they are presented. The major difference is that you can include calculated fields apply criteria, sort differently, etc. Once saved you can treat them in much the same way you would treat a table, with exceptions.

So lets say you have 3 queries and one table.

Table1

fld1
fld2
fld3
fld4

Query1
fld2
fld3
fld4

Query2
fld2
fld3
fld4

Query3
fld2
fld3
fld4

You could now make a union query out of all four objects

Select fld2, fld3, fld4, "Table1" As Source From Table1
Union Select fld2, fld3, fld4, "Query1" As Source From Query1
Union Select fld2, fld3, fld4, "Query2" As Source From Query2
Union Select fld2, fld3, fld4, "Query3" As Source From Query3
Order by fld2

When you run the query
You will get a datasheet of records with 4 columns, the data from each column will appear on each row and the last column will tell you which table or query the source came from.

This is aircode and as such has been coded for brevity.

CodeMaster: