Re: Conditional test to combine multiple records

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

.

Relevant Pages

  • Re: HELP WITH UPDATE QUERY
    ... each selected items in the list, and, in the example, make an SQL statement ... the user wants to select MULTIPLE items from this list ... query named qrProd and the list box is from that same query, ... I ASSUMED the control name was. ...
    (microsoft.public.access.queries)
  • Re: Conditional test to combine multiple records
    ... >> current sql statement. ... (or just switch to the query grid and do the ... >>> one record becaue I count the total number of shipped orders in another ... >>> over the course of multiple dates. ...
    (microsoft.public.access.queries)
  • Re: Error using " in .open with ADODB.Recordset
    ... recordset but I'm getting hung up on the SQL statement. ... query that has a string as a condition. ... Dim mrk As String ...
    (microsoft.public.data.ado)
  • Re: expiration
    ... First what type of field are you applying the criteria against? ... You can try the following in a query. ... If you are trying to get just records with the License has expired within ... it is the actual SQL statement that is executed. ...
    (microsoft.public.access.queries)
  • Re: Same date range in main and sub form
    ... You need to concatenate together the parts to make up the SQL string ... it will look like the SQL statement you original ... Allen Browne - Microsoft MVP. ... well as the name of the query on which the form is based. ...
    (microsoft.public.access.forms)