Monthly and Yearly Totals

[vsteinly]

I have a database that collect time and costs for projects. I have a query that looks like this.

tblData.Employee
tblData.Hours
tblData.Rate
tblData.Project
tblData.Task
tblValues.Pay Period

I trying to get a current pay period and year-to-date for the hours and rate. I able to get the year-to-date to work but not the current pay period. My problem is the current pay period can have null values.

I tried creating two queries, one for ytd and the other for pay period and join them but no matter what kind of join I do I can't get it to work right.

Looking for help!

 

[CJ_London]

Sorry - that is not a query that I recognise - it would normally start with 'SELECT', have a FROM, perhaps some joins and a WHERE criteria

 

[vsteinly]

Here is my SQL behind the query.

SELECT [qry By Pay Period].Employee,
[qry By Pay Period].SumOfHours,
[qry By Pay Period].SumOfRate,
[qry Project Report].Hours,
[qry Project Report].Rate,
[qry Project Report].Project,
[qry Project Report].Task
FROM [qry By Pay Period] INNER JOIN [qry Project Report] ON [qry By Pay Period].Employee = [qry Project Report].Employee
WHERE ((([qry By Pay Period].Employee)="Darrin Loomis –104076"));

 

[Geotch]

can you upload a database with a sample of the data, I can show you the solution.

 

[CJ_London]

What in that data defines the current pay period? Also hadn't appreciated you have queries on queries so you also need to send the details on qry Project Report and qry By Pay Period.

 

[vsteinly]

Here is a copy of my database

 

[Geotch]

First I deleted your parameter value in your qry By Pay Period so it shows all pay periods.

2nd - create a query to get the current pay period since you didn't really define it. I took the max pay period of the qry By Pay Period and called the query qry Current Pay Period.

3rd - link the two queries together to show all the data from the current pay period - qry Pay Period linked to Current.

4th query - qry Summary by Project Pay Period - for the current pay period
5th query - summary of all pay periods - qry Summary All

6th query - qry z final - joins the two together and gives you your answer.

Work forwards or backwards through the queries in Name Order to see the steps involved. Starting off I always took small steps when doing queries, it really helps you to solve problems.

 

[vsteinly]

Sorry I got pulled away from this for a couple days. Thanks for looking into this for me, but your last query does not bring back the correct totals for the current pay period. This is the same problem that I was having. The queries that you created for the pay period and the total work by themselves but when you join the two the current pay period hrs and rate grow exponentially. Any other ideas?

 

[Geotch]

You never defined current pay period. Is it last week? Last two weeks? Current and last week?

 

[vsteinly]

The pay period is a two week period. So there will be 26 in a year. I pickup the pay period in our payroll system so I don't need to worry about dates. The value has been set in the tblValues

 

[Geotch]

Can you take my example and change the current pay period logic?

 

[vsteinly]

Your queries are doing the same thing mine are. Individually they work it’s when they are combined into one query they go crazy. I have tried to change the joins and the fields that they are linked by. It just doesn’t want to work.

 

[Geotch]

No, my final query works. I'll take a look later.

 

[vsteinly]

Look closer at the subtotals

qry Pay Period linked to Current

SumofHours = 1,164
SumofRate = 48,519.19
These are correct.

qry Summary All
AllHours = 11,635.63
AllRate = 348,648.88
These are correct.

qry z Final
AllHours = 116,35.63--Correct
AllRate = 348,648.88--Correct
CurrentHrs = 4,671.5 --Incorrect
CurrentRate = 161,147.9 --Incorrect

 

[Geotch]

I'm looking at the database on my computer which I have uploaded again. Totals for pay period 7, which is the last pay period on record contained in:
"qry pay period linked to current" - total hours = 748, total rate 14810.54, 87 records

"qry summary all" is another query from "qry by Pay Period"
both of these total 1739.63 hrs and 314940.23 rate

Sorry, I forgot to take out the grouping of Task in qry Summary All. Once Task is taken out of "qry Summary All" and "qry Z Final" everything matches again.

New database uploaded, sorry for the confusion.

 

[vsteinly]

Yes, Yes!! That now works. Thanks. Small things sometimes get in the way of progress.

Do you think I can create another query that could include employee and task. I going to try a union to see if I can get that to work. What do you think?

 

[Geotch]

Don't think you need a union query. You need to learn how the Totals button works in queries.