query problems

[vampke]

Hi guys,

I'm stuck at this query I'm writing. I'm hoping someone will be able to help me.
I have 2 linked tables: subjects and visits with a relationship on subject_id:

table subjects:
Subject_id, name, birthdate

table visits:
Visit_id, Subject_id, parameter1, parameter2

Now I need to query that will get a result set of all subjects with the parameter1 and parameter2 of the latest visit_id.

I could do it using a select all query for subjects and than querying the visits table for every subject, but I'm sure there is a way to do this in 1 query. Could anyone be so kind to offer me some free consulting? ;)

[Ramy Mahrous]

What about using INNER JOIN on Subjects.Subjects_ID = Visits.Subject_ID and where Visits_ID = (Select Max(Visits_ID) From Visits) haa? simple, isn't it?

[vampke]

Thanks for your help Ramy,

Your suggestion will only return 1 result. I need one result per subject.
So far I managed to get a resultset that returns every subjects every visit using:

SELECT  Subjects.*, Visits.*
FROM Subjects INNER JOIN Visits ON Subjects.Subjects_ID = Visits.Subjects_ID

I guess I'm halfway there, I only need to get only the heighest result of Visit_ID for every subject

I tried the group by statement, but got an error that I could not work out

[Ramy Mahrous]

For every subject ==> group by subject ID
The highest result ==> having Visit_ID = Max (Visit_ID)

[vampke]

Thanks again Ramy,
I tried Group By already, but I always get an error on the other fields that I need in my resultset:

You tried to execute a query that does not include the specified expression 'someotherfield' as part of an aggregate function

Apparently I always get this when querying a field that I don't use in the group by statement
I don't understand this at all :(

[Ramy Mahrous]

Copy your query here which raises this error.

[vampke]

ok, here it is:

SELECT  Subjects.Subject_ID, Subjects.SubjectCode, Subjects.SubjectName, Visits.Date, Visits.Comment
FROM Subjects INNER JOIN Visits ON Subjects.Subject_ID = Visits.Subject_ID 
GROUP BY Subjects.Subject_ID
HAVING Visits.Visit_ID = Max (Visits.Visit_ID)

[Ramy Mahrous]

SELECT  Subjects.Subject_ID, Subjects.SubjectCode, Subjects.SubjectName, Visits.Date, Visits.Comment
FROM Subjects INNER JOIN Visits ON Subjects.Subject_ID = Visits.Subject_ID 
Where Subjects.Subject_ID = (select distinct Subjects.Subject_ID from Subjects)
HAVING Visits.Visit_ID = Max (Visits.Visit_ID)

Or

SELECT  Subjects.Subject_ID, Subjects.SubjectCode, Subjects.SubjectName, Visits.Date, Visits.Comment
FROM Subjects INNER JOIN Visits ON Subjects.Subject_ID = Visits.Subject_ID 
Where Subjects.Subject_ID in (select distinct Subjects.Subject_ID from Subjects)
HAVING Visits.Visit_ID = Max (Visits.Visit_ID)

[vampke]

thanks again ramy, unfortunately i get the same %£#@# error!
this is very frustrating :/
Now i get the error on Subject_ID
I'm running this query directly in ms access.

[Ramy Mahrous]

Tell me the error please, or send me the access file, to try.
<EMAIL SNIPPED>

[peter_budo]

Tell me the error please, or send me the access file, to try.
<EMAIL SNIPPED>

Why not to use attachment option of this forum? Simply zip it up and ass to next post. Do not forget to use advanced reply mod or you will not see Manage Attachments option

[vampke]

Hello again,

I'm sorry for my late reply, but I have been very busy at work :(

Here is some sql to create and populate the 2 tables:

CREATE table subjects (
subject_id  counter,
subjectname text
)
CREATE table visits (
visit_id counter,
subject_id  number,
parameter1  text,
parameter2  text
)

INSERT INTO subjects (subjectname) VALUES ("r");
INSERT INTO subjects (subjectname) VALUES ("p");
INSERT INTO subjects (subjectname) VALUES ("q");
INSERT INTO visits ( subject_id, parameter1, parameter2) VALUES ("1", "123", "121");
INSERT INTO visits ( subject_id, parameter1, parameter2) VALUES ("1", "223", "221");
INSERT INTO visits ( subject_id, parameter1, parameter2) VALUES ("2", "532", "128");
INSERT INTO visits ( subject_id, parameter1, parameter2) VALUES ("3", "372", "936");
INSERT INTO visits ( subject_id, parameter1, parameter2) VALUES ("1", "231", "721");