SQL MAX() on two tables

[Mr Kruger]

Hello!

I try to get data out of my access DB with only sql. I undersstand how
I do it in access with two queries but i am trying to access it from
an ASP.NET site!
I have two tables like this:

Tbl1
IDName
Firstname

Tbl2
ID
IDName (one to many rel. from Tbl1)
In (Yes/No field)
Out (Yes/No field)
Vacation (Yes/No field)
DateTime

Each person is either in/out or on vacation and Tbl2 records the time
and date when it happened. Now I want to see who is in/out/vacation
for today! I want to see the value of the in/out/vacation field for
Max(Date) for each person.

I have got that far so I can see the latest date for each person with:

SELECT Tbl1.IDName, Tbl1.FirstName, Max(Tbl2.DateTime) AS
['MaxOfInOutTime']
FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.IDName = Tbl2.IDName
GROUP BY Tbl1.IDname, Tbl1.FirstName;

but as soon I add one of the In/Out/Vacation field I get multiple
values.

I suppose this is a common situation in a DB is there a better way to
set up the tables to record this?

Thanks in advance!
Jonas

 

[Brian]

Hello!

I try to get data out of my access DB with only sql. I undersstand how
I do it in access with two queries but i am trying to access it from
an ASP.NET site!
I have two tables like this:

Tbl1
IDName
Firstname

Tbl2
ID
IDName (one to many rel. from Tbl1)
In (Yes/No field)
Out (Yes/No field)
Vacation (Yes/No field)
DateTime

Each person is either in/out or on vacation and Tbl2 records the time
and date when it happened. Now I want to see who is in/out/vacation
for today! I want to see the value of the in/out/vacation field for
Max(Date) for each person.

I have got that far so I can see the latest date for each person with:

SELECT Tbl1.IDName, Tbl1.FirstName, Max(Tbl2.DateTime) AS
['MaxOfInOutTime']
FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.IDName = Tbl2.IDName
GROUP BY Tbl1.IDname, Tbl1.FirstName;

but as soon I add one of the In/Out/Vacation field I get multiple
values.

I suppose this is a common situation in a DB is there a better way to
set up the tables to record this?

Thanks in advance!
Jonas

As I understand it, only one of your three Yes/No fields can be "Yes" at any
one time, so you would be better off with a single status field of some
kind. Personally I would use a text field of length 1, which can take the
values "I", "O" or "V". So, Tbl2 would look like this:

Tbl2
ID
IDName (one to many rel. from Tbl1)
Status
DateTime

Now you can use a correlated subquery like this:

SELECT Tbl1.IDName, Tbl1.FirstName, Tbl2.DateTime, Tbl2.Status
FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.IDName = Tbl2.IDName
WHERE Tbl2.DateTime = (SELECT MAX(Tbl2.DateTime)
FROM Tbl2 AS sub_tbl2 WHERE sub_tbl2.IDName = Tbl2.IDName)

 

[jonaskruger]

Thank's Brian for your help but it still not working!
I change my table as suggested and tried whit the suggested SQL but i
get the same answer. Eg all rows in table 2 shows. Is it not suppose to
be some "Group by" somewhere and where in that case? As you understand
I am not to bright whit SQL!
Thanks
Jonas

 

[JK]

Ok Thank's Brian you got me on the right track. Not that I completly
understand what that did or the different (I changed the
sub_tbl2.IDName to tbl1.IDName) BUT IT WORKS! Here is what I did:

SELECT Tbl1.IDName, Tbl1.FirstName, Tbl2.DateTime, Tbl2.Status
FROM Tbl1 INNER JOIN Tbl2 ON Tbl1.IDName = Tbl2.IDName
WHERE Tbl2.DateTime = (SELECT MAX(Tbl2.DateTime)
FROM Tbl2 WHERE tbl1.IDName = Tbl2.IDName)

Thank's once again!
Jonas