Hi, the following is my SQL stateme 1

[jane30]

Hi, the following is my SQL statement:

SELECT DISTINCTROW Count([QryDesc Master].CLUSCODE) AS Count INTO [UpCurr Desc]
FROM [QryDesc Master] LEFT JOIN [QryDesc Curr] ON [QryDesc Master].MatchKey = [QryDesc Curr].MatchKey
HAVING ((([QryDesc Curr].MatchKey) Is Null));

QryDesc Master & QryDesc Curr are twl select queries. How can I make these three queries into one query?

Any help is greatly appreciated.

 

[Sawatzky]

dear jane,

please post also the other 2 query.

would make it easier for me to think about your prob

regards Astrid

 

[jane30]

I'm sorry, Sawatzky! I guess I was really in a hurry when i posted my first question. Let me clarify my question:

I have two queries, Master and Current. Master select a,b,c , a&b&c as Key from tblMaster; Current select a, b, c a&b&c as Key from tbl2.

tbl2 contains current month information. tblMaster contains day 1 information.

I need a SQL statement to compare both Queries to find all unsync records from both queries, i.e. those records not returned by Master but returned by Current, or not returned by Current but returned by Master. So, is there a way to write a complicated SQL to combine 3 queries together?

Thanks a bunch.



Idealy, records in both tbls should be sync. If not, I need to update either table depends on which table has correct data. tbl1 changes monthly. But I don't know Current selects more records or less because the records in Current may be added or deleted. So, I need a SQL statement to list all the unsync records, no matther which tbl, i.e. those records either not in Master or not in Current. My question is how to write

 

[Sawatzky]

dear jane ,

that was really a challenge but here we go:

to select all keys (you called it match-code) from the 2 table where either the key of the first table is not in the second or v.v.

you could use a union select so you the data from tab1 in key1 and the data from tab2 in key 2:

SELECT tab1.a & tab1.b & tab1.c as key1, tab2.a & tab2.b & tab2.c as key2
FROM tab1 LEFT JOIN tab2 ON (tab1.c = tab2.c) AND (tab1.b = tab2.b) AND (tab1.a = tab2.a)
WHERE (((tab2.a) Is Null) AND ((tab2.b) Is Null) AND ((tab2.c) Is Null)) union
SELECT tab1.a & tab1.b & tab1.c as key1, tab2.a & tab2.b & tab2.c as key2
FROM tab2 LEFT JOIN tab1 ON (tab1.c = tab2.c) AND (tab1.b = tab2.b) AND (tab1.a = tab2.a)
WHERE (((tab1.a) Is Null) AND ((tab1.b) Is Null) AND ((tab1.c) Is Null))

with the following statement you put all data from tab1 that is not in tab2 into tab2

INSERT INTO tab2
SELECT *
FROM tab1
WHERE tab1.a & tab1.b & tab1.c in (
select key1 from [
SELECT tab1.a & tab1.b & tab1.c as key1, tab2.a & tab2.b & tab2.c as key2
FROM tab1 LEFT JOIN tab2 ON (tab1.c = tab2.c) AND (tab1.b = tab2.b) AND (tab1.a = tab2.a)
WHERE (((tab2.a) Is Null) AND ((tab2.b) Is Null) AND ((tab2.c) Is Null)) union
SELECT tab1.a & tab1.b & tab1.c as key1, tab2.a & tab2.b & tab2.c as key2
FROM tab2 LEFT JOIN tab1 ON (tab1.c = tab2.c) AND (tab1.b = tab2.b) AND (tab1.a = tab2.a)
WHERE (((tab1.a) Is Null) AND ((tab1.b) Is Null) AND ((tab1.c) Is Null))]. as f);

the other way round you would be like this

INSERT INTO tab1
SELECT *
FROM tab2
WHERE tab2.a & tab2.b & tab2.c in (
select key2 from [
SELECT tab1.a & tab1.b & tab1.c as key1, tab2.a & tab2.b & tab2.c as key2
FROM tab1 LEFT JOIN tab2 ON (tab1.c = tab2.c) AND (tab1.b = tab2.b) AND (tab1.a = tab2.a)
WHERE (((tab2.a) Is Null) AND ((tab2.b) Is Null) AND ((tab2.c) Is Null)) union
SELECT tab1.a & tab1.b & tab1.c as key1, tab2.a & tab2.b & tab2.c as key2
FROM tab2 LEFT JOIN tab1 ON (tab1.c = tab2.c) AND (tab1.b = tab2.b) AND (tab1.a = tab2.a)
WHERE (((tab1.a) Is Null) AND ((tab1.b) Is Null) AND ((tab1.c) Is Null))]. as f);

that would resync both tables. In acces there is AFAIK no way to combine the last 2 into 1 query.

HTH

btw: you told me that the two tables are regularly out of sync, how does it come?

regards Astrid