Show just one record based on date

[CEV]

I have a form based on a query. The SQL View of the query is shown below.
The report is showing me all records for the PositionNumber. Each
PositionNumber can have more than one record as employees come and go. The
PositionNumber can also have the Schedule or Title changed when a different
employee starts that position. When this happens is when it shows me the
different records for one PositionNumber. I only need it to show me the
record with the most recent DateEnded date. I can add DateEnded to the query
but am unsure how to make look at only the most recent date. I am not
familiar with SQL yet so I have always used the Design View when possible to
create the queries.

SELECT tblPositions.PositionNumber, tblPositions.Department,
tblEmployeePositions.Title, tblPositions.Notes,
tblEmployeePositions.Schedule, tblPositions.Inactive
FROM (tblPositions LEFT JOIN qryFilledPositions ON
tblPositions.PositionNumber = qryFilledPositions.PositionNumber) INNER JOIN
tblEmployeePositions ON tblPositions.PositionNumber =
tblEmployeePositions.PositionNumber
GROUP BY tblPositions.PositionNumber, tblPositions.Department,
tblEmployeePositions.Title, tblPositions.Notes,
tblEmployeePositions.Schedule, tblPositions.Inactive,
qryFilledPositions.PositionNumber
HAVING (((tblPositions.Inactive)=No) AND
((qryFilledPositions.PositionNumber) Is Null));

Thanks,

Chad

 

[tina]

since you're using a Totals query already, try this: open the query in
design view, add the DateEnded field to the grid, and on the Total line for
that field, change from the default GroupBy to Last.

hth

 

[Peter Yang [MSFT]]

Hello Chad,

I understand that you'd like to get most recent record of a specific
positionnumber. For exmample, you have the following query result:

postitionnumber emptitile Dateended

1 a 1/1/2005
1 b 1/1/2006
1 c 1/1/2007
2 d 1/1/2005
2 e 1/3/2006

You'd like to get the result:

1 c 1/1/2007
2 e 1/3/2006

If I'm off-site, please let me know.

You could build query based on the original query. For exmaple:

1. If you have above result as qry1, you could build qry2 as:

SELECT postitionnumber, MAX(Dateended) AS date1 FROM qry1 GROUP BY
postitionnumber;

This shall get the latest dateended with each positionnumber

2. Build qry3 by getting all the fields you want to by joining the original
qry1

SELECT a.postitionnumber, b.emptitile, b.date FROM qry2 AS a INNER JOIN
qry1 AS b ON (a.date1=b.date) AND (a.postitionnumber=b.postitionnumber);

You could get as many fields as you want such as emptilte.

3. Build the report based on qry3.

Please let me know if this is helpful. Feel free to comment if this cannot
meet your requirement. I look forward to your reply. Thank you.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[CEV]

This option did not work. Thanks though!!

Chad

since you're using a Totals query already, try this: open the query in
design view, add the DateEnded field to the grid, and on the Total line
for
that field, change from the default GroupBy to Last.

hth

 

[CEV]

This did what I wanted it to. Thanks very much Peter!!

Chad

 

[Peter Yang [MSFT]]

My Pleasure

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[tina]

yeah, i don't know why i've had this mental block on First and Last with
dates. try it with Max to get the most recent date (or Min to get the oldest
date).

hth