Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I don't have experience in Access, so I need your help. I have a form with
some clients, and a subform for every one of them with a list of employments
they have had. I want to make a querie with the most recent employment, I am
using the MAX to get the recent date but the name of the company and the
address is not coming correctly for the MAX date; so I need a query that
select the recent date and the correct company and addressfor all the clients
in the company. Thank you for your help.
 
Create a query, and instead of using max, sort it by the date field Desc

Select * From TableName Order By DateFieldName Desc

If you want to get only the 5 recent records, then you can use top
Select Top 5 * From TableName Order By DateFieldName Desc
 
Thank you Ofer, but I think I didn't explain enough. I have like 200 customers,

Data base

field1, field2, field3
susi company1 01/22/03
susi company2 12/15/04
robert company1 05/15/02
robert company2 10/27/04
robert company3 04/20/05
maria company1 03/03/01
maria company2 07/13/04


good data base

susi company2 12/15/04
robert company3 04/20/05
maria company2 07/13/04

If I used the top n, it returned the top ones and there are customers with
repeted info, and I only need the recent record of each customer.


Thanks.
 
Lorena,

Do it in 2 steps. First make a query to identify the most recent date
for each customer. The SQL view of such a query would look like this...
SELECT [field1], Max([field3] As LatestDate
FROM [Data base]
GROUP BY [field1]

Let's say you name this query LatestEmployments. Then, make another
query where you join this first query back to the table. The SQL view
if such a query will look something like this...
SELECT [field1], [field2], [field3]
FROM [Data base] INNER JOIN LatestEmployments
ON [Data base].[field1] = LatestEmployments.[field1]
AND [Data base].[field3] = LatestEmployments.LatestDate
 
Steve gave you one option, another option will be

SELECT M1.field1, M1.field2 , M1.field3
FROM TableName AS M1
WHERE M1.field3 In (SELECT Top 1 M2.field3
FROM TableName as M2
WHERE M2.[field1]=M1.[field1]
ORDER BY M2.field3 Desc)

So, if you want the last two entries per cust, all you have to do is change
the top 1 to top 2
 
Thank Ofer, your idea help too.



--
Lorena


Ofer said:
Steve gave you one option, another option will be

SELECT M1.field1, M1.field2 , M1.field3
FROM TableName AS M1
WHERE M1.field3 In (SELECT Top 1 M2.field3
FROM TableName as M2
WHERE M2.[field1]=M1.[field1]
ORDER BY M2.field3 Desc)

So, if you want the last two entries per cust, all you have to do is change
the top 1 to top 2


--
I hope that helped
Good luck


Lorena said:
Thank you Ofer, but I think I didn't explain enough. I have like 200 customers,

Data base

field1, field2, field3
susi company1 01/22/03
susi company2 12/15/04
robert company1 05/15/02
robert company2 10/27/04
robert company3 04/20/05
maria company1 03/03/01
maria company2 07/13/04


good data base

susi company2 12/15/04
robert company3 04/20/05
maria company2 07/13/04

If I used the top n, it returned the top ones and there are customers with
repeted info, and I only need the recent record of each customer.


Thanks.
Click to expand...
Click to expand...
 
Thank you Steve, it works now.



--
Lorena


Steve Schapel said:
Lorena,

Do it in 2 steps. First make a query to identify the most recent date
for each customer. The SQL view of such a query would look like this...
SELECT [field1], Max([field3] As LatestDate
FROM [Data base]
GROUP BY [field1]

Let's say you name this query LatestEmployments. Then, make another
query where you join this first query back to the table. The SQL view
if such a query will look something like this...
SELECT [field1], [field2], [field3]
FROM [Data base] INNER JOIN LatestEmployments
ON [Data base].[field1] = LatestEmployments.[field1]
AND [Data base].[field3] = LatestEmployments.LatestDate

--
Steve Schapel, Microsoft Access MVP
Thank you Ofer, but I think I didn't explain enough. I have like 200 customers,

Data base

field1, field2, field3
susi company1 01/22/03
susi company2 12/15/04
robert company1 05/15/02
robert company2 10/27/04
robert company3 04/20/05
maria company1 03/03/01
maria company2 07/13/04


good data base

susi company2 12/15/04
robert company3 04/20/05
maria company2 07/13/04

If I used the top n, it returned the top ones and there are customers with
repeted info, and I only need the recent record of each customer.


Thanks.
Click to expand...
Click to expand...
 
Back
Top