Joining Two Queries in Access Without Repeating Records   

Hello: I have two queries I would like to join in access 2003 to create a 
third query from which I can feed a report.  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 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
1,7/2/2008,9001,30,CASH,50.00
1,7/2/2008,9002,40,,

Any help will be appreciated...thanks!
Stratford

date: Wed, 30 Jul 2008 17:11:01 -0700   author:   StratfordWard

Re: Joining Two Queries in Access Without Repeating Records   

"StratfordWard"  wrote in message 
news:AD5B1CC9-C83A-4717-B35F-A7BF42B9657C@microsoft.com...
> Hello: I have two queries I would like to join in access 2003 to create a
> third query from which I can feed a report.  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 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
> 1,7/2/2008,9001,30,CASH,50.00
> 1,7/2/2008,9002,40,,
>
> Any help will be appreciated...thanks!
> Stratford
>
You've asked in a .NET programming group. You'll have better luck in an 
Access group. Here's how to find it.

As you appear to be using a web interface, start here to find an appropriate
group:

Start here,

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx

Click the + beside your language. then scroll down to Office and Desktop
Applications. In Office and Desktop Applications, open the category for 
Access, then find a group there to post in.

date: Wed, 30 Jul 2008 21:08:21 -0500   author:   PvdG42

Re: Joining Two Queries in Access Without Repeating Records   

You are correct! Thank you for letting me know about my mistake.

"PvdG42" wrote:

> 
> "StratfordWard"  wrote in message 
> news:AD5B1CC9-C83A-4717-B35F-A7BF42B9657C@microsoft.com...
> > Hello: I have two queries I would like to join in access 2003 to create a
> > third query from which I can feed a report.  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 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
> > 1,7/2/2008,9001,30,CASH,50.00
> > 1,7/2/2008,9002,40,,
> >
> > Any help will be appreciated...thanks!
> > Stratford
> >
> You've asked in a .NET programming group. You'll have better luck in an 
> Access group. Here's how to find it.
> 
> As you appear to be using a web interface, start here to find an appropriate
> group:
> 
> Start here,
> 
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx
> 
> Click the + beside your language. then scroll down to Office and Desktop
> Applications. In Office and Desktop Applications, open the category for 
> Access, then find a group there to post in.
> 
>  
> 
>

date: Wed, 30 Jul 2008 20:58:01 -0700   author:   StratfordWard