return fields associated with max value record

  • Thread starter Thread starter klkropf
  • Start date Start date
K

klkropf

I have a query that returns the max date value correctly, but I need it to
return the other data associated with that record. I would like to do it all
in one query.

tblTransferData houses the ID and AsgStartDate and is joined to
tblTransferSnapShot by ID.
tblTransferSnapShot holds the Snaphot Dates that I'm returning the max
value. I need the other data associated with this max Snapshot Date record,
such as Band and Title.

Below is the query so far. Thanks

SELECT tblTransferData.ID, Max(tblTransferSnapShot.[Snapshot Date]) AS
[MaxOfSnapshot Date]
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE (((tblTransferData.AsgEndDate)>[Snapshot Date]))
GROUP BY tblTransferData.ID;
 
I have a query that returns the max date value correctly, but I need it to
return the other data associated with that record. I would like to do it all
in one query.

tblTransferData houses the ID and AsgStartDate and is joined to
tblTransferSnapShot by ID.
tblTransferSnapShot holds the Snaphot Dates that I'm returning the max
value. I need the other data associated with this max Snapshot Date record,
such as Band and Title.

Below is the query so far. Thanks

SELECT tblTransferData.ID, Max(tblTransferSnapShot.[Snapshot Date]) AS
[MaxOfSnapshot Date]
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE (((tblTransferData.AsgEndDate)>[Snapshot Date]))
GROUP BY tblTransferData.ID;
Click to expand...

You need a Subquery as a criterion:

SELECT tblTransferData.ID, <other fields>
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT Max([X].[AsgEndDate]) FROM
tblTransferData AS X WHERE X.AsgEndDate > [Snapshot Date])
GROUP BY tblTransferData.ID;
 
Thanks for your response.

I used your logic, but I still must be doing something incorrectly. I added
the new field 'Band' and I get the error, You tried to execute a query that
does not include 'Band' as part of the aggregate function.


SELECT tblTransferData.ID, tblTransferSnapShot.Band
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date])
GROUP BY tblTransferData.ID;



John W. Vinson said:
I have a query that returns the max date value correctly, but I need it to
return the other data associated with that record. I would like to do it all
in one query.

tblTransferData houses the ID and AsgStartDate and is joined to
tblTransferSnapShot by ID.
tblTransferSnapShot holds the Snaphot Dates that I'm returning the max
value. I need the other data associated with this max Snapshot Date record,
such as Band and Title.

Below is the query so far. Thanks

SELECT tblTransferData.ID, Max(tblTransferSnapShot.[Snapshot Date]) AS
[MaxOfSnapshot Date]
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE (((tblTransferData.AsgEndDate)>[Snapshot Date]))
GROUP BY tblTransferData.ID;
Click to expand...

You need a Subquery as a criterion:

SELECT tblTransferData.ID, <other fields>
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT Max([X].[AsgEndDate]) FROM
tblTransferData AS X WHERE X.AsgEndDate > [Snapshot Date])
GROUP BY tblTransferData.ID;
Click to expand...
 
Thanks for your response.

I used your logic, but I still must be doing something incorrectly. I added
the new field 'Band' and I get the error, You tried to execute a query that
does not include 'Band' as part of the aggregate function.


SELECT tblTransferData.ID, tblTransferSnapShot.Band
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date])
GROUP BY tblTransferData.ID;
Click to expand...

What are you doing with the Band? What does it mean?

You'll need to either Group By it, or use the Where "totals" line option to
use it for criteria, or use some other aggregate function for it. Since I
don't know what it is or what you want to do with it, I can't say!
 
Band is a job band, so just a letter. I can't group by it because there are
different values for different dates. I want to display the band associated
with the max date row I'm returning. If I use the where criteria it says it
can't display the field where I entered Where in the total field.

John W. Vinson said:
Thanks for your response.

I used your logic, but I still must be doing something incorrectly. I added
the new field 'Band' and I get the error, You tried to execute a query that
does not include 'Band' as part of the aggregate function.


SELECT tblTransferData.ID, tblTransferSnapShot.Band
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date])
GROUP BY tblTransferData.ID;
Click to expand...

What are you doing with the Band? What does it mean?

You'll need to either Group By it, or use the Where "totals" line option to
use it for criteria, or use some other aggregate function for it. Since I
don't know what it is or what you want to do with it, I can't say!
Click to expand...
 
If I use the where criteria it says it can't display the field where I
entered Where in the total field.
Use the field twice, once with GROUP BY and once with WHERE.

--
Build a little, test a little.


klkropf said:
Band is a job band, so just a letter. I can't group by it because there are
different values for different dates. I want to display the band associated
with the max date row I'm returning. If I use the where criteria it says it
can't display the field where I entered Where in the total field.

