Comparing totals - second attempt!

[C Tate]

This is my second attempt at a post. I don't think I worded it well the
first time around, so here goes .



I have a table called tblprogress. It looks something like this:



Identifier Date Healthscore Housingscore
MentalHealthscore Totalscore

01/06 01/01/06 5 3
4 12

02/06 01/01/06 4 4
5 13

03/06 02/01/06 3 2
5 10

01/06 01/02/06 3 3
3 9

01/06 01/03/06 3 2
2 7

02/06 01/02/06 1 1
1 3

03/06 01/02/06 4 5
5 14



I want do design a query which will enable me to look at a client's first
score and their latest score, then tell me whether the latest score is
higher, lower or the same as the first score. Is this possible? I have
managed to get as far as designing a min query and a max query based on the
date of the assessment but I'm lost after that. I'm also a bit of a novice
so I can't understand complicated SQL statements.



At some point in the future I may wish to build another feature into my
queries which enables me to compare the latest score with the last but one
score.



If anybody can advise me (in full idiot style language!) how to proceed I'd
be very grateful.

 

[Guest]

Try these queries.
QUERY C_Tate_1 ---
SELECT C_Tate.Identifier, Min(C_Tate.Date) AS MinOfDate, Max(C_Tate.Date) AS
MaxOfDate
FROM C_Tate
GROUP BY C_Tate.Identifier;

QUERY C_Tate_2 ---
SELECT C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore
FROM C_Tate, C_Tate_1
WHERE (((C_Tate.Date)=[MinOfDate] Or (C_Tate.Date)=[MaxOfDate]) AND
((C_Tate.Identifier)=[C_Tate_1].[Identifier]))
GROUP BY C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore;

QUERY C_Tate_3 ---
SELECT C_Tate_2.Identifier, C_Tate_2.Date, C_Tate_2_1.Date,
C_Tate_2.Totalscore, C_Tate_2_1.Totalscore,
IIf([C_Tate_2].[Totalscore]<[C_Tate_2_1].[Totalscore],"Higher",IIf([C_Tate_2].[Totalscore]=[C_Tate_2_1].[Totalscore],"Same","Lower")) AS Results
FROM C_Tate_2 INNER JOIN C_Tate_2 AS C_Tate_2_1 ON C_Tate_2.Identifier =
C_Tate_2_1.Identifier
WHERE (((C_Tate_2_1.Date)>[C_Tate_2].[Date]));

 

[C Tate]

So quick! As I said in my post, sql statements are a mystery to me. I am
assuming c_tate is the name of my original table? And c_tate_1 to c_tate_3
the name of the 3 queries? If so, perhaps I can just past this code into the
sql view?
I'd really appreciate it if you could just say a little about the logic of
these steps. Many thanks for your assistance.

Try these queries.
QUERY C_Tate_1 ---
SELECT C_Tate.Identifier, Min(C_Tate.Date) AS MinOfDate, Max(C_Tate.Date)
AS
MaxOfDate
FROM C_Tate
GROUP BY C_Tate.Identifier;

QUERY C_Tate_2 ---
SELECT C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore
FROM C_Tate, C_Tate_1
WHERE (((C_Tate.Date)=[MinOfDate] Or (C_Tate.Date)=[MaxOfDate]) AND
((C_Tate.Identifier)=[C_Tate_1].[Identifier]))
GROUP BY C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore;

QUERY C_Tate_3 ---
SELECT C_Tate_2.Identifier, C_Tate_2.Date, C_Tate_2_1.Date,
C_Tate_2.Totalscore, C_Tate_2_1.Totalscore,
IIf([C_Tate_2].[Totalscore]<[C_Tate_2_1].[Totalscore],"Higher",IIf([C_Tate_2].[Totalscore]=[C_Tate_2_1].[Totalscore],"Same","Lower"))
AS Results
FROM C_Tate_2 INNER JOIN C_Tate_2 AS C_Tate_2_1 ON C_Tate_2.Identifier =
C_Tate_2_1.Identifier
WHERE (((C_Tate_2_1.Date)>[C_Tate_2].[Date]));

This is my second attempt at a post. I don't think I worded it well the
first time around, so here goes .



I have a table called tblprogress. It looks something like this:



Identifier Date Healthscore Housingscore
MentalHealthscore Totalscore

01/06 01/01/06 5 3
4 12

02/06 01/01/06 4 4
5 13

03/06 02/01/06 3 2
5 10

01/06 01/02/06 3 3
3 9

01/06 01/03/06 3 2
2 7

02/06 01/02/06 1 1
1 3

03/06 01/02/06 4 5
5 14



I want do design a query which will enable me to look at a client's first
score and their latest score, then tell me whether the latest score is
higher, lower or the same as the first score. Is this possible? I have
managed to get as far as designing a min query and a max query based on
the
date of the assessment but I'm lost after that. I'm also a bit of a
novice
so I can't understand complicated SQL statements.



At some point in the future I may wish to build another feature into my
queries which enables me to compare the latest score with the last but
one
score.



If anybody can advise me (in full idiot style language!) how to proceed
I'd
be very grateful.

 

[Guest]

assuming c_tate is the name of my original table?
CorrectCorrect

The first query pull the min and max dates of the Identifier.
The second query pulls the score that is for the min and max dates.
The third query use the second query twice, once for the min info and the
second (Access add a suffix of -1) for the max info. It has criteria the the
date for the second instance must be higher than the first. Then it has a
nested IIF statement to display Higher, Lower, or Same score.

So quick! As I said in my post, sql statements are a mystery to me. I am
assuming c_tate is the name of my original table? And c_tate_1 to c_tate_3
the name of the 3 queries? If so, perhaps I can just past this code into the
sql view?
I'd really appreciate it if you could just say a little about the logic of
these steps. Many thanks for your assistance.

