Solved

combining two queries

Posted on 2009-04-14
11
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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!

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

749 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