Junction Table and Table issue

[BartK]

Hello everyone,
I'm having some trouble getting my information that I input on my form to store in my correct table. I will attach my DB so you can take a look at what I have thus far.

Here is what I am wanting to do:
I have TblEmployee, TblEquipment and TblJunction and FrmTracking and FrmUpdate

I input the bulk of my information thru FrmTracking, my trouble is I can not seem to get the information that I input in my FrmTracking to store in the correct table. I can get the information to store in TblEmployee, however the information that I want to be stored in TblEquipment will not store in there.

Any help would be greatly appreciated. Thank you.

 

[EternalMyrtle]

I will take a look but first things first: you should not name your junction table tblJunction. Name it something like tblEmployeesEquipment

The reason being that if your database grows and you have more tables and more junction tables, this name will not be very useful. You should make it clear which tables the junction table is linking. Also, if you start making a lot of queries, calculated fields and using code, it will be MUCH harder to change this in the future.

 

[EternalMyrtle]

I cannot open your database.

 

[BartK]

It didn't even occur to me to name it that way, but it does make sense to call i that way in the event that it does grow. I have called the aformentioned TblJunction TblEmployeeEquipment and have attached the DB yet again.

I am running Access 2010, don't know if that might be a hurdle for you not being able to open it.

 

[EternalMyrtle]

Yes, that is likely the reason. I still have 2007.

What is the recordsource you are using for your main form? Does the form include a subform?

 

[BartK]

No subform, I'm just trying to input everything in on my FrmTracking, source for the FrmTracking is split, I have half of it going to my TblEmployee and here is where I'm having a little trouble. Do I have the other half point to TblEmployeeEquipment or TblEquipment?

Ideally what I want is to be able to track different pieces of equipment that is issued out to a person and then be able to delete those particular records when that piece of equipment comes back in.

 

[EternalMyrtle]

In most cases, you would use a subform if you are entering information from different tables into a form. Generally the main form would be based on your main table and your subform would be based on a junction table. This is the typical scenario.

So, you would have an employees form based on tblEmployees. Then the equipment would be entered through the subform which would be based on tblEmployeesEquipment.

 

[BartK]

Ok, If I wanted to issue some equipment to a person I would have to set it up like this:

John Doe Phone 23 Radio 45 Gas Detector 15

I would have to have Main Form: First&Last name (2 different fields) TblEmployee
SubForm Phone,Radio,GasDetector (3 different fields)TblEmployeeEquipment

I was kind of hoping just one main form to input all information into instead of having to jump between forms. Making much sense what I am shooting for?

 

[EternalMyrtle]

For one, you can make a subform look as if it is not a subform if you are crafty. But yes, it is a separate form to maintain.

You may be able to get away with making the recordsource on the main form a query based on tblEmployees and tblEmployeesEquipment but it is not recommended.

On another note, why do you have those fields in your junction table? It should be like this:

tblEmployeeEquipment
EmployeeEquipmentID (PK)
EmployeeID (FK to tblEmployees)
EquipmentID (FK to tblEquipment)

Conversely you could make EquipmentID just Equipment but use a combo box to store the values in a value list.

 

[EternalMyrtle]

So, you should not have three separate equipment fields. Phone, Radio and Gas Detector should also be in one equipment field.

 

[BartK]

I do have the junction tbl setup that way, however if I use a subform is it putting the information into the TblEquipment? And if that is the case then what is the purpose of the junction tbl? I've done some reading into what I want to get accomplished and from what I've read people are saying I need to use a juction tbl.

I haven't done anything with subforms, do they basically do the same thing as a regular form does?

 

[EternalMyrtle]

No it would still be putting the data into the junction table. EquipmentID would be a lookup to tblEquipment and the ID# would be saved in the junction table.

Yes subforms do the same thing: it will enter the data into your junction table just like the main form will enter it into your main table.

Did you set up your table relationships properly? Go to the relationships window and create them there. You should have a one to many from tblEmployees, EmployeeID(PK) to tblEmployeesEquipment, EmployeeID(FK)

 

[BartK]

I believe it is setup correclty, please view image.

 

[EternalMyrtle]

The relationship between tblEmployees and tblEmployeesEquipment looks good.

tblEquipment needs some work. Basically you should ONLY store equipment types in there. You need yet another junction table for checkouts.

tblEquipmentCheckout
EquipmentCheckoutID (FK)
EquipmentID (FK to tblEquipment)
EmployeeID (FK to tblEmployees)
CheckoutDate

See how complicated this gets?? 'Tis the reason that we didn't keep the name tblJunction

seems complicated but it is better structure, more normalized. In the end you will be able to mine your data better/

 

[EternalMyrtle]

I should have been even more clear: in tblEquipment your fieldname should be EquipmentName or something like that.

If you have several models under the same type of equipment (so 10 phones, for example) you will need to think through some more

 

[BartK]

I had no idea it would become this involved. Thank you for the step by step explanation of this. I have attached another photo, let me know if it looks correct to you and where do I even begin on subforms. I looked into that a little, however it just wanted to pull the information from the tables, much like a select query does. Thank you for everything once again.

 

[EternalMyrtle]

Ok, I now realize I made a mistake: you don't need both tblEmployeesEquipment and tblEquipmentCheckout because they are basically the same (except the latter has a checkoutdate).

So, you can just add the CheckoutDate field to tblEmployeesEquipment and get rid of tblEquipmentCheckout. Does that make sense?

Sorry about that. Just seeing it now that I see your relationships.

You still need to fix the equipment table...

 

[BartK]

Do I need to add the checkoudate field. Wouldn't the phonedate, radiodate and spotterdate suffice?? I'm trying to give each one a date in the event that the employee loses the radio but was able to turn in the other 2 pieces of equipment. I still want to have the ability to pull a report like once a week and see that he didn't turn in the radio.

One quick question, would we still need a field called "equipmentname" in TblEquipment? I thought by having separate fields it would be a lot easier to input the information in the tbl, or did you mean that we would have to have an additional field called "equipmentname" and if that is the case why?

 

[EternalMyrtle]

"Wouldn't the phonedate, radiodate and spotterdate

The way you are trying to do it is not normalized.

All of these are types of equipment and should go under one field. So the field is EquipmentType or something similar and the records are:

Phone
Radio
Spotter
etc.

Each of these will be its own "record" or row in a table (not a column). If you need to store makes/models/serial numbers, then you need more tables

The checkout table allows you to check out the equipment. The date goes there.

Each employee can check out one or more type of equipment so you will be accomplishing what you seek to do. Try it and you will see.

 

[EternalMyrtle]

you need to separate everything into distinct functions and break everything down into the most basic components.

You really should read up on the subject or normalization and relational database design. There is only so much anyone on here can help if you do not understand the fundamental principals of relational database design.

I don't want to sound mean or anything. Just being honest.