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@xxxxxxxxxxxxx wrote:
> 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: Report is making too many numbers
    ... Am I supposed to attached the new totals query to the already existing ... where the Award is stored. ... FROM LongSheetPrimContInfo LEFT JOIN LongSheetSubContInfo ON ...
    (microsoft.public.access.reports)
  • Re: Populate Field Based on Value of Previous Record
    ... base the data sheet on the normal query, ... The totals query is used to get the previous odo. ... Remove the field called PreviousODO - you can always find the value ...
    (microsoft.public.access.forms)
  • Re: Running Totals On QueryDef In Code
    ... with the query as a source. ... You could also use a totals query as you mentioned, but do it all in code. ... Dim DB As DAO.Database ... Dim intNumOfPaid, intNumOfHypoed, intNumOfNotTaken, intNumOfDeclined, ...
    (comp.databases.ms-access)
  • Re: Counting 3 different fields seperately on a report
    ... I created the 'totals query' and put in the criteria Like "X" but when I go ... "Jeff Boyce" wrote: ... Access asks you for a value (via a parameter prompt) when it can't find the ...
    (microsoft.public.access.reports)
  • Re: TWO Crosstab Query Ouput Questions
    ... locations for each species as rows with the Banding ... all recoveries in that location for each of those species. ... the SQL of the current crosstab query looks like this: ... concerning the Totals Query. ...
    (microsoft.public.access.queries)