Query not calculating

[Guest]

I have a query that retrieves a value based on dates. It then finds the difference between the values. i.e. retrieves 10 for a date of 03/31/03 and retrieves 30 for a date of 04/30/03. Then calculates the difference of 20. The problem I have is if there is no record for 03/31/03, but there is one for 04/30/03, it does not calculate the difference.... This query is based on 2 queries where one finds the begin date, and one that finds the last date in the same table. I've tried different join properties, but with no luck. Any help will be appreciated.

 

[Kelvin]

You need to create a union queries and make the min and max fields from the
2 tables as 2 fields in the union. The create another select query to group
the results and perform the difference. The same task could probably be
done with fewer queries but withount knowing your data structure, this is
the easiest method.

Kelvin

I have a query that retrieves a value based on dates. It then finds the

difference between the values. i.e. retrieves 10 for a date of 03/31/03 and
retrieves 30 for a date of 04/30/03. Then calculates the difference of 20.
The problem I have is if there is no record for 03/31/03, but there is one
for 04/30/03, it does not calculate the difference.... This query is based
on 2 queries where one finds the begin date, and one that finds the last
date in the same table. I've tried different join properties, but with no
luck. Any help will be appreciated.

 

[Guest]

I tried the union query, but I can't get it to seperate and calculate like I need it to. The database is laid out like this:

I
MeterNum
Dat
Rea

The date field will hold the last day of the month. The read field will hold the numbers I am trying to calculate. So if I search for a date of 03/31/03 and 04/30/03, it will return the correspoding Read fields. I then calculate the difference. Again, the problem is where there is no MeterNum in a month prior to the ending date I'm searching. It will not retrieve the data to calculate if, for example, there is no MeterNum in 03/31/03, but one exists in 04/30/03.

 

[Kelvin]

If you 2 queries are finding the correct info them your union should look
like this.

1, METER01, 3/30/03, 500
2, METER01, 4/30/03, 800
3, METER02, 3/30/03, 200
4, METER03, 4/30/03 700

Select [MeterNum], [Read] as Read1, 0 as Read2 from Query1
Union Select [MeterNum], 0 as Read1, [Read] as Read2 from Query2;

Then a query grouped by MeterNum, Max of Read1, and Max of Read2 will
produce a table like

MeterNum Read1 Read2
METER01 500 800
METER02 200
METER03 700

Just add another field to this query to calculate the difference of Read1
and Read2. How are you handling these situations when one of the numbers is
missing? You might want to rethink you logic and instead of picking the
data by a date, you should look for the last 2 readings, regardless of time,
and find the difference.

Kelvin

I tried the union query, but I can't get it to seperate and calculate like

I need it to. The database is laid out like this:

Id
MeterNum
Date
Read

The date field will hold the last day of the month. The read field will

hold the numbers I am trying to calculate. So if I search for a date of
03/31/03 and 04/30/03, it will return the correspoding Read fields. I then
calculate the difference. Again, the problem is where there is no MeterNum
in a month prior to the ending date I'm searching. It will not retrieve the
data to calculate if, for example, there is no MeterNum in 03/31/03, but one
exists in 04/30/03.

 

[Kelvin]

Glad I could be of help.

Kelvin

This did the trick. Thank you very much. And I have to calculate on dates,

because we will compare our calculation against our suppliers invoice, which
is based on a calendar month usage. Again, thanks.