UtterAccess Discussion Forums > Help In Understanding Building Of A Subquery

Full Version: Help In Understanding Building Of A Subquery

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

ccIces

Mar 13 2012, 02:03 PM

Access 2010 (forgot to select version)

I am looking to code a subquery that will look at 2 tables, find the any records that are in table 2 but not in table 1 and then add those records to table 1. Table 2 is not in the same database or location as table1 so I need to add an IN in my sql.
I know that to get the unmatched records I need to write my SQL as

CODE

SELECT (table2.field1, table2.field2, etc)
FROM table2 LEFT JOIN table1 ON table2.field1 = table1.field1
WHERE table1.field1 is null

I then need to include that sub query as part of the append query. Do I also need to alias the table1 as it is used in both the main query and the sub query? Also do I need an IN because of the different location of Table2??

CODE

INSERT into table1 AS Aliastable1 (fields....)
SELECT (sub query)
FROM ??

theDBguy

Mar 13 2012, 02:47 PM

Hi,

Check out Doug's response in this earlier thread.

Just my 2 cents...

ccIces

Mar 14 2012, 10:42 AM

hmm.. I see what he did there, but I still am missing something. I end up with a syntax error in my SQL when I try and test it in query builder.
Here is my SQL concantiation

CODE

strSQL = "INSERT INTO [Employee Data] ( Surname, [Given Name], DOB, Sex, Address, City, Province, Postal, [Group-Level], [Position Number], PRI, UserId, Location, [CS Date], Status, Title, Distance, Accomodation, DFA, CTR, Bilingual, LastUpdated, [User], EmployeeID )" _
& " SELECT (SELECT [;Database = " & cPath & "].Surname, [;Database = " & cPath & "].[Given Name], [;Database = " & cPath & "].DOB, [;Database = " & cPath & "].Sex, [;Database = " & cPath & "].Address, [;Database = " & cPath & "].City, [;Database = " & cPath & "].Province, [;Database = " & cPath & "].Postal, [;Database = " & cPath & "].[Group-Level], [;Database = " & cPath & "].[Position Number], [;Database = " & cPath & "].PRI, [;Database = " & cPath & "].UserId, [;Database = " & cPath & "].Location, [;Database = " & cPath & "].[CS Date], [;Database = " & cPath & "].Status, [;Database = " & cPath & "].Title, [;Database = " & cPath & "].Distance, [;Database = " & cPath & "].Accomodation, [;Database = " & cPath & "].DFA, [;Database = " & cPath & "].CTR, [;Database = " & cPath & "].Bilingual, [;Database = " & cPath & "].LastUpdated, [;Database = " & cPath & "].User, [;Database = " & cPath & "].EmployeeID" _
& " FROM [;Database = " & cPath & "] LEFT JOIN [Employee Data] ON Clng([;Database = " & cPath & "].[PRI]) = [Employee Data].[PRI]" _
& " WHERE ((([Employee Data].PRI) Is Null)))" _
& " FROM [;Database = " & cPath & "];"

and the output of that is

CODE

INSERT INTO [Employee Data] ( Surname, [Given Name], DOB, Sex, Address, City, Province, Postal, [Group-Level], [Position Number], PRI, UserId, Location, [CS Date], Status, Title, Distance, Accomodation, DFA, CTR, Bilingual, LastUpdated, [User], EmployeeID )
SELECT (SELECT [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Surname, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].[Given Name], [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].DOB, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Sex, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Address, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].City, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Province, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Direc
tory\USB Current version\COSS5.mdb].Postal, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].[Group-Level], [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].[Position Number], [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].PRI, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].UserId, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Location, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].[CS Date], [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Status, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Title, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Distance, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Accomodation, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].DFA, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].CTR, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Bilingual, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].LastUpdated, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].User, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].EmployeeID FROM [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb] LEFT JOIN [Employee Data] ON Clng([;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].[PRI]) = [Employee Data].[PRI] WHERE ((([Employee Data].PRI) Is Null)))
FROM [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb]
;

when I run it in sql view of query builder to view the results it prompts me that there is a syntax error. I followed what was posted in RD's reply about using a different syntax for the db that is in another location.

theDBguy

Mar 14 2012, 11:29 AM

Hi,

Does the error give you any clue as to where the syntax error is? If not, I suggest that you work with the query one field at a time. See if you can make it work inserting one field first. If it does, then start adding more fields, fixing any errors until you get them all in.

Just my 2 cents...

ccIces

