Date/Hours Problems....Please help

[Gabby Girl]

Having major problems trying to return values between two dates. I’ve been
searching the discussion groups and have been pulling my hair out for two
days now and can’t seem to come up with how to make this work. Here’s my
scenario:

HMR (Hourmeter Reading) is read from 100 pieces of Equipment on a monthly
basis (sometimes but rarely twice a month). My tables are as follows:

TblEquipmentReadings
HMRId – PK
Reading Date

TblEquipmentReadingsSub
SubHMRId - PK
HMRId – FK
EquipmentId (cbo from tblEquipmentMaster)
HMRReading
PrevHMR
HoursUsed (Calculated field from Current HMR reading – PrevHMR) – supposed
to be ????


With examples found in the discussions group I was able to get a total query
for Dec and Jan readings with no problems using Last (Jan reading) and First
of Dates (Dec reading) and readings. What I’m unable to figure out is we’ve
now added a third reading for Feb. I need to now pull out only Feb readings
and Jan readings.

Am I just totally stupid that I can’t for the life of me figure out how to
achieve this or what?

I’ll give some sample data to maybe help out.

HMRID ReadingDate EquipmentId HMRReading
1 12/01/05 E34 18152
1 12/01/05 F118 6485
2 01/01/06 E34 18418
2 01/01/06 F118 6499
3 02/01/06 E34 18476
3 02/01/06 F118 6524

My ultimate end wish is for my reports to look like this:

For Period 12/01/05 – 01/01/06

Equipment Id CurHMR PrevHMR HoursUsed
E34 18418 18152 266
F118 6499 6485 14


For Period 01/01/06 – 02/01/06

Equipment Id CurHMR PrevHMR HoursUsed
E34 18476 18418 58
F118 6524 6499 25

Is this even possible or should I be setting up my tables differently or
what ?
If anyone out there could help me I would forever in your debt.

Thanks Kindly
(was blonde, now bald because of all this – hee hee)

 

[Jonathan]

Hi Gabby, have a look at the access web for ideas...

http://mvps.org/access

and then search for date.

Luck
Jonathan