Query on identical fields in two tables

[Scott]

Hello,
I have a glitch in my query that I can't figure out so I
could use some help. As you can tell, I'm new with
Access. Thanks in advance for your assistance.

Objective:
I'd like a query to extract all the sales records
for "Scott" from both the Q1 and Q2 table.

Tables:
Table 1: Q1 sales
Table 2: Q2 sales

Fields I'd like in query which are in both tables:
Quarter
Salesrep
Sales dollars
Date of sale

I've shown the relationship for the Salesrep field by
dragging this field from Table 1 to this Salesrep field
in Table 2 - thus I have a line between this field
between the two tables. In Salesrep criteria, I've
input "Scott." My query output fields are the four fields
mentioned above. Ideally, this is what I want as a result
of my query.

Objective:
Scott Q1 $900 1/1/2004
Scott Q1 $500 1/2/2004
Scott Q2 $700 4/8/2004
Scott Q2 $2500 4/8/2004

Problem #1: When I select my fields for the query, I used
the fields from the Q1 table and then my output is shown
here. You'll see that I'm missing my data from the Q2
table yet the data from Q1 is shown twice - I know why
it's showing twice but I don't know how to fix it.
Scott Q1 $900 1/1/2004
Scott Q1 $500 1/2/2004
Scott Q1 $900 1/1/2004
Scott Q1 $500 1/2/2004

Problem #2: When I select my fields for the query showing
eight fields (the four from each table), I get this:
Scott Q1 $900 1/1/2004 Scott Q2 $2,500 4/4/2004
Scott Q1 $900 1/1/2004 Scott Q2 $700 4/1/2004
Scott Q1 $200 1/2/2004 Scott Q2 $2,500 4/4/2004
Scott Q1 $200 1/2/2004 Scott Q2 $700 4/1/2004

How can I consolidate Scott's sales from these two
tables? Can someone tell me what I'm doing wrong?

Thanks so much.

 

[Gerald Stanley]

To get the desired output, try a Union query along the
following lines

SELECT [SalesRep], [Quarter], [Sales dollars], [Date of sale]
FROM [Q1 Sales]
WHERE [SalesRep] = 'Scott'
UNION
SELECT [SalesRep], [Quarter], [Sales dollars], [Date of sale]
FROM [Q2 Sales]
WHERE [SalesRep] = 'Scott'
ORDER BY [SalesRep], [Quarter], [Sales dollars], [Date of sale]

Hope This Helps
Gerald Stanley MCSD