query idiot needs help (1 Viewer)

[lipin]

I am trying to query an attendance table. There are the following fields:
Emp #
Date
M/T (for Missed Time)
NS (for Non-Scheduled)
LOA (for Leave)
Total (calc. field-adds up the 3 above)

In the table there are 5 records for each Empl #. I would like to show only 1 record, showing Empl# and total, so I put Group By(it's under all fields) and then SUM under Total Field.
When I run query I get all 5 records.

Please enlighten me. Thank you.

 

[ColinEssex]

Thats probably because each date is different.

Col

 

[lipin]

You are absolutely right. Thanks.

Is there a simple way to show that weekly total? I would just delete the date field, but I need it so I can put in date parameters and pick the week I need out of the table.

 

[ColinEssex]

If you "untick" the little box on the query grid you can still use it as criteria but it will not be shown. You can then do the "Between Date1 and Date2" in the criteria to get a weekly total

Col

 

[Fizzio]

To extend on Col's answer, in the Date field, change the total to Where to pick from a range of dates. The Group By will pull each individual item for that field. I would, however, consider changing the name of your fields. Emp # to EmpID, Date to AttDate and M/T to MissedTime to avoid problems
1. Avoid using characters if possible eg #,/. etc
2. Date is a function used by Access so naming a field Date can throw up all sorts of unexpected results.
3. In general, do not store info that you can calculate in a form/report - use a calculated control instead UNLESS you need to keep an audit trail of previous totals.

HTH

 

[ColinEssex]

Thanks Fiz - I forgot about the "where" bit

Col