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

so create the query on Tbl2 (you REALLY should use names that mean
something here!) that gets the latest whatever for each person. Then
base your outer query on that. (join this query, call it qryWhereAreYou
to Tbl1 (Employee), and then you should be golden.