Conditional test to combine multiple records

J

Joe

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
 
J

John Spencer

Open your query in design mode
Select View:Totals
In the new line that appears in the grid, change Group By to Max under the
Shipped column.

If you are doing this in the SQL text window, post back with a copy of your
current sql statement. (or just switch to the query grid and do the
above).\
 
J

Joe

Hi John,

Here is my current SQL statement:


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]));
 
J

John Spencer

Play with this version and see if it even works. I'm sorry but at this
point I have to get back to work and cannot spend anymore time on this.

SELECT O.ID,

OD.LINE_NO,
OD.PROMISE_DATE,

P.ID,

SUM(OD.ORDER_QTY) AS qtyOrdered,
SUM(SL.SHIPPED_QTY) as qtyShipped,
IIf(Max(S.SHIPPED_DATE)>OD.PROMISE_DATE,0,1)) AS DateOutcome,
IIf(SUM(OD.ORDER_QTY)=SUM(SL.SHIPPED_QTY),1,0) AS QtyOutcome,
IIf(Max(S.SHIPPED_DATE)<=OD.PROMISE_DATE,OD.PROMISE_DATE-Max(S.SHIPPED_DATE))
AS DaysEarly,
IIf(MAX(S.SHIPPED_DATE)>=OD.PROMISE_DATE,MAX(S.SHIPPED_DATE)-OD.PROMISE_DATE)
AS DaysLate,

MAX(S.SHIPPED_DATE) as LastShipped
FROM dbo_PART P INNER JOIN ((((dbo_CUSTOMER C INNER JOIN dbo_CUSTOMER_ORDER
O
ON C.ID = O.CUSTOMER_ID) INNER JOIN
dbo_CUST_ORDER_LINE OD ON O.ID = OD.CUST_ORDER_ID)

INNER JOIN dbo_SHIPPER_LINE SL ON
(OD.LINE_NO = SL.CUST_ORDER_LINE_NO) AND
(OD.CUST_ORDER_ID = SL.CUST_ORDER_ID))
INNER JOIN dbo_SHIPPER S ON SL.PACKLIST_ID =S.PACKLIST_ID)

ON P.ID = OD.PART_ID

WHERE OD.PROMISE_DATE=[Forms]![frm Promise Date Attainment]![PromiseDate]
GROUP BY O.ID,

OD.LINE_NO,
OD.PROMISE_DATE,

P.ID,

IIf(Max(S.SHIPPED_DATE)>OD.PROMISE_DATE,0,1)),
IIf(SUM(OD.ORDER_QTY)=SUM(SL.SHIPPED_QTY),1,0) ,
HAVING MAX(S.SHIPPED_DATE)<=[Forms]![frm Promise Date
Attainment]![PromiseDate]


Joe said:
Hi John,

Here is my current SQL statement:


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]));






John said:
Open your query in design mode
Select View:Totals
In the new line that appears in the grid, change Group By to Max under
the
Shipped column.

If you are doing this in the SQL text window, post back with a copy of
your
current sql statement. (or just switch to the query grid and do the
above).\
Click to expand...
Click to expand...
 
J

Joe

Hi John,

This doesn't seem to be working. I'm getting strange results when
running this statement.

John said:
Play with this version and see if it even works. I'm sorry but at this
point I have to get back to work and cannot spend anymore time on this.

SELECT O.ID,

OD.LINE_NO,
OD.PROMISE_DATE,

P.ID,

SUM(OD.ORDER_QTY) AS qtyOrdered,
SUM(SL.SHIPPED_QTY) as qtyShipped,
IIf(Max(S.SHIPPED_DATE)>OD.PROMISE_DATE,0,1)) AS DateOutcome,
IIf(SUM(OD.ORDER_QTY)=SUM(SL.SHIPPED_QTY),1,0) AS QtyOutcome,
IIf(Max(S.SHIPPED_DATE)<=OD.PROMISE_DATE,OD.PROMISE_DATE-Max(S.SHIPPED_DATE))
AS DaysEarly,
IIf(MAX(S.SHIPPED_DATE)>=OD.PROMISE_DATE,MAX(S.SHIPPED_DATE)-OD.PROMISE_DATE)
AS DaysLate,

MAX(S.SHIPPED_DATE) as LastShipped
FROM dbo_PART P INNER JOIN ((((dbo_CUSTOMER C INNER JOIN dbo_CUSTOMER_ORDER
O
ON C.ID = O.CUSTOMER_ID) INNER JOIN
dbo_CUST_ORDER_LINE OD ON O.ID = OD.CUST_ORDER_ID)

INNER JOIN dbo_SHIPPER_LINE SL ON
(OD.LINE_NO = SL.CUST_ORDER_LINE_NO) AND
(OD.CUST_ORDER_ID = SL.CUST_ORDER_ID))
INNER JOIN dbo_SHIPPER S ON SL.PACKLIST_ID =S.PACKLIST_ID)

