combining record data from a table into a query 1

[mmaginniss]

I created a company timesheet table that looks like this:

Project # Empl. Start Phase Wk 1 Wk2
1 Bob 06/30/2003 A 5 2
1 Bob 07/14/2003 A 10 0
2 Joe 06/30/2003 M 8 4
2 Joe 07/14/2003 M 12 2

I want to create a query that will show me the time each employee worked on a project for the whole month (split up into weeks). This is what I would like to see:

Project # Empl. Phase Wk 1 Wk2 Wk3 Wk4
1 Bob A 5 2 10 0
2 Joe M 8 4 12 2

Any help would be truly appreciated.

 

[GoDawgs]

This will work:

SELECT

.[project#],

.employee,

.phase, Sum(IIf([start]=#6/30/2003#,[wk1])) AS week1, Sum(IIf([start]=#6/30/2003#,[wk2])) AS week2, Sum(IIf([start]=#7/14/2003#,[wk1])) AS week3, Sum(IIf([start]=#7/14/2003#,[wk2])) AS week4
FROM table
GROUP BY

.[project#],

.employee,

.phase;

(replace "table" with the name of your table...and make sure all field names are correct)

The problem with this is the dates are coded into the query, so not much leeway as far as running the query every month (you'd have to change the dates every month). I'm sure there's a way around this, just don't know what's going on in your db. Hope that helps.

Kevin

 

[mmaginniss]

That works. Thanks Kevin! You're the man. There are times when the data shows 0,0,0,0 for employee hours on a project that I would rather not see. Any way to eliminate records where I get 0,0,0,0 in wk1, wk2,wk3, and wk 4?

 

[GoDawgs]

Yeah...here's a quick fix...create another column in the query that's the sum of the 4 weeks:

Sum(IIf([start]=#6/30/2003#,[wk1]))+Sum(IIf([start]=#6/30/2003#,[wk2]))+Sum(IIf([start]=#7/14/2003#,[wk1]))+Sum(IIf([start]=#7/14/2003#,[wk2]))

You can't just use the field names unfortunately since we calculated all of those fields...so instead you have to use the entire formula for each.

Then in the criteria section for this new field just put &quot;<>0&quot;...that should solve the problem. Hope that helps.

Kevin