Conditional test to combine multiple records

Hello everyone,

I have a database where I collect shipment data from various tables.
However, I have a problem. Whenever I want to see shipping data for
orders that were set to ship on or before a certain date (in this case
January 30th) the database will return 2 rows for an order as you can
see below.

Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped:
141285 1 1/30/2006 31 10 S15F-55
1/17/2006
141285 1 1/30/2006 31 21 S15F-55
1/27/2006

This is actually one order, that was shipped over the course of
multiple dates. However, I want Access to combine this information into
one record becaue I count the total number of shipped orders in another
query for use in a shippin report. This causes my report to have
incorrect data because it shows 2 orders instead of 1 that just shipped
over the course of multiple dates.

How can I combine these records if the following is true:
If Order ID, Line, Due Date, Qty, and Part # are all the same -
I want Access to combine these records into one line displaying the
greatest shipdate (in this case 1/27/2006).

So in the end, I want my result to look like this:

Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped:
141285 1 1/30/2006 31 31 S15F-55
1/27/2006

re: Conditional test to combine multiple records

Hello jhutchi,

Just change your query to a totals query and
set your ship qty field to Sum and then re-run
it.

Hope this works for you.

Regards



jhutchings@eadmotors.com wrote:[color=blue]
> Hello everyone,
>
> I have a database where I collect shipment data from various tables.
> However, I have a problem. Whenever I want to see shipping data for
> orders that were set to ship on or before a certain date (in this case
> January 30th) the database will return 2 rows for an order as you can
> see below.
>
> Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped:
> 141285 1 1/30/2006 31 10 S15F-55
> 1/17/2006
> 141285 1 1/30/2006 31 21 S15F-55
> 1/27/2006
>
> This is actually one order, that was shipped over the course of
> multiple dates. However, I want Access to combine this information into
> one record becaue I count the total number of shipped orders in another
> query for use in a shippin report. This causes my report to have
> incorrect data because it shows 2 orders instead of 1 that just shipped
> over the course of multiple dates.
>
> How can I combine these records if the following is true:
> If Order ID, Line, Due Date, Qty, and Part # are all the same -
> I want Access to combine these records into one line displaying the
> greatest shipdate (in this case 1/27/2006).
>
> So in the end, I want my result to look like this:
>
> Order ID: Line: Due Date: Qty: Ship Qty: Part #: Shipped:
> 141285 1 1/30/2006 31 31 S15F-55
> 1/27/2006[/color]

re: Conditional test to combine multiple records

Hi Inkman,

I have tried this, but then I get errors displaying the query.

I get the error: "You tried to execute a query that does not include
the specified expression "EXPRESSION HERE" as part of an aggregate
function."

re: Conditional test to combine multiple records

Hello again. Do you need the "Shipped Date" displayed? If so,
then you'll always get two records because of different dates.
If this field were removed, perhaps this would work with you
knowing that the information displayed was on or before
Jan. 30th.

Regards

re: Conditional test to combine multiple records

Hi Inkman,

Unfortnetly, the ship date is required as it was what I use to compare
whether the order was shipped on time.

If Ship Date is greater than Due Date, then the order is late.

I want Access to combine these records, and just use the greatest
shipdate i.e. my example above.

re: Conditional test to combine multiple records

If you want to use the greatest ship date, then select "Max"
for the ship date when you select "Sum".

Regards,


jhutchings@eadmotors.com wrote:[color=blue]
> Hi Inkman,
>
> Unfortnetly, the ship date is required as it was what I use to compare
> whether the order was shipped on time.
>
> If Ship Date is greater than Due Date, then the order is late.
>
> I want Access to combine these records, and just use the greatest
> shipdate i.e. my example above.[/color]