RE: [MS_AccessPros] Using an alias'd table to join/Subqueries not cutting the mustard :/

It seems there should be an "AND" rather than a comma in the LEFT JOIN at
LEFT JOIN tblEmployeeCompliance ON A.EmplID = tblEmployeeCompliance.EmplID ###AND### A.ComplianceDescr = tblEmployeeCompliance.ComplianceDescr
Duane HookomMS Access MVPTo: MS_Access_Professionals@yahoogroups.com
From: leevt99@yahoo.com
Date: Wed, 4 Apr 2012 22:40:54 +0000
Subject: [MS_AccessPros] Using an alias'd table to join/Subqueries not cutting the mustard :/

Hi,

I am trying to do an insert query based on a table value, and a constant. I tried using a join to eliminate rows i don't need. It works in code (when it doesn't corrupt) but won't compile as a query because it doesn't recognize the alias'd table.

I have an employee table and a compliance table. I need to add employees to the table based on certain criteria.

The original query is:

INSERT INTO tblEmployeeCompliance(EmplID, ComplianceDescr)

SELECT A.EmplID, A.ComplianceDescr

FROM (SELECT EmplID, HireDate 'Information Security' as ComplianceDescr FROM tblEmployee) as A

LEFT JOIN tblEmployeeCompliance ON A.EmplID = tblEmployeeCompliance.EmplID, A.ComplianceDescr = tblEmployeeCompliance.ComplianceDescr

WHERE tblEmployeeCompliance.EmplID is null and tblEmployeeCompliance.ComplianceDescr is null

AND A.HireDate > #1/1/2011#

This runs fine in my dev version, then I import it to a new thing to send to test and it corrupts the database 100% of the time.

So I tried pasting the SQL into a query and doing a db.execute("testquery") and it doesn't recognize the Alias'd table in the compiled query.

Then I tried doing a subquery appraoch:

INSERT INTO tblEmployeeCompliance(EmplID, ComplianceDescr)

SELECT EmplID, 'Information Security' as ComplianceDescr

FROM tblEmployee

WHERE EmplID NOT IN(

Select EmplID

FROM tblEmployeeCompliance WHERE ComplianceDescr = 'Information Security')

AND HireDate > #1/1/2011#

I was running this on my local drive and it took close to 5 minutes to run, using about 1/4 of the data that's in production and test. It would be unbearably slow in test/prod.

I have to run 13 versions of this query because of different "Where" criteria for each compliance type so even running 5 minutes at a time 13 time is still over an hour. How can I get this thing to work without corrupting and being super supremely slow?

Thanks

-Lee

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/