Outer Join Dilema

[Jason]

Here is the problem.

I have two tables.

The first table from the Credit Card Vendor. I have grouped it by
date and it has three fields

Store
Date
AmexAmount


The second table is from the companys General Ledger and also has
three fields.

Store
Date
GLAmount


I want my query to look like...

Store Date AmexAmount GLAmount

I am trying to do a bank reconciliation.

Also, I create a relationship btw the Store and Date when I am
creating my query.

Any advice is GREATLY appreciated.

TIA

Jason
ja****@awdbt.co m

[Bradley]

Perhaps show us the SQL of your query ?
:)
--
regards,

Bradley
Jason wrote:

Here is the problem.

I have two tables.

The first table from the Credit Card Vendor. I have grouped it by
date and it has three fields

Store
Date
AmexAmount


The second table is from the companys General Ledger and also has
three fields.

Store
Date
GLAmount


I want my query to look like...

Store Date AmexAmount GLAmount

I am trying to do a bank reconciliation.

Also, I create a relationship btw the Store and Date when I am
creating my query.

Any advice is GREATLY appreciated.

TIA

Jason
ja****@awdbt.co m

[Jason]

On Fri, 28 May 2004 13:09:26 +1000, "Bradley"
<br*****@REMOVE THIScomcen.com. au> wrote:

Perhaps show us the SQL of your query ?
:)

I am not that advanced. I am using the query builder in access.

Thanks.

[Bradley]

Jason wrote:

On Fri, 28 May 2004 13:09:26 +1000, "Bradley"
<br*****@REMOVE THIScomcen.com. au> wrote:

Perhaps show us the SQL of your query ?
:)

I am not that advanced. I am using the query builder in access.

Thanks.

hehe, just click on the SQL button to view the SQL of your query :)

--
regards,

Bradley

[Edward]

Jason <uc********@yah oo.com> wrote in message news:<3s******* *************** **********@4ax. com>...

Here is the problem.

I have two tables.

The first table from the Credit Card Vendor. I have grouped it by
date and it has three fields

Store
Date
AmexAmount


The second table is from the companys General Ledger and also has
three fields.

Store
Date
GLAmount


I want my query to look like...

Store Date AmexAmount GLAmount

I am trying to do a bank reconciliation.

Also, I create a relationship btw the Store and Date when I am
creating my query.

Any advice is GREATLY appreciated.

TIA

Jason
ja****@awdbt.co m

Try
SELECT Store, Date, AmexAmount, GLAmount FROM [Credit Card Vendor]
INNER JOIN [General Ledger] ON [Credit Card Vendor].Store = [General
Ledger].Store AND
[Credit Card Vendor].Date = [General Ledger].Date

HTH

Edward

[Jason]

On 28 May 2004 04:01:29 -0700, te********@hotm ail.com (Edward) wrote:

Jason <uc********@yah oo.com> wrote in message news:<3s******* *************** **********@4ax. com>...

Here is the problem.

I have two tables.

The first table from the Credit Card Vendor. I have grouped it by
date and it has three fields

Store
Date
AmexAmount

The second table is from the companys General Ledger and also has
three fields.

Store
Date
GLAmount

I want my query to look like...

Store Date AmexAmount GLAmount

I am trying to do a bank reconciliation.

Also, I create a relationship btw the Store and Date when I am
creating my query.

Any advice is GREATLY appreciated.

TIA

Jason
ja****@awdbt.co m

Try
SELECT Store, Date, AmexAmount, GLAmount FROM [Credit Card Vendor]
INNER JOIN [General Ledger] ON [Credit Card Vendor].Store = [General
Ledger].Store AND
[Credit Card Vendor].Date = [General Ledger].Date

HTH

Edward

Thanks. Does that give me all the records they have in common?

I think I need all the records from both to show up...

So one table may have $414.03 on 3/5/04 and the other table doesn't
have anything on 3/5/04, but has the $414.03 on 3/6/04 and vice versa.
So it's like I want two outerjoins.

TIA

Jason

[Bob Quintal]

Jason <uc********@yah oo.com> wrote in
news:25******** *************** *********@4ax.c om:

On 28 May 2004 04:01:29 -0700, te********@hotm ail.com (Edward)
wrote:

Jason <uc********@yah oo.com> wrote in message
news:<3s***** *************** ************@4a x.com>...

Here is the problem.

I have two tables.

The first table from the Credit Card Vendor. I have grouped
it by date and it has three fields

Store
Date
AmexAmount

The second table is from the companys General Ledger and
also has three fields.

Store
Date
GLAmount

I want my query to look like...

Store Date AmexAmount GLAmount

I am trying to do a bank reconciliation.

Also, I create a relationship btw the Store and Date when I
am creating my query.

Any advice is GREATLY appreciated.

TIA

Jason
ja****@awdbt.co m
Try
SELECT Store, Date, AmexAmount, GLAmount FROM [Credit Card
Vendor] INNER JOIN [General Ledger] ON [Credit Card
Vendor].Store = [General Ledger].Store AND
[Credit Card Vendor].Date = [General Ledger].Date

HTH

Edward

Thanks. Does that give me all the records they have in
common?

I think I need all the records from both to show up...

So one table may have $414.03 on 3/5/04 and the other table
doesn't have anything on 3/5/04, but has the $414.03 on 3/6/04
and vice versa. So it's like I want two outerjoins.

TIA

Jason

You are going to have to type a little bit of sql to get your
results.

Open the query builder.close the show tables dialog.
select sql view from the view dropdown on hte toolbar.

paste the following into the window.

SELECT store, date from [credit card vendor]
UNION
SELECT store, date from [General Ledger]

save the query with some name meaningful to you.
UnionExample is meaningful to me.

Now create a new query in the builder.
From the show tables dialog, select UnionExample, credic card
vendor, and general ledger.

drag relationship lines from unionExample.st ore and
UnionExample.Da te to the same fields in credit card vendor.
Double-Click on each of those two lines, and choose the option
which reads "show all records from UnionExample and ...."

Create the relationships between UnionExample and General Ledger,
just like you did with credit card vendor.

put the Store and date fields from UnionExample onto the grid,
and the other fields from the respective table.

save and run your results query.

Bob Quintal
..