ON P.ID = OD.PART_ID

WHERE OD.PROMISE_DATE=[Forms]![frm Promise Date Attainment]![PromiseDate]
GROUP BY O.ID,

OD.LINE_NO,
OD.PROMISE_DATE,

P.ID,

IIf(Max(S.SHIPPED_DATE)>OD.PROMISE_DATE,0,1)),
IIf(SUM(OD.ORDER_QTY)=SUM(SL.SHIPPED_QTY),1,0) ,
HAVING MAX(S.SHIPPED_DATE)<=[Forms]![frm Promise Date
Attainment]![PromiseDate]


Joe said:
Hi John,

Here is my current SQL statement:


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]));






John said:
Open your query in design mode
Select View:Totals
In the new line that appears in the grid, change Group By to Max under
the
Shipped column.

If you are doing this in the SQL text window, post back with a copy of
your
current sql statement. (or just switch to the query grid and do the
above).\

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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Would you compare to explain what yo mean by "strange results"? Not what
you expected, errors, etc.

To trouble shoot, I would break the query down. Strip out the calculated
fields DateOutcome, Qty Outcome, DaysEarly, DaysLate and the Having and
Where Clauses. Do the results look correct? IF so, continue on one step
at a time

--Add back the where clause
--Add back the Having clause
--Add back the calculated fields - one at a time


Joe said:
Hi John,

This doesn't seem to be working. I'm getting strange results when
running this statement.

John said:
Play with this version and see if it even works. I'm sorry but at this
point I have to get back to work and cannot spend anymore time on this.

SELECT O.ID,

OD.LINE_NO,
OD.PROMISE_DATE,

P.ID,

SUM(OD.ORDER_QTY) AS qtyOrdered,
SUM(SL.SHIPPED_QTY) as qtyShipped,
IIf(Max(S.SHIPPED_DATE)>OD.PROMISE_DATE,0,1)) AS DateOutcome,
IIf(SUM(OD.ORDER_QTY)=SUM(SL.SHIPPED_QTY),1,0) AS QtyOutcome,
IIf(Max(S.SHIPPED_DATE)<=OD.PROMISE_DATE,OD.PROMISE_DATE-Max(S.SHIPPED_DATE))
AS DaysEarly,
IIf(MAX(S.SHIPPED_DATE)>=OD.PROMISE_DATE,MAX(S.SHIPPED_DATE)-OD.PROMISE_DATE)
AS DaysLate,

MAX(S.SHIPPED_DATE) as LastShipped
FROM dbo_PART P INNER JOIN ((((dbo_CUSTOMER C INNER JOIN
dbo_CUSTOMER_ORDER
O
ON C.ID = O.CUSTOMER_ID) INNER JOIN
dbo_CUST_ORDER_LINE OD ON O.ID = OD.CUST_ORDER_ID)

INNER JOIN dbo_SHIPPER_LINE SL ON
(OD.LINE_NO = SL.CUST_ORDER_LINE_NO) AND
(OD.CUST_ORDER_ID = SL.CUST_ORDER_ID))
INNER JOIN dbo_SHIPPER S ON SL.PACKLIST_ID =S.PACKLIST_ID)

ON P.ID = OD.PART_ID

WHERE OD.PROMISE_DATE=[Forms]![frm Promise Date Attainment]![PromiseDate]
GROUP BY O.ID,

OD.LINE_NO,
OD.PROMISE_DATE,

P.ID,

IIf(Max(S.SHIPPED_DATE)>OD.PROMISE_DATE,0,1)),
IIf(SUM(OD.ORDER_QTY)=SUM(SL.SHIPPED_QTY),1,0) ,
HAVING MAX(S.SHIPPED_DATE)<=[Forms]![frm Promise Date
Attainment]![PromiseDate]


Joe said:
Hi John,

Here is my current SQL statement:


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]));






John Spencer wrote:
Open your query in design mode
Select View:Totals
In the new line that appears in the grid, change Group By to Max under
the
Shipped column.

If you are doing this in the SQL text window, post back with a copy of
your
current sql statement. (or just switch to the query grid and do the
above).\

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional test to combine multiple records 5
Combine multiple rows into one record 10
Delete Multiple Lines 2
Ranking Records with multiple criteria. 1
3 queries into 1 8
Help with complex query 1
Complex for me but may be a very simple query for you experts. 2
Count Unique Fields Only 1

Top