Query to List Dup to One Cust - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query to List Dup to One Cust

Lance Broxson

4 years ago

How can i make a query that takes a ProductOnOrderTable
that is made from a linked excel spreadsheet from a
manufacturer. And a query OrdersQ that has customers
that have bought the product and join them together to
show that product is sold to that customer. My problem is
my ProductOnOrderTable has product like

Model   Qty    EstDateRecieved
Ref555   2       6/15/22
Ref444   1       6/13/22
Ref555    1      6/20/22

When I add OrdersQ and join the Model fields it list like this

(Results Now)
Model   Qty    EstDateRecieved  CustSoldTo
Ref555   2       6/15/22                    Joe
Ref444   1       6/13/22                    Sue
Ref555    1      6/20/22                     Joe

The customer gets put on every item that I have multiples ordered.
Is there a way to just show Joe on the first Item and not on the others.
If not in OrdersQ just leave blank, it is a stock item.

(Results I would like to have)
Model   Qty    EstDateRecieved  CustSoldTo
Ref555   2       6/15/22                    Joe
Ref444   1       6/13/22                    Sue
Ref555    1      6/20/22

Thanks,
Lance
Silver Member

Query to list duplicates to one customer

	Scott Axton @Reply 4 years ago
You probably will need two queries. One to get the list and one to group. Check out the Aggregate Queries and the Grouping videos

Kevin Yip @Reply

4 years ago

You can't put "Joe" on just one line because Access can't tell the difference between the first "Ref555" and the other "Ref555", so it links to both of them. "Ref555" is in the "many" side of the relationship. One way to fix this is to have "Ref555" be on the "one" side, i.e. by making it a *normalized* list. This was how we did it when I worked in retail: https://imgur.com/nSq8vtl . Each product appears only once on the list. Stock and on-order qty of each product are on the *same row* and in separate columns, not on the same column like you did.

Also consider that there could be several customers who order "Ref555". That means the customer/order info is actually the "many" side. To present this info the right way, you need to put them in a "child" section, such as a subform on a form, or a sub-report on a report.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View