By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

Outer Join Dilema

P: n/a
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.com
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.com



Nov 13 '05 #2

P: n/a
On Fri, 28 May 2004 13:09:26 +1000, "Bradley"
<br*****@REMOVETHIScomcen.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.
Nov 13 '05 #3

P: n/a
Jason wrote:
On Fri, 28 May 2004 13:09:26 +1000, "Bradley"
<br*****@REMOVETHIScomcen.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
Nov 13 '05 #4

P: n/a
Jason <uc********@yahoo.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.com


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
Nov 13 '05 #5

P: n/a
On 28 May 2004 04:01:29 -0700, te********@hotmail.com (Edward) wrote:
Jason <uc********@yahoo.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.com


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
Nov 13 '05 #6

P: n/a
Jason <uc********@yahoo.com> wrote in
news:25********************************@4ax.com:
On 28 May 2004 04:01:29 -0700, te********@hotmail.com (Edward)
wrote:
Jason <uc********@yahoo.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.com
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.store and
UnionExample.Date 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
..



Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.