Merge Records in Query

[mous]

I have the following table:-

Staff Name, Day, Hrs, Wk1, Wk2, Wk3, Wk4 Total Hrs

Bob, Fri, 2, 1, 1, 0, 0 4
Bob, Fri, 2, 0, 1, 1, 0 4
Bob, Mon, 3, 0, 0, 0, 1 3

Currently this add up that bob is working 4 hours in wks 1 and 2
and 4 hours in wks 2 and 3 and 3 hours in wk 4 resulting in a total of 11 hours.

I want to be able to merge the records to this:

Bob, Fri, 2, 1, 1, 1, 0 6
Bob, Mon, 3, 0, 0, 0, 1 3

and get a total figure of 6 hours in wk 1, 2 and 3 and 3 hours in week 4 totalling: 7

Is this something I can do in query?

 

[KenHigg]

Do a totals query and group on name and week and then sum totals.

Would this work...
kh

(How long have you been collecting data in this db, seems you made need to normalize a bit.)

 

[mous]

If I sum totals I receive a 2 in week 3 I only want 1 thus the need to merge not sum. Thanks anyway.

 

[KenHigg]

The quick way to do this would be to do the first query then use it as a source for a second query. In the second query replace each wk1, etc with something like:

NewWeek1: iif([wk1 >= 1, 1, 0)


Make sense?


kh

 

[mous]

Sorry, this doesn't work either.

 

[KenHigg]

Can you post a sample?

kh

 

[Brianwarnock]

This will take 3 queries
query1 group on name day hrs and select max of each week field
query2 takes in query1 group as before but add new fields as expressions
totdays is sum of all the Maxofwk fields
totalhours is totaldays*query!hrs
query3
takes in these 2 joined on name day hrs and select all fields as required from both queries.

But how did you get data like that?

Off home now, back tomorrow

Brian

 

[Brianwarnock]

Ok thats not the full story as you want an overall total so a 4th query is required I think