How can I join two queries and avoid duplicate records?

[StratfordWard]

Hello: I have two queries I would like to join in access 2003 to create a
third query from which I can run a report or cut/paste into excel, etc.
Each query needs to be linked on on two fields, location and date, and for
any given locaiton and date combination, there can be zero to several
records. I have tried joining
these several different ways and even tried using an intermediary query
containing
only location and date to join each main query.

For example, Query1 contains fields called Location, Date, Item, and QtySold
and Query2 contains fields called Location, Date, PayType, and PayAmt. I
want to produce a query that displays each record from each query only once
BUT may have one or more records display for each location and date.

Results may look like this:

LOCATION, DATE, ITEM, QTYSOLD, PAYTYPE, PAYAMT
1,7/1/2008,9001,42,CASH,50.00
1,7/1/2008,,,CRED,75.00
2,7/2/2008,9001,30,CASH,50.00
2,7/2/2008,9002,40,,

Any help will be appreciated...thanks!
Stratford

 

[bcap]

You haven't got duplicate records in your output. You may have duplicates
in the first two fields, but the subsequent fields are different.

So, what exactly do you want the output to look like?

 

[Bob Barrows [MVP]]

As bcap says: you have no duplicate records in the results you have shown. A
duplicate would mean every single field is duplicated, and would indicate
you do not have the key fields identified and used in the join, causing a
cartesian product. That does not appear to be the case here.

So, again as bcap says: you need to show us what you want the output to look
like. In addition, we need to see the input records (the output from the two
records you are joining) that produced the results you initially posted.

 

[Ron2006]

" I want to produce a query that displays each record from each query
only once
BUT may have one or more records display for each location and date.
"

What you are describing there is a formated report that is printing
certain information on change of location.

A form with a subform with information from table 1 on the main form
and information from table 2 on the subform can also be arranged like
that but NOT in a single query with joined tables.


A single query viewed in datasheet view cannot do what you are asking
there.

Ron

 

[StratfordWard]

Sorry I was not more specific. The example I originally gave was what we do
want the output to look like.

For purposes of my example, here are the records in Query1:
LOCATION, DATE, ITEM,QTYSOLD
1,7/1/2008,9001,42
2,7/2/2008,9001,30
2,7/2/2008,9002,40

And records in Query2:
LOCATION, DATE, PAYTYPE, PAYAMT
1,7/1/2008,CASH,50.00
1,7/1/2008,CRED,75.00
2,7/2/2008,CASH,50.00

Here is what we are getting now:
LOCATION, DATE, ITEM, QTYSOLD, PAYTYPE, PAYAMT
1,7/1/2008,9001,42,CASH,50.00
1,7/1/2008,9001,42,CRED,75.00
2,7/2/2008,9001,30,CASH,50.00
2,7/2/2008,9002,40,CASH,50.00

Here is what we want to get:
LOCATION, DATE, ITEM, QTYSOLD, PAYTYPE, PAYAMT
1,7/1/2008,9001,42,CASH,50.00
1,7/1/2008,,,CRED,75.00
2,7/2/2008,9001,30,CASH,50.00
2,7/2/2008,9002,40,,

In other words, we are trying to create a query that joins two existing
querys on LOCATION and DATE. Each query can have zero to several records for
any given LOCATION and DATE. For any given LOCATION and DATE, the LOCATION
and DATE can repeat as many times as necessary; however, when the sales data
or payment data repeats, it throws off summary totals, etc.

It may or may not help to know that we eventually hope to have a
reconciliation report that is driven by the results...however, right now, we
just need to be able to cut and paste the results into an Excel worksheet.

Again, thanks in advance for any suggestions!
Stratford