Query expression 1

[directorz]

Could someone assist with SQL in creating a calculated field. I need to show a meter total and I have 2 tables as follows:

Tbl1
MeterID – pk
InitialMeterRead

Tbl2
MeterReadID – pk
Ref_MeterID – fk to Tbl1.MeterID
MeterRead

Tbl1.InitialMeterRead is the start reading of a meter. Subsequent readings are recorded in Tbl2.MeterRead. The meter total will be determined by subtracting the start reading in Tbl1 from the highest reading, or maximum reading, in Tbl2.MeterRead.

Thank you

 

[THWatson]

directorz
Try this...

SELECT DISTINCTROW tbl1.MeterID, Max([MeterRead]-[InitialMeterRead]) AS MeterTotal
FROM tblMeter INNER JOIN tbl2 ON tbl1.MeterID = tbl2.Ref_MeterID
GROUP BY tblMeter.MeterID;

Tom

 

[directorz]

THWatson,
Thanx for the response. This returns a join error. For a better visual, I uploaded a sample at geocities.com. Login name is directorz2002. Password is ticket.Via file manager, you can download the sample and better see where I'm at...

 

[directorz]

Please note

The meter total will be determined by subtracting the start reading in Tbl1 from the highest reading, or maximum reading, in Tbl2.MeterRead.

So I will need a maximm value in Tbl2 first. Then subtract Tbl1 to arrive at a total

 

[THWatson]

directorz
Hmmm. Worked at my end. I made up two small tables, plugged in the same data and it worked here. However...

I tried going to geocities.com but I can't get signed in.

To confirm, your
Yahoo ID is directorz2002
Password is ticket.Via

Tom

 

[THWatson]

directorz
Yes, that's the way I designed the two small tables.

tbl1
MeterID 1 reading (start reading) is 1000
MeterID 2 reading (start reading) is 3500

tbl2 (I'll just show the foreign keys)
Ref_MeterID 1 (meter reading) is 3300

Ref_MeterID 2 (meter reading) is 4560
Ref_MeterID 2 (meter reading) is 5000

Correct?

Tom
The SQL result for MeterID 1 is 2300
The SQL result for MeterID 2 is 1500

 

[directorz]

password is ticket

 

[THWatson]

directorz
Sorry. I misunderstood the password. Got it this time, and downloaded the sample file.

I made a new query, using the two tables. Here's the SQL that works here.

SELECT TblMeter.MeterID, Max([MeterRead]-[InitialMeterRead]) AS MeterTotal
FROM TblMeter INNER JOIN TblMeterRead ON TblMeter.MeterID = TblMeterRead.Ref_MeterID
GROUP BY TblMeter.MeterID;

See if this does it for you.

Tom

 

[directorz]

THWatson,
Yes, that's it! I modified the existing with yours and everything work great. Many thanks.

Directorz