Confusing Query, Need Help 1

[jminn0311]

I have a table as follows:

Date Shift Upgrage1 Upgrade2
1/1/11 Shift1 .50% .75%
1/2/11 Shift2 .25% .85%
1/3/11 Shift3 .95% .35%
1/4/11 Shift4 .65% .45%

There is a lot of data in this table and I need to be able to query it by a date range and per shift returning the average upgrade for the selected date range. Is this possible with Access 2007? Thank you for any help.

 

[SkipVought]

hi,

What have you tried?

What were the results?

What results did you expect?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

It would be nice to know how your Fields in your table are defined, because it looks like all of them are Text (and they should be Date and Numbers). And what are the names of your fields because 'Date' is really bad idea for a field name - it is a reserved word and those should be avoided.

"returning the average upgrade" is that for Upgrage1 or Upgrade2 ?

Have fun.

---- Andy

 

[MazeWorX]

Assuming your table looks like this:


wrkDate ShiftID Upgrage1 Upgrade2
1/1/11 Shift1 .50 .75
1/2/11 Shift2 .25 .85
1/3/11 Shift3 .95 .35
1/4/11 Shift4 .65 .45
1/4/11 Shift1 .75 .25
wrkDate - Text
ShiftID - Text
UpGrade1 - Double
Upgrade2 - Double

you could

query1:

SELECT tblShifts.wrkDate, tblShifts.ShiftID, tblShifts.Upgrade1, tblShifts.Upgrade2
FROM tblShifts
WHERE (((tblShifts.wrkDate) Between "1/1/11" And "1/4/11"))

query2

SELECT Query1.ShiftID, Avg(Query1.Upgrade1) AS AvgOfUpgrade1, Avg(Query1.Upgrade2) AS AvgOfUpgrade2, Avg(([Upgrade1]+[Upgrade2]))/2 AS AvgOfBoth
FROM Query1
GROUP BY Query1.ShiftID

and your results:

ShiftID	AvgOfUpgrade1	AvgOfUpgrade2	AvgOfBoth
Shift1	   0.625                0.5	  0.5625
Shift2	   0.25	             0.85	  0.55
Shift3	   0.95	             0.35	  0.65
Shift4	   0.65	             0.45	  0.55

this works if the data types are like above
But of coarse this is irrelevant without the field data types. As you can see it can be done

As Andrzejek has requested how exactly is your table defined

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work