Tables Setup Correctly

[BartK]

Hi everyone, I've been having this problem and I think it has something to do with my relationships between my tables. I will attach my DB so you can see what I mean. Open up the Tracking form to see what I mean.

On my main form it gives me errors in my text boxes, I have, atleast to the best of my knowledge, got the control source of each text box looking at the correct table, however I'm getting the #error and #name message appear in my boxes when I open the form. Ultimately I would like the DB to be able to store information about John Doe was issued a phone and he didn't return it. And in the event it comes back then I can delete him from that table of users not returning stuff.

I know this type of thing can be done with access but I can't get past the setting up of the tables. Please help.:banghead::banghead:

 

[jdraw]

Work through this tutorial to learn about tables, attributes and relationships.
Good luck.

 

[plog]

I'm not able to open the database file. Can you save it as .mdb or just post a screen shot of the relationships window?

 

[BartK]

Here is a picture of my relationship table setup. Hopefully you can make use of it.

I'm not able to visit that site, the company I work for has that one blocked. Any other sites you can think of that might help me out? Many thanks.

 

[plog]

Let's work small issue to big issue:

1. 'Date' is a bad name for a field because its a reserved word in access, which means it can cause issues when writing code and queries. I would rename that field prefixing it with what date it represents (i.e. HireDate, BirthDate, etc).

2. Field and tables shouldn't have non-alphanumeric characters in them. Again, this is because it makes code and queries just a little more difficult. Eliminate your field name spaces ([Last Name]-> [LastName], [Employee Number] -> [Employee Number]).

3. 1-1 relationships are a sign of a bad structure. All of your tables have a 1-1 relationship with Employee Table. Why? Does everyone have a phone? Everyone have a radio? Everyone have a spotter? Also, and this are different questions: Does everyone have at most 1 radio, phone and/or spotter?

4. I think you've linked your tables improperly. You have Employee.EmployeeID linked to Phone.PhoneID, yet you have an EmployeeID in the Phone table. That doesn't seem right. First it woudl seem that the EmployeeID fields should be linked, second PhoneID is the primary key in the Phone table.

5. Phone and Radio tables essentially have the same structure, which means they should be in the same table. When you have tables with the same fields, but different names, you should have all the data in a new table with the same structure plus one more field--[Type]. That new field would be used to distinguished what field it used to belong in.

6. You might even be able to roll Spotter data into that table I described in #5.

Those are the issues I see from what you've given me. There also might be an issue with 'Crew'. What is it for and what does it do?

 

[BartK]

Plog and Access,
Thank you for all the insight. As you can probably tell, I'm very new to multiple tables. I will definately see what I can do to make my DB more "friendly". As for the 1 to 1 relationship, I totally agree with you, however I can't seem to setup the relationship 1 to many. Also in regard to the different tables with the same structures, Radio, Phone, Spotter. I may be barking up the wrong tree here, don't those have to be separated out being that not everyone gets the same equipment every day and there are days when they don't return the "equipment" issued out? As for the Crew field, it is something in the future if we wanted to know how many people on Day Crew Lost Equipment we can do a query on the type of crew.

EX. Day 1 John Doe issued phone 35, spotter 18 and radio 12, nothing returned
Day 2 John doe Issued phone 78, Day 1 & 2 "equipment" returned
etc. etc.
Hopefully you can start to see what I am going for.

 

[plog]

If that's your goal, our advice about putting all equipment in one table stands. Plus, to do what you want you will also need a junction table (http://en.wikipedia.org/wiki/Junction_table).

You have multiple people and multiple pieces of equipment--a many to many relationship. To do that you need a table between your two tables (a junction table) to help manage those many relationships.

Here's a simplified layout of what you need:

Employees
EmployeeId, FirstName, LastName, ...

Equipment
EquipmentID, EquipmentType, ...

EquipmentAssignments
AssignmentID, EquipmentID, EmployeeID, CheckOutDate, CheckInDate

That last table will do what you want--it will tie and employee to a piece of equipment and hold when they took it out and when they returned it.

 

[BartK]

Plog,
Thank you for that, I had no Idea it would get that in depth, ofcourse this might be beginner stuff as well, as for me this is the first i've heard of a junction table. I've read the link you attached, I did see the name Cross-Tab in there. I do know that there is a query called that, however how difficult is it to setup a junction table. Or is that just a name for what the table does?

1. Are there any PK in the tables?
2. I assume that the relationship between all the tables would be EmployeeID, correct? Being that is the "common thread" between them. Sorry for all the NOOB questions. Thank you once a gain.

 

[plog]

1. Its good practice to put a primary key in every table, however, you don't really need one in a junction table.

2. No. If you look at my simple structure the table Equipment wouldn't have any connection to an employee. That connection would be made in the unction table.

The link I gave didn't have any mention of the term 'cross-tab'. That is a type of query, but doesn't apply to anything you've mentioned you want/need.

A junction table is just a term for the function a table performs in a many-to-many realtionship. You set it up just like a regular table--create a new table, define the fields and field types you want and create it.

 

[BartK]

Plog,
Thank you for all your help. I've done some readon and have setup the junction table. Now I just have to work out the control source problem I am having. Thank you once again for all your help.