Sample.zip ( 12.32K ) Number of downloads: 5

Hi Forum,
I have a table that contains columns for Part Number, Equipment Number, Forecast Delivery and Actual Delivery.

Each Part Number represents a part that makes up one of the items of equipment, represented by the Equipment Number. Each Equipment Number has multiple related Part Numbers.

The Forecast Delivery date indicates when the part should arrive and the Actual Delivery date is recorded when the part arrives. The idea is to track the deliveries and plan for assembly of the equipment when all parts have arrived.

I have set up a query that works well for showing the status of Parts but I would like to be able to report on status of assembled equipment also (i.e. all parts have arrived).

I have tried to do this by grouping by Equipment (and removing parts form the query) and selecting Max in the Totals row for Forecast Delivery and Actual Delivery.

The problem is those items of equipment for which some parts have already arrived but some have not.

In these cases my query returns the latest date of the parts that have arrived, giving the false impression that all parts have arrived.

In cases where not all parts have arrived I need the query to show, for that item of equipment, no date in the Actual Delivery field (or Forecast Delivery field as the case may be).

Can I set my query up to do this? If so, how?

Thanks very much for any help with this.

Dave

Hi Dave

I would modify the first query, and create a second summary query. Here is the SQL.


SELECT tblParts.[Equipment No], tblParts.[Forecast Delivery], tblParts.[Actual delivery]
FROM tblParts
WHERE (((tblParts.[Actual delivery]) Is Null));

The summary query is


SELECT DISTINCT [tblParts Query].[Equipment No]
FROM [tblParts Query];

Unless this is a very simple application, I would have a separate table for equipment. The same parts can probably be used in several pieces of equipment.

Hi,

Thanks. This is heading the right way but I still need to show the Delivery Forecast date for parts that don't have an Actual delivery date.

For example, In my sample database the one part "filtered out" because it doesn't have an Actual delivery date (PM-001-003) happens to be the part with the latest Forecast Delivery date, so I need to show this date as the forecast date for the part but show no actual date for it.

As it stands this delivery date doesn't appear in my results and the delivery date for PM-001 is shown as 6/03/2012 when I should show 9/04/2012.


Thanks very much


Dave

In this case include the forecast date in the first query, and in a report, group by equipment number. You don't need the second query. All the second query did was use DISTINCT to remove duplicate equipment numbers.

My query is still not returning the result I need. If I create a report from it I just get a report with incorrect results.

What I need is a query that returns the maximum date, unless there is an entry with Null in the date field, in which case my query returns Null (nothing).

So if I set my query up with Max in the Totals row for Forecast delivery and Actual delivery it is almost right. I need something like "Return Max if there is no Null, otherwise return Null" but I have no idea how to do this in Access.



Thanks very much

Not tried this but it may work. Create an extra column in the query. Call it something like Result If your date was called dteMax, set the column to Result: nZ(dteMax, "Nil")

Thanks very much Neville,

I created a calculated field as you suggested and played around until I got the result I needed. A satisfactory solution turned out to be:


ActDate: Nz([Actual delivery], "Date TBA")


Thanks again.

Dave

Glad to see it worked. Good luck with the rest of the database.