[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

combining two queries

Posted on 2009-04-14
11
Medium Priority
?
344 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

656 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