displaying total in just one row

  • Thread starter Thread starter OTWarrior via AccessMonster.com
  • Start date Start date
O

OTWarrior via AccessMonster.com

I have a query that takes the dates that people have worked, counts the
ammount of work done each day, and then total all of that data.

Ideally (as I am exporting to excel), I would like to just show the total
once, or just at the end of the count (like an "=sum(a1:b20)" in excel)

is this possible?

sql code below

SELECT qry_EXP_ContractsReceivedDaily.dteDateDocReceived,
qry_EXP_ContractsReceivedDaily.CountOfnBookingID,
qry_EXP_ContractsReceivedDailyTOTAL.SumOfCountOfnBookingID
FROM qry_EXP_ContractsReceivedDaily, qry_EXP_ContractsReceivedDailyTOTAL;
 
First thing first: I don't see any joins between the tables/queries in the
SQL statement. This will cause a Cartesian join which usually is a mistake.
You need a join between them or else you could be creating thousands if not
millions of bogus records. Is there a line between the tables in the query
design QBE grid? There needs to be. In an SQL statement there would either be
the word "JOIN" between the tables or a WHERE clause that joins at least one
field in each table.

After that problem is fixed, open the query in design view. Go up to View,
Totals. You should see a new line in the QBE grid that has Group By. Change
CountOfnBookingID and SumOfCountOfnBookingID to Sum.
 
Back
Top