Updateable Query To List Products Ordered

[Jack]

TblProduct
ProductID
ProductName
Selected (Y/N)

TblOrder
OrderID
CustomerID

TblOrderDetail
OrderDetailID
OrderID
ProductID

Most customers have multiple orders and each customer has a mix of products
in each order. The mix of products from order to order varies.

How do you write a query that provides a list of the products (no
duplicates) a customer has ordered collectively in all his orders where the
query is updateable so the Selected field can be checked?

Thanks for all help!

Jack

 

[pietlinden]

SELECT DISTINCT produces a non-updateable recordset. I don't think you
can. You could write the data to a temporary table with a checkbox (as
another field) and go from there, though.

 

[John Vinson]

How do you write a query that provides a list of the products (no
duplicates) a customer has ordered collectively in all his orders where the
query is updateable so the Selected field can be checked?

You can't.

If you had 38 records, of which you are displaying 18 (no duplicates),
there are 20 records which need to be updated which you do not want to
display. Your two requirements are directly in contradiction!

Two suggestions:

- Run an Update query updating the Selected field

- Better yet - *do away with* the redundant, derived Selected field.
You can create a Query joining the tables to identify which items are
selected.

John W. Vinson[MVP]

 

[Jack]

John,

Thanks for responding!

I don't understand your logic. The Selected field is in the products table.
There may be 38 order detail records collectively for a customer but that
may represent say 6 products. I would like to have a query display those 6
product records from the products table and be able to select one or more of
those records by checking the selected field. I'm not updating the 38
records.

Jack

 

[John Vinson]

John,

Thanks for responding!

I don't understand your logic. The Selected field is in the products table.
There may be 38 order detail records collectively for a customer but that
may represent say 6 products. I would like to have a query display those 6
product records from the products table and be able to select one or more of
those records by checking the selected field. I'm not updating the 38
records.

Well... in a sense, you are. When you have a Query joining two tables,
you get a Recordset containing all of the qualifying joined records.
You'll see those six products multiple times.

Why not use a Form with a Subform? That way you'll see each product
record individually.


John W. Vinson[MVP]

 

[Chris2]

TblProduct
ProductID
ProductName
Selected (Y/N)

TblOrder
OrderID
CustomerID

TblOrderDetail
OrderDetailID
OrderID
ProductID

Most customers have multiple orders and each customer has a mix of products
in each order. The mix of products from order to order varies.

How do you write a query that provides a list of the products (no
duplicates) a customer has ordered collectively in all his orders where the
query is updateable so the Selected field can be checked?

Thanks for all help!

Jack

Jack,

I will join the current list and say: No, it isn't possible. *

When you say, "so the Selected field can be checked", this very much
sounds like the process is trying to operate on the Query's Datasheet.
If that is true, try designing a FORM to handle this functionality,
and build VBA on the Form's code page to do any Updating of the
original tables that is necessary.

Sincerely,

Chris O.


* Why? Various operations, like GROUP BY, produces resultsets without
valid primary key values in *all* the remaining rows, and the the
resultset is *made* non-updateable by MS Access to prevent all sorts
of database horrors from occuring.


Sincerely,

Chris O.