473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query / Join help please!

Hello Gurus,

I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!

How can I accomplish this in one query? or Can I?

Thanks.

Nov 13 '05 #1
4 1522

<bh***********@mci.com> schreef in bericht news:11**********************@z14g2000cwz.googlegr oups.com...
Hello Gurus,

I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!

How can I accomplish this in one query? or Can I?

Thanks.


The tables have the same structure ?
A UNION quey is what you need like:

Select * FROM tblCurrent
UNION Select * FROM tblPrevious

Paste this in the SQL-view of a new query and look if the result is what you want.
If so, save the query.
Make a new query and use the saved query in the query-grid to make a make-table-query

Arno R

Nov 13 '05 #2
Arno,

I guess I was not clear. Both tables have same structure. What I want
to have in the resultat table is, when they have matches, I want them
in one record; but then differnt records for Left outerjoin and RIGHT
outerjoin.

Arno R wrote:
<bh***********@mci.com> schreef in bericht news:11**********************@z14g2000cwz.googlegr oups.com...
Hello Gurus,

I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!

How can I accomplish this in one query? or Can I?

Thanks.


The tables have the same structure ?
A UNION quey is what you need like:

Select * FROM tblCurrent
UNION Select * FROM tblPrevious

Paste this in the SQL-view of a new query and look if the result is what you want.
If so, save the query.
Make a new query and use the saved query in the query-grid to make a make-table-query

Arno R


Nov 13 '05 #3
Sorry, don't understand.
Maybe you need 3 query's then ? Left-join, Right join and a normal Join ?
But how can you have the matches in one record ??
Can you give a simplified example of a couple of records in your tables AND your desired result ?

Arno R

<bh***********@mci.com> schreef in bericht news:11**********************@g47g2000cwa.googlegr oups.com...
Arno,

I guess I was not clear. Both tables have same structure. What I want
to have in the resultat table is, when they have matches, I want them
in one record; but then differnt records for Left outerjoin and RIGHT
outerjoin.





Arno R wrote:
<bh***********@mci.com> schreef in bericht news:11**********************@z14g2000cwz.googlegr oups.com...
> Hello Gurus,
>
> I need help! I have two table, tblCurrent and tblPrevious. What I
> want to join the tables, and create a new table that have matching
> records from both the tables, plus this new table also includes
> records from tblcurrent that were are not in the tblprevious and also
> records from tblprevious that are not in tblcurrent!
>
> How can I accomplish this in one query? or Can I?
>
> Thanks.


The tables have the same structure ?
A UNION quey is what you need like:

Select * FROM tblCurrent
UNION Select * FROM tblPrevious

Paste this in the SQL-view of a new query and look if the result is what you want.
If so, save the query.
Make a new query and use the saved query in the query-grid to make a make-table-query

Arno R

Nov 13 '05 #4
bh***********@mci.com wrote:
Hello Gurus,

I need help! I have two table, tblCurrent and tblPrevious. What I
want to join the tables, and create a new table that have matching
records from both the tables, plus this new table also includes
records from tblcurrent that were are not in the tblprevious and also
records from tblprevious that are not in tblcurrent!

How can I accomplish this in one query? or Can I?

Thanks.


Perhaps this will get you started:

tblCurrent
CID Data1 Data2
1 X Y
2 A B
3 C D
4 G H

tblPrevious
PID Data1 Data2
1 E F
2 G H

qryLeftRightInner:
SELECT tblCurrent.Data1, tblCurrent.Data2, tblCurrent.CID, Null AS PID
FROM tblCurrent LEFT JOIN tblPrevious ON tblCurrent.Data2 =
tblPrevious.Data2 WHERE tblPrevious.Data2 Is Null UNION SELECT
tblPrevious.Data1, tblPrevious.Data2, Null AS CID, tblPrevious.PID FROM
tblPrevious LEFT JOIN tblCurrent ON tblCurrent.Data2 =
tblPrevious.Data2 WHERE tblCurrent.Data2 IS NULL UNION SELECT
tblCurrent.Data1, tblCurrent.Data2, tblCurrent.CID, tblPrevious.PID
FROM tblCurrent INNER JOIN tblPrevious ON tblCurrent.Data2 =
tblPrevious.Data2 WHERE tblCurrent.Data1 = tblPrevious.Data1 AND
tblCurrent.Data2 = tblPrevious.Data2;

! qryLeftRightInner:
Data1 Data2 CID PID
A B 2
C D 3
E F 1
G H 4 2
X Y 1

qryNewTable:
SELECT tblLeftRightInner.Data1, tblLeftRightInner.Data2,
tblLeftRightInner.CID, tblLeftRightInner.PID INTO tblNewTable FROM
tblLeftRightInner;

I don't see yet how to get it down to one query in an elegant way.
Since the union query does its own select distinct I took out the
distinctrow that normally gets inserted by the unmatched query wizard.
The second LEFT JOIN is really a RIGHT JOIN done backwards since using
the same syntax and swapping table names was easier.

James A. Fortune

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Jim | last post by:
I need help on a query. There is a common titles database for several radio stations Some titles are enabled and some are not enabled for each station. Example of some tables: Titles ...
4
by: Orion | last post by:
Hi, This is kind of last minute, I have a day and a half left to figure this out. I'm working on a project using ms-sqlserver. We are creating a ticket sales system, as part of the system, I...
6
by: Mike | last post by:
I have had alot of problems with my query, I have a two tables of informations and just need a simple Query to get what I want but it is turning out not to be so simple. Table 1 Has the following...
4
by: d.p. | last post by:
Hi all, I'm using MS Access 2003. Bare with me on this description....here's the situation: Imagine insurance, and working out premiums for different insured properties. The rates for calculating...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
3
by: s_wadhwa | last post by:
Hi, I'm trying to convert MS Access 97 .mdb application to Access 2003 .adp application with SQL Server as Backend. I'm having trouble converting Access Query into SQL Query. The Query is...
0
by: mlarson | last post by:
I have a program that worked fine then they needed to be able to also see the empty cells (inmate cells) on a housing unit when they ran the query. So what I had to do was take two tables and...
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
9
by: Frawls | last post by:
Hi I Am am having problems with a stored Procedure that i wrote. Basically whats happening is that the Stored procedure Runs fine when i EXECUTE it in SQL Query analyzer. But when i debug...
3
by: Richard Hollenbeck | last post by:
I hope this isn't too confusing. The following query runs pretty fast by itself, but when I want to use it in a report (pasted below the query), it takes at least fifteen seconds to run! Then I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.