Re: Conditional test to combine multiple records
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Tue, 31 Jan 2006 12:44:44 -0500
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" <jhutchings@xxxxxxxxxxxxx> wrote in message
news:1138726967.506806.21060@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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).\
>>
>> "Joe" <jhutchings@xxxxxxxxxxxxx> wrote in message
>> news:1138718754.860747.202910@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>> > 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
>> >
>
.
- Follow-Ups:
- References:
- Conditional test to combine multiple records
- From: Joe
- Re: Conditional test to combine multiple records
- From: John Spencer
- Re: Conditional test to combine multiple records
- From: Joe
- Conditional test to combine multiple records
- Prev by Date: Re: And Using a query to select data in one field compared to anot
- Next by Date: Re: using the IIF function in access
- Previous by thread: Re: Conditional test to combine multiple records
- Next by thread: Re: Conditional test to combine multiple records
- Index(es):
Relevant Pages
|
|
