Query Breaks when Database Closed and Opened 1

[Elysynn]

Hello,

I am hoping you can help me with a small problem. Below are two pieces of SQL. The first is the way it should be, the second is what happens to it after I close and open the database.

BEFORE

SELECT qryNode1.UserNumber, qryNode1.type_id, dbo_user_aliases.alias
FROM qryNode1 INNER JOIN dbo_user_aliases ON (qryNode1.UserNumber = dbo_user_aliases.user_id) AND (qryNode1.type_id = dbo_user_aliases.type_id) AND (qryNode1.last_activity = dbo_user_aliases.last_activity);

AFTER

SELECT A.last_activity, A.user_id AS UserNumber, A.type_id
FROM qryNode AS A INNER JOIN [SELECT user_id, Max(last_activity) AS LastUpdate FROM qryNode GROUP BY user_id]. AS L ON (A.user_id = L.user_id) AND (A.last_activity = L.LastUpdate);

This is problematic because other queries function off of this one. It isn't hard to fix, but in the interest of efficiency, I don't want to have to fix it every day. Any advice you can offer is appreciated.

Thanks,
Elysynn

 

[Golom]

Are you sure you posted the right stuff? They look like completely different queries to me.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Elysynn]

My apologies this should be the Before query

SELECT A.last_activity, A.user_id AS UserNumber, A.type_id
FROM qryNode AS A INNER JOIN (SELECT user_id, Max(last_activity) AS LastUpdate FROM qryNode GROUP BY user_id) AS L ON (A.user_id = L.user_id) AND (A.last_activity = L.LastUpdate);

Please disregard the BEFORE in the previous post...

Thanks,
Elysynn

 

[Golom]

Yes, that is annoying isn't it? Try creating the subquery as a separate query

SELECT user_id, Max(last_activity) AS LastUpdate 
FROM qryNode GROUP BY user_id

called qryMaxActivity (for example) then

SELECT A.last_activity, A.user_id AS UserNumber, A.type_id
FROM qryNode AS A INNER JOIN qryMaxActivity AS L 
     ON  (A.user_id = L.user_id) 
     AND (A.last_activity = L.LastUpdate);

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Elysynn]

Thanks, Golom. That seems to have done the trick. So the question becomes - why does Access do that annoying thing in the first place?

-Elysynn

 

[Golom]

Because references like this

FROM [;database=C:\SomeDir\Somedb.mdb;PWD=Password].TableName ...

are possible. For some reason, Access seems to think that embedded subqueries in a FROM clause are references to some external connection that uses the [...]. syntax. I think that MS needs to fix that in a service release.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]