Date Comparison Query

[El Gordo]

Hello all,

I have a database with 5 tables. Each table has 4 fields in it:
- date
- region
- city
- amount
The amount field varies between things used (electricity, materials, etc)
and things made (products, money, etc). I'm tracking 50+ sites that are
located in different cities and grouped into regions. Each day, the sites
use different amounts of material to create different amounts of product and
these amounts are logged into a database (not necessarily everyday). I'm
using a create-table query to calculate ratios of product to material and I
need to be able to tell how many days elapse between each entry in the
database. Any ideas? Thanks in advance.

J

 

[Tom Ellison]

Dear J:

I must first assume the rows in your table are uniquely indexed such that
there really is a well defined (and unique) meaning to the term "between
each entry in the database". You should reveal just which columns form this
unique order to your rows. Do you have a constraint on the table to that
effect (a unique index or primary key)?

The way to find the elapsed time between two rows is to use a subquery.
This would retrieve the unique "previous row" so the difference can be
calculated.

If you will fill in the precise definition of which is the "previous row" I
can probably provide a query. If this definition does not ALWAYS produce a
unique "previous row" then the query will definitely fail. Ambiguity is not
the basis for query work.

Tom Ellison

 

[El Gordo]

Tom,
Thanks for your assistance. I'll try to clarify without getting into too
much boring detail. For each table, I have multiple-primary keys (date,
region, city) such that only one plant can enter a consumption per date, but
every plant can enter a consumption for each date (allowing multiple dates,
regions, and smilar cities). One of my tables is for electrical consumption
and it's fields are:
date
region
city
consumption
Another table is production in lbs. It's fields are the same where
consumption is the lbs of production. (every other table is structured the
same way)
If I'm querying this table to calculate electric consumption/lb production
how can I tell what the last entry date was for a particular city. I realize
that the table has to be sorted by city and then date for this to work, but
I'm having trouble working out the actual query. Once again, thanks very
much

J

 

[John Vinson]

Hello all,

I have a database with 5 tables. Each table has 4 fields in it:
- date
- region
- city
- amount

Well... then you have some real problems with the database. You'ld do
much better to have ONE table with five fields, including a field for
the type of things-used.

The amount field varies between things used (electricity, materials, etc)
and things made (products, money, etc). I'm tracking 50+ sites that are
located in different cities and grouped into regions. Each day, the sites
use different amounts of material to create different amounts of product and
these amounts are logged into a database (not necessarily everyday). I'm
using a create-table query to calculate ratios of product to material and I
need to be able to tell how many days elapse between each entry in the
database. Any ideas? Thanks in advance.

A CreateTable query is a lot of extra overhead that you don't really
need! Note that you can base a Form, a Report, an Export, or pretty
much anything else on a Select query. You may be making the very
common error of assuming that you must have everything in one table to
do so: you don't.

You'll have to give an example of what you mean by "how many days
elapse between each entry". You've got five tables with five different
date fields; do you need the time elapsed between records in one
table, across multiple tables, or what? How is this date difference to
be used?

John W. Vinson[MVP]

 

[Tom Ellison]

Tom,
Thanks for your assistance. I'll try to clarify without getting into too
much boring detail. For each table, I have multiple-primary keys (date,
region, city) such that only one plant can enter a consumption per date,
but every plant can enter a consumption for each date (allowing multiple
dates, regions, and smilar cities). One of my tables is for electrical
consumption and it's fields are:
date
region
city
consumption
Another table is production in lbs. It's fields are the same where
consumption is the lbs of production. (every other table is structured the
same way)
If I'm querying this table to calculate electric consumption/lb production
how can I tell what the last entry date was for a particular city. I
realize that the table has to be sorted by city and then date for this to
work, but I'm having trouble working out the actual query. Once again,
thanks very much

J

 

[Tom Ellison]

Dear J:

This time, I'll try to actually send a response.

A subquery can produce the date of the previous entry:

SELECT *
FROM Table1 T
Table1 T1
WHERE T1.[date] =
(SELECT MAX([date])
FROM Table1 T2
WHERE T2.[date] < T.[date]
AND T2.region = T.region
AND T2.city = T.city)

Substitute the actual name of your table where the above says Table1.

The last 5 lines of this query are a correlated subquery. The symbols T,
T1, and T2 are aliases (look up in help for explanation).

If date is unique for any region/city then you will get only one pairing for
each row.

Using this, you have two rows of the table in each row of the results.
Calculating the elapsed time between them is simple (I hope).

Tom Ellison