asked on

combining two queries

Hi, I have a database in MS Access.

I have two queries:-

qryLastCA:-
SELECT [PB Listing].[CA No], Max([Created Date]) AS MaxCreated
FROM [PB Listing]
GROUP BY [PB Listing].[CA No];

PBSorted:-
SELECT [PB Listing].[Created Date], [PB Listing].[Contract ID],[PB Listing].[CA No],....
FROM [PB Listing]
WHERE [PB Listing].[CA No] is not null
ORDER BY [PB Listing].[CA No];

I wish to have qryLastCA inside PBSorted as onq query itself.
The qryLastCA is basically to show the latest CA No's only if there are records with same CA No. It will show teh latest one based on the date of creation of the record (Created Date).

Please help

rockiroads

So basically what u want is PBSorted to show that info based on the latest created date?

SELECT [Created Date], [Contract ID], [CA No],....
FROM [PB Listing] as a
WHERE [CA No] is not null
AND [Created Date] IN (SELECT Max([Created Date] FROM [PB Listing] WHERE [CA No] = a.[CA No])
ORDER BY [CA No];

Assuming CA No is unique

Very important to keep table alias (i.e. as a) as this is used in the inner query

sahi0002

ASKER

Hi, there is something wrong somewhere it is giving me syntax error.

Basically let me give you an example:-

there are lots of CA NO's. some can be same like if there are many CA No as 98765, one have Created Date as 1/2/2007, other having 1/2/2008, another having, 1/2/2009, then for that query only the one having date as 1/2/2009 should be seen.

sahi0002

ASKER

PB Listing is the name of my table.

rockiroads

whats the syntax error, I just copied it from your sql where you had .... I assume you have either removed it or populated wit other fields?

SELECT *
FROM [PB Listing] as a
WHERE [CA No] is not null
AND [Created Date] IN (SELECT Max([Created Date] FROM [PB Listing] WHERE [CA No] = a.[CA No])
ORDER BY [CA No]

This should list details of CA No which has the latest created date

sahi0002

ASKER

it is showing missing ), ], or Item in query expression '[CA No] is not null
AND [Created Date] IN (SELECT Max([Created Date] FROM [PB Listing] WHERE [CA No] = a.[CA No])
ORDER BY [CA No]'.

rockiroads

Ah, I see, missing ) in MAX

currently it is
Max([Created Date] FROM

Add the ) before the FROM
i.e.

Max([Created Date]) FROM

sahi0002

ASKER

Hi rockiroads,

placing the bracket, it does not show me any error and saves properly but when i open the query it does not read any of the field.

i.e for field [PB Listing].[Created Date], it becomes [PB Listing].[Created Date] as expr1... and so on.

I have attached a sample database for you to see.
The query name is PBSorted under queries.

Thanks
ExcelUpdate.mdb

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sahi0002

ASKER

Sorry, I never saw when you replied.
I'm just testing it. I'll get back to you soon. :)

sahi0002

ASKER

Hi rockiroads,

Sorry for the late reply. Was out of town. It is working fine removing the fields and keeping only a few but I want all those fields to appear in query PBSorted.
How do I make the query work without trimming the fields to few?

Waiting for your reply :)

sahi0002

ASKER

Sorry again, my mistake.. I had put all the fields as [PB Listing].[field name]
it should have been A.[field name]

its working
Thanks :)