query design for matched records

[Hong Luen via AccessMonster.com]

MS Access Query Wizard (the unmatched query wizard) provides an option for
searching records that have no related records in another data table.

However, how should I design the query if I am searching for records that
have related records in another data table.

For example:
Table 1 Customer contacts
Table 2 Orders
How to build a query to look up customer(s) from Table 1 that have placed
order(s) in a certain period (Table 2).

Even if a certain customer ordered a few times, I need it appear just once
in the search result.

 

[John Vinson]

However, how should I design the query if I am searching for records that
have related records in another data table.

For example:
Table 1 Customer contacts
Table 2 Orders
How to build a query to look up customer(s) from Table 1 that have placed
order(s) in a certain period (Table 2).

Even if a certain customer ordered a few times, I need it appear just once
in the search result.

Create a Query joining Table1 to Table2 by the customerID.

Put a criterion on the order date field but *uncheck* the Show
checkbox on this field - you don't want to see the order dates if they
ordered repeatedly, just use it as a criterion.

Then set the query's Parameters (right mouseclick the background of
the tables and select parameters) to set the "Unique Values" parameter
to True - so it will show you each customer only once no matter how
many orders are found.

John W. Vinson[MVP]

 

[Hong Luen via AccessMonster.com]

Dear John,
After right-clicking, it pops up a table of 2 columns "parameters" & "data
type". How to set the "Unique Value"? Thank you for your help.
Hong

 

[John Vinson]

Dear John,
After right-clicking, it pops up a table of 2 columns "parameters" & "data
type". How to set the "Unique Value"? Thank you for your help.
Hong

Hrm. When I right click in query design view I get a popup window with
ten choices (SQL View, Design View, ...); the bottom one is
Properties. Are you clicking *on a table icon* or on a field? Just
click in the grey background. Or, select View... Properties from the
Menu, or hit F4.

John W. Vinson[MVP]