Solved

combining two queries

Posted on 2009-04-14
11
319 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Touch screen experience for personal computers, improved security, and performance have made Windows 8 a great hit amongst users. If you are an Outlook user and thinking of or have upgraded to Win 8 or 8.1, then here are some guidelines that may pro…
It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

860 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