query sort by week

[AccidentalFate]

i have a query i am working on that is sorted, sumed and grouped... i have it grouped by 4 fields (date, class, rate, shift) with one sumed field (hours). it doesn't group and sum the data correctly.

here is the SQL

SELECT EnterJobLabor.Classification, EnterJobLabor.Rate, EnterJobLabor.Shift, Sum(EnterJobLabor.HoursWorked) AS SumOfHoursWorked, EnterJobLabor.DateWorked
FROM EnterJobLabor
GROUP BY EnterJobLabor.Classification, EnterJobLabor.Rate, EnterJobLabor.Shift, EnterJobLabor.DateWorked
HAVING (((EnterJobLabor.DateWorked)=([dateworked]-Weekday([dateworked])+7)))
ORDER BY EnterJobLabor.DateWorked;

is there a max on the groups? or is there something else wrong?

and ideas on help...

 

[Alc]

What does the query look like?

 

[AccidentalFate]

i showed a picture.. does it work?

 

[Alc]

The picture is of the result of the query.
What does the query itself look like (i.e the SQL)?

 

[AccidentalFate]

shift should be a 1 or a 2 - that works
class is jw,ad,pm,op,ef... etc. - that works
rate should be 1, 1.5 or 2 - doesn't work

 

[AccidentalFate]

Oh... here

SELECT EnterJobLabor.Classification, EnterJobLabor.Rate, EnterJobLabor.Shift, Sum(EnterJobLabor.HoursWorked) AS SumOfHoursWorked, EnterJobLabor.DateWorked
FROM EnterJobLabor
GROUP BY EnterJobLabor.Classification, EnterJobLabor.Rate, EnterJobLabor.Shift, EnterJobLabor.DateWorked
HAVING (((EnterJobLabor.DateWorked)=([dateworked]-Weekday([dateworked])+7)))
ORDER BY EnterJobLabor.DateWorked;

directly from the sql

 

[Alc]

The query looks fine to me. I think it just gives the impression of not grouping as the values match. Each of the records seems to be different, taking into account the combination of Class, Shift and Date, so the Rate has no effect.

 

[AccidentalFate]

i thought it looked oka...

but i know for a fact that there should be a rate of 1 for each week just like there is a 1.5 but the 1 isn't showing....

 

[Alc]

If you just run

SELECT EnterJobLabor.Classification, EnterJobLabor.Rate
FROM EnterJobLabor
GROUP BY EnterJobLabor.Classification, EnterJobLabor.Rate;

Does it give you the expected values?

 

[AccidentalFate]

no... it doesn't

 

[Alc]

How about

SELECT EnterJobLabor.Rate
FROM EnterJobLabor
GROUP BY EnterJobLabor.Rate;

If that one doesn't work, perhaps the data in the table isn't as it should be?

 

[AccidentalFate]

hmm... yeah... 1, 1.5, and 2

 

[AccidentalFate]

SELECT EnterJobLabor.Rate, Sum(EnterJobLabor.HoursWorked) AS SumOfHoursWorked, EnterJobLabor.DateWorked
FROM EnterJobLabor
GROUP BY EnterJobLabor.Rate, EnterJobLabor.DateWorked
HAVING (((EnterJobLabor.DateWorked)=([dateworked]-Weekday([dateworked])+7)))
ORDER BY EnterJobLabor.DateWorked;


i tried this too and it still wont show the rate 1

 

[Alc]

So this doesn't show any Rate values of '1'

SELECT EnterJobLabor.Classification, EnterJobLabor.Rate
FROM EnterJobLabor
GROUP BY EnterJobLabor.Classification, EnterJobLabor.Rate;

but this does?

SELECT EnterJobLabor.Rate
FROM EnterJobLabor
GROUP BY EnterJobLabor.Rate;

I'm afraid I'm stumped

I'll carry on having a think about it today, but I have a few meetings to attend, so hopefully someone else will step in with a suggestion.

 

[AccidentalFate]

it works on both there^^^

 

[AccidentalFate]

i dono if this will work... never done it...

http://www.filejumbo.com/Download/B22077746FA736CA - i forgot to show the database window so you will need to download... make a new database and import the table and query

but what i did was made another database with just the information needed... see if this helps you in trying to figure it out... i'm stumped too...

 

[AccidentalFate]

from looking more into it... it also doesn't add correctly or show all the fields...

 

[AccidentalFate]

help please...