Attendance Log - Missing person(s) Query

[hinoks]

I have a daily attendance tracking of 100 staff, and would like to be able to find out who was missing in particular date by running a query.

I have two tables



1) List of staff members (Name and Dept)
2) Daily log of who attended (Date, Name, Dept etc.)

I appreciate your advice very much!

[ssanfu]

I have a daily attendance tracking of 100 staff, and would like to be able to find out who was missing in particular date by running a query.

I have two tables

1) List of staff members (Name and Dept)
2) Daily log of who attended (Date, Name, Dept etc.)

I appreciate your advice very much!

Are those two tables related? I hope you are not using "name" as a field name.
The way to find who is in one table but not in another table is to use an "unmatched query". In the database window, select Queries, then click on "New". In the dialog box, the bottom option is the "Find unmatched query wizard".

[hinoks]

Are those two tables related? I hope you are not using "name" as a field name.
The way to find who is in one table but not in another table is to use an "unmatched query". In the database window, select Queries, then click on "New". In the dialog box, the bottom option is the "Find unmatched query wizard".

ssanfu: Thanks for the quick response!

These two tables are related. Now, yes I do use "name" as the field name... And I don't even know what may be causing to do on my database... I really need some guidance on that one...

The issue I encountered by using ummatched query is that the database cumulates the same name day by day. So when I run "Unmatched Query", it matches to the data previuosly entered. Also, I use combo box for selecting names by department so unmatching names would not likely to be entered.

My comparison requires to be

100 default staff's name compared to the actual attendances' name of the day.

Thanks for your help very much!!
Hinoks

[crismroman]

You need to use 2 query:
1. Staff present in a date selected
Q1: select name from daily_log where date=[x]
where x it is a parameter= date that you will enter
2. Staff that is missing for date=x
q2: select L.* from List_of_staff_members as L left join Q1 on L.name=Q1.name
where Q1.name is Null

[hinoks]

You need to use 2 query:

2. Staff that is missing for date=x
q2: select L.* from List_of_staff_members as L left join Q1 on L.name=Q1.name
where Q1.name is Null

Crismroman:

Thanks for your help! q1 I could handle!! But q2, I have no idea how to even read / understand it.

I'd really appreciate if you could dissect further for me.

Thank you for your help very much!!!

[ssanfu]

ssanfu: Thanks for the quick response!

These two tables are related. Now, yes I do use "name" as the field name... And I don't even know what may be causing to do on my database... I really need some guidance on that one...

The issue I encountered by using ummatched query is that the database cumulates the same name day by day. So when I run "Unmatched Query", it matches to the data previuosly entered. Also, I use combo box for selecting names by department so unmatching names would not likely to be entered.

My comparison requires to be

100 default staff's name compared to the actual attendances' name of the day.

Thanks for your help very much!!
Hinoks

OK, about using "Name"... "Name" is a property of objects in Access. It is a reserved word, meaning that you shouldn't use reserved words as object names (as well as spaces or special characters)

Here is a link to a list of reserved words from Allen Browne:
http://allenbrowne.com/AppIssueBadWord.html

And a link about spaces or special characters"
http://access.mvps.org/access/tencommandments.htm

Using "Name" (to me) implies that the full name is stored in the field, such as John Smith. So how do/would you sort by last name? Or mayve the name is entered as "Smith, John". How do you get just the first name or just the last name? Seems simple, until you get a name like "Jose M. de la Cruz".
Should I mention that it is not a normalized table structure?


OK, looking at the second query:
select L.* from List_of_staff_members as L left join Q1 on L.name=Q1.name
where Q1.name is Null

List_of_staff_members as L : "List_of_staff_members" is the table name, L is the alias.

"L.name" is the field name "Name" (see what I mean?) "L" is the shortcut to refer to the table "List_of_staff_members". Less typing....

"L.*" means return all fields from the table "L" (List_of_staff_members)

[hinoks]

Thank you very much Steve and Crismroman for all your kind advices!! I was able to solve the unknown!!

Thank you for your time and help!!!

hinoks