If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access Table Help
I am working on a database that displays employee information. I have tables
that include: Employees Absence / Tardy Checklists Special Events For each of these tables the primary key is their ID# and each table has all the ID#'s in. I have two other tables that a Inservices 1 Inservices 2 They also include the primary key ID# These two tables only include specific people assigned to certain departments. I have the relationships a 1-1 for all the tables back to the Employees. My problem is that when I combine data when making a query from one of the all tables and one of the some table my data does not show up. So I have to have something wrong. I can't see to figure it out. So any help that anyone can give me would be appreciated. Thank! |
#2
|
|||
|
|||
Access Table Help
On Sun, 16 Nov 2008 12:32:02 -0800, mndpy
wrote: I am working on a database that displays employee information. I have tables that include: Employees Absence / Tardy Checklists Special Events For each of these tables the primary key is their ID# and each table has all the ID#'s in. What's the datatype of ID#? I have two other tables that a Inservices 1 Inservices 2 What is in these tables? It's almost surely an error to have two (apparently) identical tables. They also include the primary key ID# These two tables only include specific people assigned to certain departments. I have the relationships a 1-1 for all the tables back to the Employees. BEEEEP!!! That's your problem. That would mean the each employee can have one, and only one, Absence/Tardy, ever. Surely you don't fire an employee for being a minute late! One to one relationships are VERY RARE. If you're not "Subclassing" or using "Table driven field level security" or a couple of other arcane techniques, you almost surely want one to many relationships, not one to one. My problem is that when I combine data when making a query from one of the all tables and one of the some table my data does not show up. Not sure what you mean by "the all tables" or "the some tables", but your one to one relationship is surely the cause of the problem. So I have to have something wrong. I can't see to figure it out. So any help that anyone can give me would be appreciated. I think you need to rethink how your relationships work! If each Employee can have zero, one, or many Absences, then you need an Absences_Tardy (don't use / in fieldnames) table with its own primary key (perhaps an autonumber), and a *foreign key* field, an employee ID linked one-to-many to the Employees table. You would use a Form based on Employees with a Subform based on Absences_Tardy to enter data. If each employee may be involved in zero, one, or many Special Events, and each Special Event may be attended by zero, one, or many Employees, then you have a Many to Many relationship (not a one to one relationship). You will need a new table. A structure like this may work: Employees EmployeeID (again, don't use # in fieldnames, it's a date delimiter) LastName FirstName other biographical data SpecialEvents (it's best not to use blanks either... sorry... g) EventID Primary Key EventName EventDate other info about the event EventAttendance EmployeeID link to Employees, who attended EventID link to SpecialEvents, what did they attend any info about THIS employee's attendance at THIS event -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Access Table Help
My tables consist of the following information:
Employees Table - 34 Different items related to their personal information and hire information Absence / Tardy - includes 15 absence reports and 15 tardy reports that can be filled out for each employee Special Events - Includes 24 different yes/no categories to know of the employee participated in it. The Checklist Table has 75 items yes/no on if someone is trained in that particular area. The inservices 1 and 2 are table that only include certain people from the employees table. They do not include everyone and do not include the same people. So any of help on how to relate them would be great. Thanks! |
#4
|
|||
|
|||
Access Table Help
On Mon, 17 Nov 2008 09:33:00 -0800, mndpy
wrote: My tables consist of the following information: Employees Table - 34 Different items related to their personal information and hire information Absence / Tardy - includes 15 absence reports and 15 tardy reports that can be filled out for each employee Special Events - Includes 24 different yes/no categories to know of the employee participated in it. The Checklist Table has 75 items yes/no on if someone is trained in that particular area. The inservices 1 and 2 are table that only include certain people from the employees table. They do not include everyone and do not include the same people. So any of help on how to relate them would be great. Thanks! You've got a start, but you're not quite there! I'm not quite clear what's in some of these tables. What are the actual fieldnames, datatypes and contents of the fields in Absence/Tardy (and please DO change the name, the slash *will* cause problems). Your Special Events table is a decent spreadsheet design but it's not good for a relational table. "Fields are expensive, records are cheap" - if you have one field per event, then whenever you have a 25th event, you will need to change the structure of your table, of your form, of all your queries, of all your reports... OUCH! The Checklist table is even worse (though the same problem). If you have a many to many relationship, such as each Employee being trained in many Areas, and each Area having many Employees trained, you need three tables: Employees EmployeeID LastName FirstName etc., biographical info you already have TrainingAreas AreaID Primary Key, autonumber probably Area Text description of the training area other info about this training area, e.g. prerequisites EmployeeTraining EmployeeID link to Employees, who was trained AreaID for which area were they trained perhaps trainingdate, satisfactory/unsatisfactory completion, comments You'ld use the same technique with Special Events as I posted previously. If you're copying the data in Employees into the Inservice tables... DON'T. Data should be stored once, and once only; storing it redundantly in a second table is asking for a heap of trouble! Perhaps you could explain a bit more what an "inservice" means. If you wish, take a look at some of the resources below. Crystal's tutorial is a good place to start: Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP): http://allenbrowne.com/casu-22.html MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|