Try these queries.
QUERY C_Tate_1 ---
SELECT C_Tate.Identifier, Min(C_Tate.Date) AS MinOfDate, Max(C_Tate.Date)
AS
MaxOfDate
FROM C_Tate
GROUP BY C_Tate.Identifier;

QUERY C_Tate_2 ---
SELECT C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore
FROM C_Tate, C_Tate_1
WHERE (((C_Tate.Date)=[MinOfDate] Or (C_Tate.Date)=[MaxOfDate]) AND
((C_Tate.Identifier)=[C_Tate_1].[Identifier]))
GROUP BY C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore;

QUERY C_Tate_3 ---
SELECT C_Tate_2.Identifier, C_Tate_2.Date, C_Tate_2_1.Date,
C_Tate_2.Totalscore, C_Tate_2_1.Totalscore,
IIf([C_Tate_2].[Totalscore]<[C_Tate_2_1].[Totalscore],"Higher",IIf([C_Tate_2].[Totalscore]=[C_Tate_2_1].[Totalscore],"Same","Lower"))
AS Results
FROM C_Tate_2 INNER JOIN C_Tate_2 AS C_Tate_2_1 ON C_Tate_2.Identifier =
C_Tate_2_1.Identifier
WHERE (((C_Tate_2_1.Date)>[C_Tate_2].[Date]));

This is my second attempt at a post. I don't think I worded it well the
first time around, so here goes .



I have a table called tblprogress. It looks something like this:



Identifier Date Healthscore Housingscore
MentalHealthscore Totalscore

01/06 01/01/06 5 3
4 12

02/06 01/01/06 4 4
5 13

03/06 02/01/06 3 2
5 10

01/06 01/02/06 3 3
3 9

01/06 01/03/06 3 2
2 7

02/06 01/02/06 1 1
1 3

03/06 01/02/06 4 5
5 14



I want do design a query which will enable me to look at a client's first
score and their latest score, then tell me whether the latest score is
higher, lower or the same as the first score. Is this possible? I have
managed to get as far as designing a min query and a max query based on
the
date of the assessment but I'm lost after that. I'm also a bit of a
novice
so I can't understand complicated SQL statements.



At some point in the future I may wish to build another feature into my
queries which enables me to compare the latest score with the last but
one
score.



If anybody can advise me (in full idiot style language!) how to proceed
I'd
be very grateful.

 

[C Tate]

Brilliant. I got the queries to work and am really delighted with them. Wish
I could whip up some code in 5 minutes like that!

Correct

The first query pull the min and max dates of the Identifier.
The second query pulls the score that is for the min and max dates.
The third query use the second query twice, once for the min info and the
second (Access add a suffix of -1) for the max info. It has criteria the
the
date for the second instance must be higher than the first. Then it has a
nested IIF statement to display Higher, Lower, or Same score.

So quick! As I said in my post, sql statements are a mystery to me. I am
assuming c_tate is the name of my original table? And c_tate_1 to
c_tate_3
the name of the 3 queries? If so, perhaps I can just past this code into
the
sql view?
I'd really appreciate it if you could just say a little about the logic
of
these steps. Many thanks for your assistance.

Try these queries.
QUERY C_Tate_1 ---
SELECT C_Tate.Identifier, Min(C_Tate.Date) AS MinOfDate,
Max(C_Tate.Date)
AS
MaxOfDate
FROM C_Tate
GROUP BY C_Tate.Identifier;

QUERY C_Tate_2 ---
SELECT C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore
FROM C_Tate, C_Tate_1
WHERE (((C_Tate.Date)=[MinOfDate] Or (C_Tate.Date)=[MaxOfDate]) AND
((C_Tate.Identifier)=[C_Tate_1].[Identifier]))
GROUP BY C_Tate.Identifier, C_Tate.Date, C_Tate.Totalscore;

QUERY C_Tate_3 ---
SELECT C_Tate_2.Identifier, C_Tate_2.Date, C_Tate_2_1.Date,
C_Tate_2.Totalscore, C_Tate_2_1.Totalscore,
IIf([C_Tate_2].[Totalscore]<[C_Tate_2_1].[Totalscore],"Higher",IIf([C_Tate_2].[Totalscore]=[C_Tate_2_1].[Totalscore],"Same","Lower"))
AS Results
FROM C_Tate_2 INNER JOIN C_Tate_2 AS C_Tate_2_1 ON C_Tate_2.Identifier
=
C_Tate_2_1.Identifier
WHERE (((C_Tate_2_1.Date)>[C_Tate_2].[Date]));


:

This is my second attempt at a post. I don't think I worded it well
the
first time around, so here goes .



I have a table called tblprogress. It looks something like this:



Identifier Date Healthscore Housingscore
MentalHealthscore Totalscore

01/06 01/01/06 5 3
4 12

02/06 01/01/06 4 4
5 13

03/06 02/01/06 3 2
5 10

01/06 01/02/06 3 3
3 9

01/06 01/03/06 3 2
2 7

02/06 01/02/06 1 1
1 3

03/06 01/02/06 4 5
5 14



I want do design a query which will enable me to look at a client's
first
score and their latest score, then tell me whether the latest score is
higher, lower or the same as the first score. Is this possible? I have
managed to get as far as designing a min query and a max query based
on
the
date of the assessment but I'm lost after that. I'm also a bit of a
novice
so I can't understand complicated SQL statements.



At some point in the future I may wish to build another feature into
my
queries which enables me to compare the latest score with the last but
one
score.



If anybody can advise me (in full idiot style language!) how to
proceed
I'd
be very grateful.