Return minimum value across fields

[Guest]

I have a query that references 4 tables with the same format data in them
(ie. deliveries from 4 locations - 1 per table) with fields Item, Quantity,
Delivery Date. Each table can contain the same item or different ones. I
need a query to return a total list of unique Items with the sum of Quantity
and Minimum Delivery Date, ie. Item, total quantity, first delivery.

How do I do this?

 

[Guest]

Sounds like you need a UNION ALL query and then a totals query.

 

[Allen Browne]

The best solution will be to create one new table, containing all the data
from the 4 existing ones. You will probably need one extra field to indicate
which location the record was for. Use an Append query to copy the records
from the existing tables to the new one.

It is possible to create a UNION query that combines the data from the 4
tables into one result set, but that will be considerably less efficent to
exeucte, the result will be read-only, and you have to write the SQL
statement yourself since the graphical query view doesn't handle UNION
queries.

Once you have all the records in the one table, you can use a Totals query
to group by the item, sum the quantity and select the Min delivery date.
Just depress the Total button on the toolbar in query design.

 

[Guest]

Worked a treat, thanks

Sounds like you need a UNION ALL query and then a totals query.