Tracking time spent on customers records based on date of entry (1 Viewer)

marlind618

Registered User.
Local time
Today, 12:03
Joined
May 22, 2013
Messages
15
I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waiste. Thainks for any help,.
 

gblack

Registered User.
Local time
Today, 19:03
Joined
Sep 18, 2002
Messages
632
Yes, you should be able to do this... I think your issue might be how you're grouping the data. Don't use distinct... use Group By.

No sure how much you know about MS Access queries and the GUI... You need to use the totals button (found on the design ribbon) of your query, in design mode.

When you click this, a totals row will appear in the critera area below... In that totals row, click the dropdown underneath whatever column you are using to get your hours worked and choose: Sum

Your "Station" field (i.e. the field that has ABC and CBS in your example) should already be listed as Group By in the totals row (group by is the default for all yoru fields...

At the start, make sure you only have these two tables in your query... if you add fields, your query will sum your Hours field based upon all the other fields in the query you are grouping by... which may lead to you getting those 6 rows again... So for starters just put those two fields in the query...

This should sum your totals row for each station (ABC, CBS, NBC... whatever) in your table.

HTHs,
Gary
 

marlind618

Registered User.
Local time
Today, 12:03
Joined
May 22, 2013
Messages
15
Gary, Thanks will try this today.
 

marlind618

Registered User.
Local time
Today, 12:03
Joined
May 22, 2013
Messages
15
Gary, Thanks for your tip. I don't know how I missed that.
 

Users who are viewing this thread

Top Bottom