Last date if...

[Jim L.]

I am working on a report based on a query containing the following fields;
DelDate-Shows dates of all deliveries
1ChGV-gross volume delivered to Tank 1
2ChGV-gross volume delivered to Tank 2
1ChNV-net volume delivered to Tank 1
2ChNV-net volume delivered to Tank 2
1Oct-Octane reading for Tank 1
2Oct-Octane reading for Tank 2
(This goes on the same for 11 tanks)
The table is set up so each delivery date is 1 record. One record may have
a delivery to just Tank 1, just Tank 2 or many tanks.
What I would like to display on the report is the most recent delivery date
for each tank.
Here is my basic idea, but I'm not sure of the proper "grammer" to place in
the text boxes to make it work;
Display the most recent [DelDate] where [1ChGV] is not null.
I would then do the same in a text box for each tank. I'm sure this should
be easy, but there are too many variables in it for me to figure out.
Thanks for any help

 

[Allen Browne]

Jim, the problem here is with the way the table is designed. Instead of
having repeating fields for each tank in this table, you need to split this
into 2 tables, with fields like this:

Delivery table, with fields:
DeliveryID AutoNumber primary key
DelDate Date/Time date of this delivery

DeliveryDetail table:
DelDetailID AutoNumber primary key
DeliveryID relates to your Delivery table
TankID Number which tank
GrossVolume Number
NetVolume Number
OctReading Number

On paper, this is like a delivery docket, where the header describes the
delivery itself (the fields in the Delivery table), and then there are
multiple line items (one for each tank affected.) In fact, that's what the
interface will look like: you will have a main form bound to the Delivery
table, and a subform (in continuous view) showing the line items in the
delivery.

Once you have that setup, it's dead easy to create the query you want, as
well as heaps of others you will need. For this one:

1. Create a query using both tables.

2. Depress the Total button on the toolbar in query design.
Access adds a Total row to the query design grid.

3. In the Total row under the TankID field, accept Group By.

4. In the Total row under the DelDate field, choose Max.

The query gives you a row for each tank, with the most recent date beside
each one.

Technically, the process of breaking the data down into related tables is
called normalization. It is the most important thing to learn about
databases, so hopefully this helps.