look at 2 tables

G

Guest

I have 2 tables. I would like to write a query that compares the 2 tables,
and puts ALL records from each table which do not appear in BOTH into a NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan account number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account number of 1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan account number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account number of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan account number
of 1
table 2 (today's data) may contain a record with loan account number of 1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
 
J

John Spencer

This looks like two unmatched queries that would be unioned. The query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be created
in the SQL (text) view.
 
G

Guest

I was going to try this, but it looks like the query would create 2 tables,
based on "In table 1" and "In table 2". I only want to create one table. I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want values from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



John Spencer said:
This looks like two unmatched queries that would be unioned. The query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be created
in the SQL (text) view.

geebee said:
I have 2 tables. I would like to write a query that compares the 2 tables,
and puts ALL records from each table which do not appear in BOTH into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan account number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account number of 1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan account
number
of 1
table 2 (today's data) may contain a record with loan account number of 1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
 
J

John Spencer

If you don't need to identify the source tables, then remove the two
phrases. I thought you would want to know which table had the data, so I
added in a calculated field to tell you that.


geebee said:
I was going to try this, but it looks like the query would create 2 tables,
based on "In table 1" and "In table 2". I only want to create one table.
I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want values
from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



John Spencer said:
This looks like two unmatched queries that would be unioned. The query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be
created
in the SQL (text) view.

geebee said:
I have 2 tables. I would like to write a query that compares the 2
tables,
and puts ALL records from each table which do not appear in BOTH into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account number of
1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account
number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan account
number
of 1
table 2 (today's data) may contain a record with loan account number of
1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks John! Now I want to append the query results to a table called
tbl_Masterpop_main. Once again, here is the query you suggested:

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;

How should I amend this to make the wuery results be appended to a table
called tbl_masterpop main. Please note that I do not want records to be
appended that already exist in the tbl_masterpop_main

Thanks in advance,
geebee


John Spencer said:
If you don't need to identify the source tables, then remove the two
phrases. I thought you would want to know which table had the data, so I
added in a calculated field to tell you that.


geebee said:
I was going to try this, but it looks like the query would create 2 tables,
based on "In table 1" and "In table 2". I only want to create one table.
I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want values
from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



John Spencer said:
This looks like two unmatched queries that would be unioned. The query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be
created
in the SQL (text) view.

I have 2 tables. I would like to write a query that compares the 2
tables,
and puts ALL records from each table which do not appear in BOTH into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account number of
1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account
number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan account
number
of 1
table 2 (today's data) may contain a record with loan account number of
1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

The best way is to save the union query and use the saved query in an Append
query.

Assumption:
Saved query named qUniAccounts

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM qUniAccounts as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

There is the possibility that you can do this all in one query, however it
stands a good chance of failing due to your field names containing
characters other than A-Z and the underscore and therefore requiring
brackets around the field name(s). The following might work, but the need
to use brackets [] inside the subquery in the FROM clause will probably
cause Access to generate an error message.

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM
(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)
as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

geebee said:
Thanks John! Now I want to append the query results to a table called
tbl_Masterpop_main. Once again, here is the query you suggested:

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;

How should I amend this to make the wuery results be appended to a table
called tbl_masterpop main. Please note that I do not want records to be
appended that already exist in the tbl_masterpop_main

Thanks in advance,
geebee


John Spencer said:
If you don't need to identify the source tables, then remove the two
phrases. I thought you would want to know which table had the data, so I
added in a calculated field to tell you that.


geebee said:
I was going to try this, but it looks like the query would create 2
tables,
based on "In table 1" and "In table 2". I only want to create one
table.
I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want values
from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



:

This looks like two unmatched queries that would be unioned. The
query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be
created
in the SQL (text) view.

I have 2 tables. I would like to write a query that compares the 2
tables,
and puts ALL records from each table which do not appear in BOTH
into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account number
of
1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account
number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan
account
number
of 1
table 2 (today's data) may contain a record with loan account number
of
1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

AWESOME. Now, one more thing...
I ran some tests to find out that I need to enter another criteria to the
query. For example, here is the original query:

(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)

Now, I want to add add to the original query results those records in which
the [loan acct #] may not appear in tbl_masterpop or tbl_masterpop_new AND
which appear in tbl_archive; in other words, the original query lists all
those records whole [loan acct #] did not appear in both tables tbl_masterpop
and tbl_masterpop_new. but i also want those records which have a [loan acct
#] in tbl_archive

thanks in advance,
geebee


John Spencer said:
The best way is to save the union query and use the saved query in an Append
query.

Assumption:
Saved query named qUniAccounts

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM qUniAccounts as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

There is the possibility that you can do this all in one query, however it
stands a good chance of failing due to your field names containing
characters other than A-Z and the underscore and therefore requiring
brackets around the field name(s). The following might work, but the need
to use brackets [] inside the subquery in the FROM clause will probably
cause Access to generate an error message.

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM
(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)
as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

geebee said:
Thanks John! Now I want to append the query results to a table called
tbl_Masterpop_main. Once again, here is the query you suggested:

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;

How should I amend this to make the wuery results be appended to a table
called tbl_masterpop main. Please note that I do not want records to be
appended that already exist in the tbl_masterpop_main

Thanks in advance,
geebee


John Spencer said:
If you don't need to identify the source tables, then remove the two
phrases. I thought you would want to know which table had the data, so I
added in a calculated field to tell you that.


I was going to try this, but it looks like the query would create 2
tables,
based on "In table 1" and "In table 2". I only want to create one
table.
I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want values
from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



:

This looks like two unmatched queries that would be unioned. The
query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be
created
in the SQL (text) view.

I have 2 tables. I would like to write a query that compares the 2
tables,
and puts ALL records from each table which do not appear in BOTH
into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account number
of
1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account
number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan
account
number
of 1
table 2 (today's data) may contain a record with loan account number
of
1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Sorry, you lost me in the explanation of what you want to do.

I think you want to modify the union query so that it returns only records
in tbl_masterPop that are in tbl_archive and are not in tbl_MasterPop_New
and
records in tbl_MasterPop_New that are in tbl_archive and not in
tbl_MasterPop.

If that is true then you need to do something like the following fro each
half of the UNION

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM (tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #])
INNER JOIN tbl_Archive On tbl_masterpop.[Loan Acct #] =tbl_Archive.[Loan
Acct #] =
WHERE tbl_masterpop_new.[Loan Acct #] is Null

You can try the above as a standalone query to see if it works. If it does
then use it in the UNION.

geebee said:
AWESOME. Now, one more thing...
I ran some tests to find out that I need to enter another criteria to the
query. For example, here is the original query:

(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)

Now, I want to add add to the original query results those records in
which
the [loan acct #] may not appear in tbl_masterpop or tbl_masterpop_new AND
which appear in tbl_archive; in other words, the original query lists all
those records whole [loan acct #] did not appear in both tables
tbl_masterpop
and tbl_masterpop_new. but i also want those records which have a [loan
acct
#] in tbl_archive

thanks in advance,
geebee


John Spencer said:
The best way is to save the union query and use the saved query in an
Append
query.

Assumption:
Saved query named qUniAccounts

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM qUniAccounts as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

There is the possibility that you can do this all in one query, however
it
stands a good chance of failing due to your field names containing
characters other than A-Z and the underscore and therefore requiring
brackets around the field name(s). The following might work, but the
need
to use brackets [] inside the subquery in the FROM clause will probably
cause Access to generate an error message.

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM
(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)
as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

geebee said:
Thanks John! Now I want to append the query results to a table called
tbl_Masterpop_main. Once again, here is the query you suggested:

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;

How should I amend this to make the wuery results be appended to a
table
called tbl_masterpop main. Please note that I do not want records to
be
appended that already exist in the tbl_masterpop_main

Thanks in advance,
geebee


:

If you don't need to identify the source tables, then remove the two
phrases. I thought you would want to know which table had the data,
so I
added in a calculated field to tell you that.


I was going to try this, but it looks like the query would create 2
tables,
based on "In table 1" and "In table 2". I only want to create one
table.
I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want
values
from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



:

This looks like two unmatched queries that would be unioned. The
query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be
created
in the SQL (text) view.

I have 2 tables. I would like to write a query that compares the
2
tables,
and puts ALL records from each table which do not appear in BOTH
into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan
account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account
number
of
1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan
account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account
number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan
account
number
of 1
table 2 (today's data) may contain a record with loan account
number
of
1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Almost there (I hope). I want those [loan acct #] that are in neither
tbl_masterpop nor tbl_masterpop_new, but in tbl_archive. I now have the
following:

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
INNER JOIN tbl_Archive
ON tbl_masterpop.[Loan Acct #] =tbl_Archive.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
INNER JOIN tbl_Archive
ON tbl_masterpop_new.[Loan Acct #] =tbl_Archive.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;

But now I am getting a syntax error (missing operator).

YIKES. My brain is fried. I dont know what to do

thanks in advance,
geebee


John Spencer said:
Sorry, you lost me in the explanation of what you want to do.

I think you want to modify the union query so that it returns only records
in tbl_masterPop that are in tbl_archive and are not in tbl_MasterPop_New
and
records in tbl_MasterPop_New that are in tbl_archive and not in
tbl_MasterPop.

If that is true then you need to do something like the following fro each
half of the UNION

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM (tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #])
INNER JOIN tbl_Archive On tbl_masterpop.[Loan Acct #] =tbl_Archive.[Loan
Acct #] =
WHERE tbl_masterpop_new.[Loan Acct #] is Null

You can try the above as a standalone query to see if it works. If it does
then use it in the UNION.

geebee said:
AWESOME. Now, one more thing...
I ran some tests to find out that I need to enter another criteria to the
query. For example, here is the original query:

(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)

Now, I want to add add to the original query results those records in
which
the [loan acct #] may not appear in tbl_masterpop or tbl_masterpop_new AND
which appear in tbl_archive; in other words, the original query lists all
those records whole [loan acct #] did not appear in both tables
tbl_masterpop
and tbl_masterpop_new. but i also want those records which have a [loan
acct
#] in tbl_archive

thanks in advance,
geebee


John Spencer said:
The best way is to save the union query and use the saved query in an
Append
query.

Assumption:
Saved query named qUniAccounts

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM qUniAccounts as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

There is the possibility that you can do this all in one query, however
it
stands a good chance of failing due to your field names containing
characters other than A-Z and the underscore and therefore requiring
brackets around the field name(s). The following might work, but the
need
to use brackets [] inside the subquery in the FROM clause will probably
cause Access to generate an error message.

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT Q.[Loan Acct#], Q.[Status]
FROM
(SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null)
as Q LEFT JOIN tbl_MasterPop_Main as M
ON Q.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null

Thanks John! Now I want to append the query results to a table called
tbl_Masterpop_main. Once again, here is the query you suggested:

SELECT "In Table 1" AS Source
, tbl_masterpop.[Loan Acct #]
, tbl_masterpop.[Status]
FROM tbl_masterpop LEFT JOIN tbl_masterpop_new
ON tbl_masterpop.[Loan Acct #] = tbl_masterpop_new.[Loan Acct #]
WHERE tbl_masterpop_new.[Loan Acct #] is Null
UNION ALL SELECT "In Table 2" as Source
, tbl_masterpop_new.[Loan Acct #]
, tbl_masterpop_new.[Status]
FROM tbl_masterpop_new LEFT JOIN tbl_masterpop
ON tbl_masterpop_new.[Loan Acct #] = tbl_masterpop.[Loan Acct #]
WHERE tbl_masterpop.[Loan Acct #] is Null;

How should I amend this to make the wuery results be appended to a
table
called tbl_masterpop main. Please note that I do not want records to
be
appended that already exist in the tbl_masterpop_main

Thanks in advance,
geebee


:

If you don't need to identify the source tables, then remove the two
phrases. I thought you would want to know which table had the data,
so I
added in a calculated field to tell you that.


I was going to try this, but it looks like the query would create 2
tables,
based on "In table 1" and "In table 2". I only want to create one
table.
I
am dealing with tbl_masterpiece and tbl_masterpiece_new. I want
values
from
these 2 tables to be put in tbl_masterpiece_main

I think the "In table 1" and the "In table 2" threw me off.

Please explain

Thanks in advance,
geebee



:

This looks like two unmatched queries that would be unioned. The
query
would look something like the following.

SELECT "In Table 1" as Source
, [Loan Account Number]
, [Status]
FROM Table1 LEFT JOIN Table2
ON Table1.[Loan Account Number] = Table2.[Loan Account Number]
WHERE Table2.[Loan Account Number] is Null
UNION ALL
SELECT "In Table 2" as Source
, [Loan Account Number]
, [Status]
FROM Table2 LEFT JOIN Table1
ON Table2.[Loan Account Number] = Table1.[Loan Account Number]
WHERE Table1.[Loan Account Number] is Null

This type of query cannot be created in the query grid. It must be
created
in the SQL (text) view.

I have 2 tables. I would like to write a query that compares the
2
tables,
and puts ALL records from each table which do not appear in BOTH
into a
NEW
table. For examples:

example 1:
* table 1 (yesterday's data) may contain a record with loan
account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may contain a record with loan account
number
of
1
with a status of "OLD"
I DO NOT want this record appended to the new table.

example 2:
* table 1 (yesterday's data) may contain a record with loan
account
number
of 1 with a status of "CURRENT".
table 2 (today's data) may NOT contain a record with loan account
number
of
1 at all
I DO want this record appended to the new table.

example 3:
table 1 (yesterday's data) may NOT contain a record with loan
account
number
of 1
table 2 (today's data) may contain a record with loan account
number
of
1
with a status of "CURRENT"
I DO want this record appended to the new table.

I would like to write ONE query, if possible.

HOW?

Thanks in advance,
geebee
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Then you need to add another UNION ALL to the original query and forget
about adding tbl_archive to those queries or better

Why don't you just Add records from tbl_Archive to tbl_Masterpop_Main after
you've added from your other two tables. That will be simpler than trying
to build a more complex query and it might be faster.

INSERT INTO tbl_Masterpop_Main ([Loan Acct#], [Status])
SELECT A.[Loan Acct#], A.[Status]
FROM tbl_Archive as A LEFT JOIN tbl_MasterPop_Main as M
ON A.[Loan Acct#] = M.[Loan Acct#]
WHERE M.[Loan Acct#] is Null



geebee said:
Almost there (I hope). I want those [loan acct #] that are in neither
tbl_masterpop nor tbl_masterpop_new, but in tbl_archive. I now have the
following:
Click to expand...
SNIP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complex Query to count Loan Number 1
append only if [status] is different 3
Sumif function in Access 2
query not acting right 5
unique loan acct #s 3
Parameter query not working correct. 1
dlookup problems 3
DUPLICATE QUERY results 1

Top