Problem Using Query As Source

[TastyWheat]

Here's the root of all my troubles:

SELECT EmployeeID, NewStoreID As HomeStore
FROM [Store Change] AS SC
WHERE EffectiveDate=(
     SELECT Max(EffectiveDate)
     FROM [Store Change]
     WHERE SC.EmployeeID=EmployeeID
);

This query works perfectly fine by itself. It's supposed to find the most recent store ID for each employee and it does. However, if I use that query inside another query I get the error, "The Microsoft Jet database engine does not recognize 'SC.EmployeeID' as a valid field name or expression." I've tried changing the original query slightly, but there isn't much I can do without ruining my functionality.

 

[FoFa]

Don't use it inside another query. Just join it to another query instead.

 

[ByteMyzer]

Try:

SELECT SC.EmployeeID, SC.NewStoreID As HomeStore
FROM [Store Change] AS SC
WHERE SC.EffectiveDate=(
     SELECT Max(SC2.EffectiveDate)
     FROM [Store Change] AS SC2
     WHERE SC2.EmployeeID=SC.EmployeeID
);

 

[TastyWheat]

I didn't think about this at first, but it seems like important information now. I'm trying to join two queries, one of which is a crosstab query. The solution is supposed to be to fill in the Parameters, but I don't know what to put or where to put it.

 

[TastyWheat]

Well, I give up. I really wanted to have all of the important information in one query. I honestly don't see why Access is making such a fuss. So it looks like I'll have two seperate queries and perform DLookups on one of them (joining them in the form would give me the same trouble).

For those of you who are interested I have one last gigantic query that should've done everything if it wasn't for that error #3070:

TRANSFORM Count([Class Attendance].DateAttended) AS CountOfDateAttended
SELECT Temp3.FirstName, Temp3.LastName, Temp3.HomeStore, Temp3.LevelName, Temp3.TestLabel, Temp3.Title, Temp3.EID
FROM [Class Attendance]
RIGHT JOIN (
     SELECT Temp2.FirstName, Class.CID, Class.ShortName, Temp2.LastName, Temp2.HomeStore, Temp2.LevelName, Temp2.TestLabel, Temp2.Title, Temp2.EID
     FROM Class, (
          SELECT Employee.FirstName, Employee.LastName, [Store Change].NewStoreID AS HomeStore, [Screening Level].LevelName, [Testing Level].TestLabel, Position.Title, Employee.EID
          FROM [Testing Level]
          RIGHT JOIN (
               [Screening Level]
               RIGHT JOIN (
                    [Position] RIGHT JOIN (
                         (Employee
                         LEFT JOIN [Position Change]
                         ON Employee.EID = [Position Change].EmployeeID)
                         LEFT JOIN [Store Change]
                         ON Employee.EID = [Store Change].EmployeeID)
                    ON Position.PID = [Position Change].NewPositionID)
               ON [Screening Level].SLID = Employee.ScreeningLevel)
          ON [Testing Level].TLID = Employee.TestLevel
     WHERE (
          (
               ([Store Change].EffectiveDate)=(
                    SELECT MAX(EffectiveDate)
                    FROM [Store Change] As SC1
                    WHERE SC1.EmployeeID=[Store Change].[EmployeeID])
               )
          AND (
               ([Position Change].EffectiveDate)=(
                    SELECT MAX(EffectiveDate)
                    FROM [Position Change] AS Temp1
                    WHERE Temp1.EmployeeID=[Position Change].EmployeeID)
          )
     )) AS Temp2) AS Temp3
ON ([Class Attendance].ClassID = Temp3.CID) AND ([Class Attendance].EmployeeID = Temp3.EID)
GROUP BY Temp3.FirstName, Temp3.LastName, Temp3.HomeStore, Temp3.LevelName, Temp3.TestLabel, Temp3.Title, Temp3.EID, Temp3.CID
PIVOT Temp3.ShortName;

There are absolutely no queries inside of it, just tables.

 

[TastyWheat]

Done Deal!

I can't believe I didn't try this earlier. This was the piece that kept giving me a lot of trouble:

([Store Change].EffectiveDate)=(
     SELECT MAX(EffectiveDate)
     FROM [Store Change] As SC1
     WHERE SC1.EmployeeID=[Store Change].EmployeeID
)

So I changed it to this:

([Store Change].EffectiveDate)=(
     DMax("[EffectiveDate]", "[Store Change]",
          "[Store Change].EmployeeID = " & [Store Change].EmployeeID)
)

I was avoiding the use of functions because I assumed it would be noticably slower. I really don't see any delay in displaying the records. Even though the original query worked (before joining to the crosstab query) I guess eventually it got confused by the external reference ([Store Change].EmployeeID).