use a query in a query

[bbittman]

I have a query that gets the current session, named GetCurrentSession:

SELECT MAX(Session.[SessionID]) as Sess
FROM [Session];

This query works fine and returns the correct value.

Then, I want to use it to get client purchases from only the current session:

SELECT Purchases.PurchID, Purchases.PartID, Purchases.PurchDate, Purchases.Count, Purchases.PayType, Purchases.Paid, Purchases.TicketNumbers, Purchases.Delivered, Purchases.DeliveredDate, Purchases.PaymentDate, Purchases.PaidOut, Purchases.PaidOutDate, Purchases.Reconciled, Purchases.ReconcildedDate, GetCurrentSession.Sess
FROM Purchases inner join GetCurrentSession
on (Purchases.SessionID)=GetCurrentSession.sess;

The problem is whenever I run this query I get a dialog box 'Enter Parameter Value' for 'GetCurrentSession.Sess.

Is this not allowed? Is there a way to do this?

Thanks,
Shane

 

[theDBguy]

Hi Shane. Yes, this should be possible. Is the name of your first query GetCurrentSession? If so, you might try renaming it just in case it's conflicting with something else. Just a thought...

 

[bbittman]

As you suggested, I renamed by query to qryGetCurrentSession and it worked but I don't know why.

I'm just starting building my queries and, so far, only have the two so I'm not sure what sort of conflict there could have been.

 

[theDBguy]

Hi. Glad to hear you got it working. If you can post a sample copy of your database, we might be able to see where the issue was coming from. Otherwise, I'm just happy it's fixed now. Good luck!