Simple... I think....

[eussias]

My dilemma... I have three fields in a table...

1. ClientNumber
2. MMSEScore
3. WeekNumber (0,26,52)

What I'm hoping to do is write a query which will list all clients who have a MMSE score at Week 26, which is greater than the MMSE at Week 0. Can anyone help?

 

[fcail]

Hi,

try this :

SELECT a.Clientnumber
FROM Table_client a
WHERE a.weeknubmer=26
and a.mmsescore > ( select b.mmsescore from Table_client b where b.weeknumber=0 and a.clientnumber=b.clientnumber);


Regards

Fcail

 

[eussias]

Fcail...

That won't work as the 2 tables are ClientInfo & Cognitive. Cognitive contains the fields ClientNumber, WeekNumber and MMSEScore.
The ClientInfo table doesn't contain a WeekNumber field, only ClientNumber. It is a one to many relationship. Any help would be greatly appreciated.

 

[Sawatzky]

dear eussias,

make a new query,
change the table name in the select statement postet above. paste this into the sql-view of the new query.
if you want to add data from your client info table. change to the edit-view . add the client info table to the view .
and make a left join from the client info table to the cognitive table.

regards astrid

 

[eussias]

This is the code I have... saying there is a data mismatch. I have been over it numerous times, but cannot see what's wrong. Any help?? Thanks.

SELECT ClientInfo.[Client Number], ClientInfo.[Surname], ClientInfo.[Forename]
FROM ClientInfo LEFT JOIN Cognitive ON ClientInfo.[Client Number] = Cognitive.[Client Number]
WHERE Cognitive.[Week Number] = 26 AND Cognitive.[MMSE Score] >
(SELECT Cognitive.[MMSE Score] FROM Cognitive WHERE Cognitive.[Week Number] = 0
AND ClientInfo.[Client Number] = Cognitive.[Client Number]);

 

[JaneC]

Hi,

Just pasted your code and made up the tables and it worked fine. I assumed that week number was numeric, changing it to text gave a data type mismatch error so you may want to check this.

JC

 

[eussias]

Error Message : At most one record can be returned by this subquery.. anyone know why this is happening??

 

[Sawatzky]

dear eussias,

try whether this subquery return moer than 1 record if so
the error message wants to point you at this.

(SELECT Cognitive.[MMSE Score] FROM Cognitive WHERE Cognitive.[Week Number] = 0
AND ClientInfo.[Client Number] = Cognitive.[Client Number]);

when you evaluate > sql expects in the subquery only 1 value (record) returned.

could be that the second condition implicitely creates a crossjoin between the 2 tables clientinfo and cognitive

regards astrid

 

[eussias]

I have run this subquery :

SELECT Cognitive.[MMSE Score]
FROM ClientInfo LEFT JOIN Cognitive ON ClientInfo.[Client Number] = Cognitive.[Client Number]
WHERE Cognitive.[Week Number]=0 AND [ClientInfo].[Client Number]=[Cognitive].[Client Number];

separately and it returns more than one record. There must be a crossjoin being created between the 2 tables, but I don't know how to fix this. Can anyone help??

 

[JaneC]

Hi,

I think there is a problem with your data. There is probably 2 entries in Cognitive for the same CLientId and Week number (0)

I just created thsi situation on my db and it gave the same error. When the duplicate is removed then the query works fine.

Jane

 

[Sawatzky]

dear eussias,

select first(Cognitive.[MMSE Score])
FROM ClientInfo LEFT JOIN Cognitive ON ClientInfo.[Client Number] = Cognitive.[Client Number]
WHERE Cognitive.[Week Number]=0 AND [ClientInfo].[Client Number]=[Cognitive].[Client Number];

will give you the first value, but I am not sure if it is what you want.


Could you determine which of the records returned , is the one you want to get?

regards astrid

 

[eussias]

Thanks guys... turned out my colleague ran an append query on the Cognitive table causing there to be duplicate records. Got it working now... thanks for all your help... much appreciated.