Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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
0
sahi0002
Asked:
sahi0002
  • 7
  • 4
1 Solution
 
rockiroadsCommented:
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
0
 
sahi0002Author Commented:
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.
0
 
sahi0002Author Commented:
PB Listing is the name of my table.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rockiroadsCommented:
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
0
 
sahi0002Author Commented:
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]'.
0
 
rockiroadsCommented:
Ah, I see, missing ) in MAX

currently it is
Max([Created Date] FROM

Add the ) before the FROM
i.e.

Max([Created Date]) FROM

0
 
sahi0002Author Commented:
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
0
 
rockiroadsCommented:
that query worked. Loads of fields on display so I trimmed it down to select just some fields

SELECT a.[CA No], a.[Created Date], a.[Contract ID], a.[Account Code], a.[Postal Code], a.[Site ID]
FROM [PB Listing] AS a
WHERE (((a.[CA No]) Is Not Null) AND ((a.[Created Date]) In (SELECT Max([Created Date]) FROM [PB Listing] WHERE [CA No] = a.[CA No])))
ORDER BY a.[CA No]

produced this

CA No      Created Date      Contract ID      Account Code      Postal Code      Site ID
1210598002      15/04/2009 14:22:07      3000                  
1210598002      15/04/2009 14:22:07      3000                  
1212098503      15/04/2009 14:22:07      2000                  
1212198001      15/04/2009 14:22:07      4000                  
1212898003      15/04/2009 14:22:07      50001                  2535
1237187004      15/04/2009 14:22:07      50002                  2494
1238698002      15/04/2009 14:22:07      50003                  2028
1238898001      15/04/2009 14:22:07      50004                  1767
1344798008      15/04/2009 14:22:07      50005                  5651
1393051007      15/04/2009 14:22:07      50006                  2446
1411490005      15/04/2009 14:22:07      50007                  1250

Now I noticed there are two rows if the same CA No and Created Date,

Looking at your table you definitely need a primary key, one that identifies a unique row. You can create just a ID field of type AutoNumber.

So what rules do you have to display just one 1210598002 ?

0
 
sahi0002Author Commented:
Sorry, I never saw when you replied.
I'm just testing it. I'll get back to you soon. :)
0
 
sahi0002Author Commented:
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 :)
0
 
sahi0002Author Commented:
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 :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now