Auto fill a field or entry in a table

[hardin4019]

Hi Guys,
I'm really new to Access, and have little experience with VB, but I've managed to make myself a DB with some forms and need some help in layman's terms. I've seen this topic about a million times but nothing seems to be working for me. I have Access with office 97 so that could be a portion of my problem.

Here's what I have and what I want to do.
Employees Table:
ID
EmployeeName
Employer
Shift
SupervisorName

Attendance Table:
ID
EmployeeName (Combo Box)
SupervisorName (Text Box)
AbscenceType (Combo Box)
PapersTurnedIn (Check Box)
Excuesed (Check Box)
Date (calender control)

So I have an Attendance form that when you open it pulls up a list of EmployeeNames from the Employees Table, with the exception of SupervisorName (its a 1 to many thing, 1 employee has 1 supervisor, but 1 supervisor has many employees), everything else needs to be manually filled in. What I want is to either have the supervisor name be filled into the text box and selected so I don't have to click on it to select it, or to not even need the text box or any kind of input and when I select the EmployeeName, the table auto fills in the SupervisorName, completely unseen by the end user. The end goal here is to have a report that lists employees by supervisor and then their attendance. I'd like to think I could get it to total up the number of unexcused absences based on the 365 days prior to the report being run, but I'll make it there everntually.

 

[Big Pat]

You don't need to have the supervisor logged at all in the attendance table. You've got that in your Employees table so here's where the relational part of databases comes in. Create a query and add BOTH tables to it.

Add Employee Name and Supervisor Name from Employees
Add Date from Attendances
Link Employees [ID] field to Attendances [EmployeeName] field. This might happen automatically in your query if you have set up relationships. I can't be certain of this step otherwise.

Run this query. You should get a list of employees, supervisors and dates. If your report needs other fields from either table, add them now. If required use the criteria rows at the bottom of the query "grid", to make it filter your results, e.g. to exclude the Excused ones. (0 = unticked, -1 = ticked)

When you're happy the query contains the DATA you want, then create a new report, based on that query - NOT on the table. When going through the report wizard, there should be an option for grouping. Tell it you want to group by Supervisor Name.

That should give you the basics. The rest is then formatting the report, with headings, fonts, width of text boxes etc. I can't remember much about Access 97 now but moving and resizing the various items (controls) on the report should be easy enough.

Read up a little on creating queries if you can. I hope this helps.

 

[hardin4019]

Thanks Pat!
The actual creation of the query took a minute or so, and the layout of the report took more time than that. It looks great. I'll start working on totaling up excused and un-excused now and see if I can't get this thing doing the what I really hoped it would do.

I got a little confused with the linking of the two tables together and which fields to link, but I went back to what I knew, the employee names in both fields were the same, so I linked them, then things started looking just fine.

 

[Big Pat]

the employee names in both fields were the same, so I linked them, then things started looking just fine.

OK, you can *probably* get away with that, for now at least, but what happens if you ever have two employees with the same name? John Smith (A) might get stuck with John Smith (B)'s poor attendance record.

Every employee has an ID and that is (or should be!) the primary key in that table.

Your attendance table, as well as its own primary key, should have a field called something like EmployeeNo (number datatype, not a primary key). I would always put this field right after the Primary Key in the Attendance table, but that may be only MY convention I'm not sure now.

This field is known as the foreign key for that table, i.e. it relates to a field in another table. I've even seen code by programmers who would name their fields PK_EmployeeID and fk_EmployeeNo and I guess that's a good idea to really clarify what's going on.

Anyway, you don't store the Employee name in Attendances, you store just the EmployeeNo. Then you use the Relationships window to link the two tables, by dragging Employees-ID over to Attendances-EmployeeNo. It should automatically create a one-to-many relationship.

When you build any queries using those tables the fields will be correctly linked for you.

Glad you got it working anyway. You'll learn a lot around here. I knew a lot less than you appear to when I started coming here!

 

[hardin4019]

Hi Pat,
Good point. I actually understand it now. So I tried it, I removed the EmployeeName Field From Attendance and did a little shifting around so I have this:

Employees Table:
EmployeeID <== PrimaryKey
EmployeeName
Employer
Shift
SupervisorName
Department

Attendance Table:
PrimaryID
ForeignEmployeeID
EmployeeName (leaving it till I get this to work)
Date
AbscenceType
Excused
PapersTurnedIn

So, I linked the EmployeeID to ForeignEmployeeID. Now the part I am actually having trouble with is in my form and getting ForeignEmployeeID = EmployeeID. I click on the Employees combo box and want a list of names to show up, then have it force ForeignEmployeeID to the right ID #, but when I look at the table, its filling it in with a 0, not the employee ID number. And, when I remove EmployeeName as the control source of my drop down and remove the EmployeeName field from Attendance, my combo box goes nuts and won't let me pick a name. So, where/how do I make the relationship that says that when this name is picked from the list, force this EmployeeID to go into this space in the Attendance table. I tried making a query... Thought that was the right direction to go, but still not sure what I did wrong. Tried making the EmployeeID have a lookup reference to the Employees Table and get the EmployeeID for the correct name, but still bringing up 0's.

 

[hardin4019]

Fixed it! Used my combo box, set control source to the ForeignEmployeeID, set the other options to lookup the EmployeeName, then EmployeeID from a query that had them sorted in Alphabetical Order, bound the combo box to show only the EmployeeName, but at the same time it chooses the correct ID and drops it in the table. That is pretty cool.

Future things I feel like are important to do:
1. Attendance double entry error checking, can't enter the same person on the same date for the same reason IE abscent and on vacation, vacation twice, etc
2. Being able to run a report that has limiting factors you can choose from one or multiple combo boxes rather than 18 different reports to choose from
3. Attendance date control that allows you to say choose a beginning and end date for something like a scheduled vacation and have it enter all the dates into the table and fill in the other valid info, or just a way to have the date field hold multiple dates in the table for a single line
4. Summing different points values for different types of abscences, IE a full absence is 1 point, vs a tardy or leave early are each a .5 of a point, and all of this is only if it is un-excused..... (DONE)


Any ideas Big Pat? You're a life saver!