How you so this depends on how each delivery per product is recorded.
You refer to "a record for 1/5/09" which has deliveries for multiple
products, but normally this sort of thing would require four tables:
1. Deliveries (with columns such as DeliveryID, DeliveryDate,
CustomerID – a foreign key referencing the primary key of a Customers
table, etc).
2. Customers (with columns CustomerID, CustomerName, etc).
3. Products (with columns ProductID, ProductDescription, etc).
4. ProductDeliveries (with columns DeliveryID - in this case a
foreign key referencing the primary key of Deliveries, ProductID - a
foreign key referencing the primary key Products, Quantity etc). This
table models the many-to-many relationship between products and
deliveries.
A report would be based on a query joining the four tables. The
report would be grouped by delivery with the delivery date, customer
to etc in the group header, and each detail row would include the data
per product delivered. An unbound text box in the detail section
could then show the latest delivery date for that product with:
=DMax("DeliveryDate","YourQueryName","ProductID = " & [ProductID] & "
And Quantity > 10")
or if you want the latest delivery of the product to the current
customer:
=DMax("DeliveryDate","YourQueryName","ProductID = " & [ProductID]] & "
And Quantity > 10" & " And CustomerID = " & [CustomerID]))
If each delivery is to multiple customers then you'd also need a
CustomerDeliveries table to model the relationship between customers
and deliveries, the CustomerID column in Deliveries would no longer be
required, but the ProductDeliveries table would require a CustomerID
foreign key referencing the key of Customers, i.e. the primary key of
ProductDeliveries would now be a three-column key rather than two.
That's how this sort of thing should be modelled, but your reference
to what I take to be a [Product1Delivered] column suggests you might
be doing what is known as 'encoding data as column headings' which is
bad design and gives rise to the sort of problems you are
encountering. A fundamental principle of the database relational
model (the Information Principle) is that data is stored as values at
row positions in tables and in no other way.
Ken Sheridan
Stafford, England
I have a Deliveries query where each delivery date is a record of a quantity
delivered for each product. For example, a record for 1/5/09 may have an
amount delivered for Product 1 and Product 3. Then 2/1/09 may have an amount
delivered for Product 2 and Product 3. On the report, I want the "Last
Delivery Date" text boxes (one for each product) to show the most recent
delivery date if the amount delivered was more than 10.
Here's what I'm thinking, but I don't know how to write it in each of the
text box control source properties on the report:
=DMAX( Deliveries Query, [DeliveryDate]) where (10<[Product1Delivered])
I'm sure this is nowhere close to what I need. Please help!
