M
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
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