If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Last date if...
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 |
#2
|
|||
|
|||
Last date if...
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jim L." wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|