Compare total Score based on Completion Date

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

Guest

I have a table of data where there are multiple records for the same ID. I
need to find the latest date and score and compare it to the earliest date
and score. Any ideas? I'm pretty new to Access and not sure if I should use
a SQL statement query or something else.
 
Hi Fran,

This is the query will list the Earliest Date (MIN_DATE) and score
(MIN_SCORE) and Lastest Date (MAX_DATE) and score (MAX_SCORE).

Table Name: Table1
Field: Item (PK), ADate, Score

SELECT DISTINCT [Table1].Item, DMin("ADate","[Table1]","Item='" & [Item] &
"'") AS MIN_DATE,DLookup("Score","[Table1]","Item='" & [Item] & "' AND ADATE
= #" & [MIN_DATE] & "#") AS MIN_SCORE, DMax("ADate","[Table1]","Item='" &
[Item] & "'") AS MAX_DATE, DLookup("Score","[Table1]","Item='" & [Item] & "'
AND ADATE = #" & [MAX_DATE] & "#") AS MAX_SCORE
FROM [Table1];

Hope this helps.
 
I have a table of data where there are multiple records for the same ID. I
need to find the latest date and score and compare it to the earliest date
and score. Any ideas? I'm pretty new to Access and not sure if I should use
a SQL statement query or something else.
Click to expand...

A SQL query will work here. It's a bit tricky, you'll need two nested
subqueries:

SELECT ID,
(SELECT [Score] FROM table AS EARLY WHERE EARLY.ID = table.ID AND
EARLY.[date] = (SELECT Min([date]) FROM table AS ED WHERE ED.ID =
table.ID)) AS EarliestScore,
(SELECT [Score] FROM table AS LATE WHERE LATE.ID = table.ID AND
Late.[date] = (SELECT Mas([date]) FROM table AS LD WHERE LD.ID =
table.ID)) AS LatestScore;


John W. Vinson[MVP]
 
Thanks for your help. We have resolved the problem.

John Vinson said:
I have a table of data where there are multiple records for the same ID. I
need to find the latest date and score and compare it to the earliest date
and score. Any ideas? I'm pretty new to Access and not sure if I should use
a SQL statement query or something else.
Click to expand...

A SQL query will work here. It's a bit tricky, you'll need two nested
subqueries:

SELECT ID,
(SELECT [Score] FROM table AS EARLY WHERE EARLY.ID = table.ID AND
EARLY.[date] = (SELECT Min([date]) FROM table AS ED WHERE ED.ID =
table.ID)) AS EarliestScore,
(SELECT [Score] FROM table AS LATE WHERE LATE.ID = table.ID AND
Late.[date] = (SELECT Mas([date]) FROM table AS LD WHERE LD.ID =
table.ID)) AS LatestScore;


John W. Vinson[MVP]
Click to expand...
 
Thanks for your help. We have resolved the problem.

JL said:
Hi Fran,

This is the query will list the Earliest Date (MIN_DATE) and score
(MIN_SCORE) and Lastest Date (MAX_DATE) and score (MAX_SCORE).

Table Name: Table1
Field: Item (PK), ADate, Score

SELECT DISTINCT [Table1].Item, DMin("ADate","[Table1]","Item='" & [Item] &
"'") AS MIN_DATE,DLookup("Score","[Table1]","Item='" & [Item] & "' AND ADATE
= #" & [MIN_DATE] & "#") AS MIN_SCORE, DMax("ADate","[Table1]","Item='" &
[Item] & "'") AS MAX_DATE, DLookup("Score","[Table1]","Item='" & [Item] & "'
AND ADATE = #" & [MAX_DATE] & "#") AS MAX_SCORE
FROM [Table1];

Hope this helps.


Fran said:
I have a table of data where there are multiple records for the same ID. I
need to find the latest date and score and compare it to the earliest date
and score. Any ideas? I'm pretty new to Access and not sure if I should use
a SQL statement query or something else.
Click to expand...
Click to expand...
 
Back
Top