By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Conditional test to combine multiple records

P: n/a
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

Jan 31 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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

jh********@eadmotors.com 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


Jan 31 '06 #2

P: n/a
Hi Inkman,

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

Jan 31 '06 #3

P: n/a
Hello again. Do you need the "Shipped Date" displayed? If so,
then you'll always get two records because of different dates.
If this field were removed, perhaps this would work with you
knowing that the information displayed was on or before
Jan. 30th.

Regards

Jan 31 '06 #4

P: n/a
Hi Inkman,

Unfortnetly, the ship date is required as it was what I use to compare
whether the order was shipped on time.

If Ship Date is greater than Due Date, then the order is late.

I want Access to combine these records, and just use the greatest
shipdate i.e. my example above.

Jan 31 '06 #5

P: n/a
If you want to use the greatest ship date, then select "Max"
for the ship date when you select "Sum".

Regards,
jh********@eadmotors.com wrote:
Hi Inkman,

Unfortnetly, the ship date is required as it was what I use to compare
whether the order was shipped on time.

If Ship Date is greater than Due Date, then the order is late.

I want Access to combine these records, and just use the greatest
shipdate i.e. my example above.


Jan 31 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.