Mar 14 2012, 11:33 AM

if the indicator is where it stops in the message then it is just pefore the [;Database = " & cPath & "].PRI part in the sub query.

but the debug method you suggested is perfect and thank you for that.

theDBguy

Mar 14 2012, 11:36 AM

QUOTE (ccIces @ Mar 14 2012, 09:33 AM)

if the indicator is where it stops in the message then it is just pefore the [;Database = " & cPath & "].PRI part in the sub query.

Oh, I see. Just remove that part. You probably don't need it. Just use the field names without the table.

QUOTE

but the debug method you suggested is perfect and thank you for that.

Hope that helps...

ccIces

Mar 14 2012, 11:59 AM

I shortened it out and actually discovered that my SQL had the wrong fields.. but after fixing that and trying it again it still gives me a syntax and when I go back to the sql view in query builder it shows that the whole sub-query section that I add is bolded. I think my whole structure for that type of query must be wrong...

Here is the revised SQL view

CODE

INSERT INTO [Employee Data] ( Surname, [Given Name], Sex, Address, City, Province, Postal, PRI, UserId, [CS Date], Status)
SELECT (SELECT [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].EMPE_SNM, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].EMPE_GVN_NM, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].EMPE_GNDR, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].ST_NM, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].CTY_TXT, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].STE_PROV_CD, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].PSTL_CD, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].PRI, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].EMPE_UID, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].EMPE_STRT_DT, [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Status, FROM [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb] LEFT JOIN [Employee Data] ON [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].[PRI] = [Employee Data].[PRI] WHERE ((([Employee Data].PRI) Is Null)))FROM [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb];

