Conditional test to combine multiple records

[jhutchings]

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

 

[Guest]

Use a TOTALs query. Just change a select query by clicking on the icon of
the Greek letter Epsilon.
In the Total row change Ship Qty from Group By to SUM. Change Shipped in
the Total row from Group By to Last.

 

[jhutchings]

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."

 

[Guest]

Post your SQL statement bu opening the query in design view and clicking on
menu VIEW - SQL View. Highlight, copy and then paste in a post.

 

[jhutchings]

SELECT dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.LINE_NO,
dbo_CUST_ORDER_LINE.PROMISE_DATE, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_SHIPPER_LINE.SHIPPED_QTY, dbo_PART.ID,
Sum(IIf(dbo_SHIPPER!SHIPPED_DATE>dbo_CUST_ORDER_LINE!PROMISE_DATE,0,1))
AS DateOutcome,
IIf(dbo_CUST_ORDER_LINE!ORDER_QTY=dbo_SHIPPER_LINE!SHIPPED_QTY,1,0) AS
QtyOutcome,
IIf(dbo_SHIPPER!SHIPPED_DATE<=dbo_CUST_ORDER_LINE!PROMISE_DATE,dbo_CUST_ORDER_LINE!PROMISE_DATE-dbo_SHIPPER!SHIPPED_DATE)
AS DaysEarly,
IIf(dbo_SHIPPER!SHIPPED_DATE>=dbo_CUST_ORDER_LINE!PROMISE_DATE,dbo_SHIPPER!SHIPPED_DATE-dbo_CUST_ORDER_LINE!PROMISE_DATE)
AS DaysLate, dbo_SHIPPER.SHIPPED_DATE
FROM dbo_PART INNER JOIN ((((dbo_CUSTOMER INNER JOIN dbo_CUSTOMER_ORDER
ON dbo_CUSTOMER.ID = dbo_CUSTOMER_ORDER.CUSTOMER_ID) INNER JOIN
dbo_CUST_ORDER_LINE ON dbo_CUSTOMER_ORDER.ID =
dbo_CUST_ORDER_LINE.CUST_ORDER_ID) INNER JOIN dbo_SHIPPER_LINE ON
(dbo_CUST_ORDER_LINE.LINE_NO = dbo_SHIPPER_LINE.CUST_ORDER_LINE_NO) AND
(dbo_CUST_ORDER_LINE.CUST_ORDER_ID = dbo_SHIPPER_LINE.CUST_ORDER_ID))
INNER JOIN dbo_SHIPPER ON dbo_SHIPPER_LINE.PACKLIST_ID =
dbo_SHIPPER.PACKLIST_ID) ON dbo_PART.ID = dbo_CUST_ORDER_LINE.PART_ID
GROUP BY dbo_CUSTOMER_ORDER.ID, dbo_CUST_ORDER_LINE.LINE_NO,
dbo_CUST_ORDER_LINE.PROMISE_DATE, dbo_CUST_ORDER_LINE.ORDER_QTY,
dbo_SHIPPER_LINE.SHIPPED_QTY, dbo_PART.ID,
IIf(dbo_SHIPPER!SHIPPED_DATE<=dbo_CUST_ORDER_LINE!PROMISE_DATE,dbo_CUST_ORDER_LINE!PROMISE_DATE-dbo_SHIPPER!SHIPPED_DATE),
IIf(dbo_SHIPPER!SHIPPED_DATE>=dbo_CUST_ORDER_LINE!PROMISE_DATE,dbo_SHIPPER!SHIPPED_DATE-dbo_CUST_ORDER_LINE!PROMISE_DATE),
dbo_SHIPPER.SHIPPED_DATE
HAVING (((dbo_CUST_ORDER_LINE.PROMISE_DATE)=[Forms]![frm Promise Date
Attainment]![PromiseDate]) AND
((dbo_SHIPPER.SHIPPED_DATE)<=[Forms]![frm Promise Date
Attainment]![PromiseDate]));

 

[Guest]

I do not have the skills! I can not help you at this point as you have stuff
in your joins that I have never heard of before. You have field comparisons
like you find in a WHERE or HAVING statement. Then you And'ed some of them
together.

I suggest that you start with two of your main tables, build a simple query
and then test it. Then add the next table an test. On and on to build what
you need.