Query to show all cutomer that placed an order

[Simon]

I have a database for an online shop, I would like to create a query
that show all customer names and email address

How do i do a query that will dispay all custromer that have placed an
order, as my current query dispays customers name and email address
more than once if they have place more than one order

Not all customer in tblCustomer have placed an order
any help would be great

 

[Ken Sheridan]

In query design view select 'Yes' as the 'Unique Values' property of the
query in its properties sheet. This is the equivalent of SELECT DISTINCT in
SQL view. Join the query to the Orders table on CustomerID, or whatever the
keys fields are, but include only fields from the Customers table. As an
INNER JOIN (the default join type) returns only rows with matches in both
tables only customers who've placed orders will be returned and the DISTINCT
option means only one row for each will be returned.

Ken Sheridan
Stafford, England

 

[Larry Linson]

I have a database for an online shop, I would like to create a query
that show all customer names and email address

How do i do a query that will dispay all custromer that have placed an
order, as my current query dispays customers name and email address
more than once if they have place more than one order

Not all customer in tblCustomer have placed an order
any help would be great

If we knew what data you had, how it is laid out in tables (the fact that
tblCustomer does not contain orders does not surprise me, but it doesn't
give any indication of other tables), and how those tables are related, then
it might be that someone could offer useful suggestions. If your database
is a commercial one, you might mention the name; if not, then you need to
examine the database itself to determine what information you have. Please
clarify.

Larry Linson
Microsoft Office Access MVP

 

[Paul Shapiro]

Without the table layout, this is a rough guess:
Select C.customerName, C.emailAddress
From Customers as C
Where Exists (Select * From Orders as O Where O.customerID=C.customerID)

 

[Lars]

HI

Supposing you have the table Customers including cid, name, email.... and
another table called Orders including cid, orderno ....


select distinct Customers.Email, Customers.cid, Orders.cid from Customer,
Orders where Customers.cid = Orders.cid.

Add this SQL command to a form and a DataSource component so you hide the
SQL from the user to avoid SQL insertion.


Lars