the idea of the query is that any updated records would be in the remote table (COSS5.mdb)
So I made the find unmatched query which I then inserted after the SELECT as SELECT (SELECT [;Database = \\ etc...)

is that the right structure for where the sub query should go? After looking back at the link you first showed me, the whole structure seems different...

Also, I should just have the remote table once? like this SELECT [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb] (EMPE_SNM, EMPE_GVN_NM...)?

ccIces

Mar 15 2012, 07:30 AM

Ok, I have been plugging away and I see one of my errors and have corrected it.
I went back to query design view and created my find duplicates query, then made that into an append query. IF I test it in the query builder it works the way I need it to.
The only issue is that I now need to specify that one of the tables in not in the same database.
Here is the sql that works

CODE

INSERT INTO [Employee Data] ( PRI, Sex, Surname, [Given Name], Address, City, Province, Postal, Status, [CS Date], UserId )
SELECT Employee.PRI, Employee.EMPE_GNDR, Employee.EMPE_SNM, Employee.EMPE_GVN_NM, Employee.ST_NM, Employee.CTY_TXT, Employee.STE_PROV_CD, Employee.PSTL_CD, Employee.EMPE_ACTV, Employee.EMPE_STRT_DT, Employee.EMPE_UID
FROM Employee LEFT JOIN [Employee Data] as EMPEDATA1 ON Employee.[PRI] = EMPEDATA1.[PRI]
WHERE (((EMPEDATA1.PRI) Is Null));

The table "Employee" is the table that is located in another location. Would I simply add the IN after the FROM to specify where this table is located? or would that IN represent where the insert into table is located??

ccIces

Mar 15 2012, 08:30 AM

Well, back to Square 1. I thought I almost had it figured out but nothing I tried seems to work.

so, square 1 it is:
I have 2 tables I need to compare and add records that are unmatched to one of the 2 tables.

table1 is Employee and is located in a different database. This table contains all of the records I need.
Employee.PRI, Employee.EMPE_GNDR, Employee.EMPE_SNM, Employee.EMPE_GVN_NM, Employee.ST_NM, Employee.CTY_TXT, Employee.STE_PROV_CD, Employee.PSTL_CD, IIf([EMPE_ACTV]=True,"Active","TOS") AS Status1, Employee.EMPE_STRT_DT, Employee.EMPE_UID
(NOTE: Employee.PRI in this table is a TXT field, EMPE_ACTV is a Boolean field)

Table2 is [Employee Data] and is located in the database that I am working in. This table contains records that may need to be updated from new records added to table1
INSERT INTO [Employee Data] ( PRI, Sex, Surname, [Given Name], Address, City, Province, Postal, Status, [CS Date], UserId )
(NOTE: [Employee Data].PRI is a NUMERIC FIELD, [Employee Date].Status is a TEXT field)

how can I create a SQL string that will find unmatched records between Table1 and Table2 and then append those records to Table2??

ccIces

Mar 15 2012, 10:19 AM

Also,
When I try this in Qery Builder to test ( I imported a copy of the table that exists in another location) I first set the unmatched query whicvh results in this SQL view

CODE

SELECT Employee.PRI, Employee.EMPE_GNDR, Employee.EMPE_SNM, Employee.EMPE_GVN_NM, Employee.ST_NM, Employee.CTY_TXT, Employee.STE_PROV_CD, Employee.PSTL_CD, IIf([EMPE_ACTV]=True,"Active","TOS") AS Expr1, Employee.EMPE_STRT_DT, Employee.EMPE_UID, [Employee Data].PRI
FROM Employee LEFT JOIN [Employee Data] ON Employee.[PRI] = [Employee Data].[PRI]
WHERE (((IIf([EMPE_ACTV]=True,"Active","TOS"))="Active") AND (([Employee Data].PRI) Is Null));

Then I take that and make it an append query which gives me this SQLL view:

CODE

INSERT INTO [Employee Data] ( PRI, Sex, Surname, [Given Name], Address, City, Province, Postal, Status, [CS Date], UserId, PRI )
SELECT Employee.PRI, Employee.EMPE_GNDR, Employee.EMPE_SNM, Employee.EMPE_GVN_NM, Employee.ST_NM, Employee.CTY_TXT, Employee.STE_PROV_CD, Employee.PSTL_CD, IIf([EMPE_ACTV]=True,"Active","TOS") AS Expr1, Employee.EMPE_STRT_DT, Employee.EMPE_UID, [Employee Data].PRI
FROM Employee LEFT JOIN [Employee Data] ON Employee.[PRI] = [Employee Data].[PRI]
WHERE (((IIf([EMPE_ACTV]=True,"Active","TOS"))="Active") AND (([Employee Data].PRI) Is Null));

When I run that I get an error saying Duplicate Output Destination (PRI)
The view shows me the records I need but the append is wrong...

theDBguy

Mar 15 2012, 09:44 PM

Hi,

QUOTE (ccIces @ Mar 14 2012, 09:59 AM)

So I made the find unmatched query which I then inserted after the SELECT as SELECT (SELECT [;Database = \\ etc...)

You should only have one SELECT in your syntax. For example:

INSERT INTO TableName (FieldName)
SELECT FieldName FROM [;Database=...]
WHERE ...

Just my 2 cents...

ccIces

Mar 16 2012, 08:52 AM

still stuck. I have the SQL working when I test it in the query builder with both tables in the same database. When I try to make it work with the one table in another location, it fails to work.
This is how it looks in SQL view in the query builder.

CODE

INSERT INTO [Employee Data] ( PRI, Sex, Surname, [Given Name], Address, City, Province, Postal, Status, [CS Date], UserId)
SELECT Employee.PRI, Employee.EMPE_GNDR, Employee.EMPE_SNM, Employee.EMPE_GVN_NM, Employee.ST_NM, Employee.CTY_TXT, Employee.STE_PROV_CD, Employee.PSTL_CD, IIf([EMPE_ACTV]=True,"Active","TOS") AS Expr1, Employee.EMPE_STRT_DT, Employee.EMPE_UID
FROM Employee LEFT JOIN [Employee Data] ON Employee.[PRI] = [Employee Data].[PRI]
WHERE (((IIf([EMPE_ACTV]=True,"Active","TOS"))="Active") AND (([Employee Data].PRI) Is Null));

Now, I need to make it an sql string and have the location of the table Employee identified by it's location.

CODE

INSERT INTO [Employee Data] ( PRI, Sex, Surname, [Given Name], Address, City, Province, Postal, Status, [CS Date], UserId ) SELECT [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Employee(PRI, EMPE_GNDR, EMPE_SNM, EMPE_GVN_NM, ST_NM, CTY_TXT, STE_PROV_CD, PSTL_CD, EMPE_ACTV, EMPE_STRT_DT, EMPE_UID) FROM [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Employee LEFT JOIN [Employee Data] ON [;Database = \\s60zcffp0001\BDI120$\POEMS Working Directory\USB Current version\COSS5.mdb].Employee.[PRI] = [Employee Data].[PRI] WHERE (((IIf([EMPE_ACTV]=True,'Active','TOS'))='Active') AND (([Employee Data].PRI) Is Null));

This is the SQL result of my string, when I copy this to query builder I get the error invalid bracketing... sigh

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.