?
Solved

combining two queries

Posted on 2009-04-14
11
Medium Priority
?
348 Views
Last Modified: 2012-05-06
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
Comment
Question by:sahi0002
  • 7
  • 4
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24145334
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
 

Author Comment

by:sahi0002
ID: 24145798
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
 

Author Comment

by:sahi0002
ID: 24145804
PB Listing is the name of my table.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24145847
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
 

Author Comment

by:sahi0002
ID: 24145877
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24146413
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
 

Author Comment

by:sahi0002
ID: 24153892
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24155312
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
 

Author Comment

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

Author Comment

by:sahi0002
ID: 24181413
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
 

Author Comment

by:sahi0002
ID: 24181455
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question