John W. Vinson said:
Thanks for your response.

I used your logic, but I still must be doing something incorrectly. I added
the new field 'Band' and I get the error, You tried to execute a query that
does not include 'Band' as part of the aggregate function.


SELECT tblTransferData.ID, tblTransferSnapShot.Band
FROM tblTransferData INNER JOIN tblTransferSnapShot ON tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date])
GROUP BY tblTransferData.ID;
Click to expand...

What are you doing with the Band? What does it mean?

You'll need to either Group By it, or use the Where "totals" line option to
use it for criteria, or use some other aggregate function for it. Since I
don't know what it is or what you want to do with it, I can't say!
Click to expand...
Click to expand...
 
Thank you. I am trying to loose weight but the truth is, Takuya and I eat
alot of snacks. He lost his appetite when found out that I tak makan because
takde duit. Sedih and nangis and Rar sakit perut and berak and need quaker
oats and she told her Daddy to stop thinking of those and GrandPa`s will
cook and makan together. Actually wait for to mix together to kuatkan badan
loves.
Dramalah ni!
entered Where in the total field.
Use the field twice, once with GROUP BY and once with WHERE.
Monei angry lah for all lupa and make Monei lupa. Natasya is Monei.
--
Build a little, test a little.


klkropf said:
Band is a job band, so just a letter. I can't group by it because there
are
different values for different dates. I want to display the band
associated
with the max date row I'm returning. If I use the where criteria it says
it
can't display the field where I entered Where in the total field.
Click to expand...
hehehhe..
John W. Vinson said:
On Thu, 25 Mar 2010 09:43:03 -0700, klkropf

Thanks for your response.

I used your logic, but I still must be doing something incorrectly. I
added
the new field 'Band' and I get the error, You tried to execute a query
that
does not include 'Band' as part of the aggregate function.
I was so stress. So I made over and true, My hair grew. Dad too...:].

SELECT tblTransferData.ID, tblTransferSnapShot.Band
FROM tblTransferData INNER JOIN tblTransferSnapShot ON
tblTransferData.ID =
tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date])
GROUP BY tblTransferData.ID;
I trust you Takuya.Thankyou for loving Us just the way we are.Aren`t
we trully funny? It really works..
What are you doing with the Band? What does it mean?

You'll need to either Group By it, or use the Where "totals" line
option to
use it for criteria, or use some other aggregate function for it. Since
I
don't know what it is or what you want to do with it, I can't say!
--
31 cents for 1 table spoon full. ok what! so banyak.Children can afford
it. Hmm..suke..cute.SyimereiI.
John W. Vinson [MVP]
.Dr. pakai vest hmm.o`.Monei patutlah sesak nafas. Susahkan.
Rar makes Jaffar ketawa so much..he tak ketawa.
Click to expand...
Click to expand...
Click to expand...
 
Band is a job band, so just a letter. I can't group by it because there are
different values for different dates. I want to display the band associated
with the max date row I'm returning. If I use the where criteria it says it
can't display the field where I entered Where in the total field.
Click to expand...

If you're selecting the maximum value row with the subquery then you don't
need the Group By. Try

SELECT tblTransferData.ID, tblTransferSnapShot.Band, <other fields you want>
FROM tblTransferData INNER JOIN tblTransferSnapShot
ON tblTransferData.ID = tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date]);
 
Thank you. I am trying to loose weight but the truth is, Takuya and I eat
alot of snacks.
Click to expand...

Eighte, please find another place for your chat. This is a technical support
forum, not a chat room.
 
If I don't use Group By then it only returns one record....the record with
the max Snapshot Date of any employee ID. I need one record for each
employee ID with the max shaphot date and other data related to that snapshot
date.

I can do this with multiple queries.... I was just trying to simplify the
process.


John W. Vinson said:
Band is a job band, so just a letter. I can't group by it because there are
different values for different dates. I want to display the band associated
with the max date row I'm returning. If I use the where criteria it says it
can't display the field where I entered Where in the total field.
Click to expand...

If you're selecting the maximum value row with the subquery then you don't
need the Group By. Try

SELECT tblTransferData.ID, tblTransferSnapShot.Band, <other fields you want>
FROM tblTransferData INNER JOIN tblTransferSnapShot
ON tblTransferData.ID = tblTransferSnapShot.ID
WHERE tblTransferData.AsgEndDate = (SELECT
Max([tblTransferData].[AsgEndDate]) FROM
tblTransferData AS tblTransferData WHERE tblTransferData.AsgEndDate >
[Snapshot Date]);
Click to expand...
 
Back
Top