Kamis, 05 April 2012

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

 

Bleh, I actually have an "And" in the actual query, but when I was typing it in I must have just put a comma for some reason. :/

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
>
> 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@...
> 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]
>

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar