Total Queries

  • Thread starter Thread starter John Parkinson
  • Start date Start date
J

John Parkinson

I am trying to build a totals queries from two tables.
Table1 has LABORCOST and EQUIPCOST fields that I want
totals for. Table2 has MATERIALCOST. When I try to add
these two tables in a totals queries and SUM the
individual cost, I am getting some outlandish numbers.

Please note that these tables were imported dBaseIV files
and they have a common field of ROADNUMBER.

I can do a total query on each table that works.

Thanks in advance.

John
 
You're on the right track.

1. Make a totals query for each individual table that
gives you an ID number and a total cost (labor/equip).
2. Make a third query that has the two total tables,
linked on the record ID numbers and reports the ID, total
cost from table 1 and total cost from table 2.
3. Edit the SQL statement to add the keyword DISTINCT.
Like this:

SELECT DISTINCT qry1.id, qry1.SumOfsales, qry2.SumOfsales2
FROM qry1 INNER JOIN qry2 ON qry1.id = qry2.ID;

qry1 and qry2 are my two total queries and ID is the ID
field. This would return results like:

id SumOfsales SumOfsales2
1 $8,213.00 $5,710.00
2 $821.30 $7,200.00

Hope this helps you.

Bob Tulk
MOUS (97/XP) Expert
 
Thanks Bob,

I had the total query working but didn't have the SQL
statement. I will give that a try.

John
 
Back
Top