look at 2 tables

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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 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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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
 
Back
Top