Updateable Query

[Steve]

A database contains the following tables:
TblCustomer
CustomerID
CustomerName
SelectedCheckBox
etc

TblOrder
OrderID
CustomerID
OrderDate
etc

Most customers have multiple orders so CustomerID for a specific customer
appears more than once in TblOrder. I need an updateable query that contains
a unique list of CustomerID, CustomerName and SelectedCheckbox that returns
a single record for each customer who placed an order between May 1 and May
27. From this list, SelectedCheckbox will be checked to select certain
customers for something else.

The criteria for OrderDate will be:
Between #5/1/09# And #5/27/09#

Any ideas on how to create the updateable query?

Thanks!

Steve

 

[Piet Linden]

A database contains the following tables:
TblCustomer
CustomerID
CustomerName
SelectedCheckBox
etc

TblOrder
OrderID
CustomerID
OrderDate
etc

Most customers have multiple orders so CustomerID for a specific customer
appears more than once in TblOrder. I need an updateable query that contains
a unique list of CustomerID, CustomerName and SelectedCheckbox that returns
a single record for each customer who placed an order between May 1 and May
27. From this list, SelectedCheckbox will be checked to select certain
customers for something else.

The criteria for OrderDate will be:
Between #5/1/09# And #5/27/09#

Any ideas on how to create the updateable query?

Thanks!

Steve

SELECT CustomerID, CustomerName and SelectedCheckbox
FROM Customer
WHERE EXISTS (SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN
#5/1/09# And #5/27/09#)

should be updateable...

 

[John Spencer]

I would use something like the following if you need only records from
tblCustomer.

SELECT CustomerID, CustomerName, SelectedCheckbox
FROM TblCustomer
WHERE CustomerID IN
(SELECT CustomerID
FROM tblOrder
WHERE OrderDate Between #5/1/2009# and #5/27/2009#)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================