Correcting look-up fields

[bfriend5]

This is sort of a cross-post to an original problem of mine here: http://www.access-programmers.co.uk/forums/showthread.php?t=276943

plog was helpful and showed me what was going wrong in the database but I am unsure of how to really correct that (and I don't like to pester anyone with question after question and figured it be best answered under the Tables forum). plog has informed me that my form is based on a query and not a table like I had originally thought. My main question is how can I correct my tables so I can re-structure my form correctly?

Attached is an image of the relationships I have set up for the database.The main table I use is tblSample. As you can see, all other tables are linked to this in a one to many relationship. Is this the proper way of doing this? I have tried to normalize everything out but am unsure if it has been done properly (probably not seeing as I have come across several problems). Furthermore, I've been using look-up fields for the following: SampleTyp, Products, Requester, Classification, Panelists, Contact_Info... so about everything. Can any of you please point me to a better procedure to structure this properly? I've been going through MS Access 2013 for Dummies and the Access Bible but it's been slow progress. I know the learning curve is steep at first but I'm starting to get some aspects of it.

Any advice is greatly appreciated Thank you!

 

[WatsonDyar]

How large (number of records) do you expect tblSample to have? I ask because the number of fields in this table and the "attachments" field are concerning.

Have you put your db into place yet?

 

[spikepl]

Looks good to me but:

1. As per above, attachments are an Access invention that a) will make moving the db to any other system difficult, and b) may explode in size
2. Multivalues fields are not recommended by anyone here - do not use them
3. Get a consistent naming convention (it's all over right now) : your FK are there with a suffix, or not, same for Item and ItemID - if you are storing a key then make the name of the field reflect that, consistently

 

[RainLover]

I don't like your structure at all.

You have One table that is doing all the work. It simply has some lookups to make data entry simple. Ignore the lookup and complete the field manually and you end up with one very large Table. You may as well use excel.

Go back and look at the table. You should have groups of data that repeat. This should be stored in a separate Table.

For example, if you had an employee who had several qualifications you would put the main information in one Table. Like Name address DOB as these do not change. then the qualifications go into a separate table that is linked back to the employee table. This may contain multiple entries.

You relationships are not one to many they are one to one.

Suggest that you research relationships or normalisation again.

Hope this helps even though it is not what you expected.

 

[bfriend5]

No, thank you. That's exactly what I was thinking as well. I am trying to normalize the best I can but I have had zero formal training on the subject. I've been trying to do my research and it sounds like I need to re-structure everything, exactly like you said. I just get hung up when thinking about relationships, and what is considered a many to many as opposed to a one to many. It doesn't help I can't articulate my thoughts that well, but thank you for your advice.

If I understand you correctly, you are essentially saying that I need to re-structure by looking at the tables and seeing what repeats itself in the table for one particular field? The fields that repeat are what I have made look-ups- which is a big error in database design? And those fields should actually be there own table? Then I link that child table to the parent table with PK and FK fields? That's what I understand at least. Just one question though, is there a way to make it a drop-down type list for some of those without doing it as a look-up? I've got the Microsoft Access 2013 Bible from the library but haven't really come across an alternative for that, granted if the user has to manually enter it isn't all that bad, could just result in spelling errors?

Anyways, thank you again. I appreciate the time everyone here takes to help out.

 

[RainLover]

what is considered a many to many as opposed to a one to many.

The main table has One Record while the sub table has many Records. These are joined by the Primary Key (One of in the Main Table) to the Foreign Key in the Sub table. This can be many records.

eg The One table has a Location of say "Building 14". The Sub has many records of what is located within that Location. eg 3 Planes 4 Boats and sixteen cars. So that is 23 individual records each about separate items. Each item can be described like Large Red Car, Small blue plain.

The Many to Many requires an extra table. This could be where A school teacher has many students grouped into classes. Each Student has many Teachers, one for each subject. Do a Google and look for something that has a picture to describe the construction.

The use of Look ups is only wrong when you use them within the Table. If you use them in a Form it is not only correct but also encouraged.

Your job is to find those things in the main table that can Change. eg the price or date of manufacture. As you can easily see, the price may change Daily or Monthly etc. So you need a table to track these. What you are not doing is deleting the current price but rather adding the updated price. The date of change would normally be added as a separate field.

Hope this helps a little more. Please post back when you have another question or do not grasp what I have already said.

 

[bfriend5]

It's starting to make more sense, so thank you very much for that! I think the biggest thing I got hung up on was the particular use of the lookup fields. Before, I didn't realize it was incorrect to have the lookup in the actual table, but your comment about it being encouraged for forms has me trying to see it in a different light.

I have attached an updated image of my relationships. Just note that tblEvaluatedSample is still quite large because I am figuring out how to not lose any of the data that was already in it (only 15 records), but I have been trying to use append queries and will get rid of the duplicates. Anyways, I'd like to try to explain my thinking, if you could tell me if I'm on the correct path, that would be great. So, take tblProducts. In that table I have listed all of the products possible. These are a fixed set of items that won't change (very often, at least). Granted, items may be added down the road, but I figured that looking up the products in tblEvaluatedSample was the correct way to link the two together. Now, I'm seeing it as I just wanted the ProductID_PK from tblProducts to be stored in the ProductID_FK in tblEvaluatedSample, and that number would be the link between the two tables, pointing the evaluated sample to the associated product. My only question is how to use a lookup in the forms for the user to enter that particular product for the evaluated sample? Would I have to create a query to search all of the products? And then use that query as a control source for a combo box? But then how would that combo box be connected to the evaluated sample? That's my biggest question. Well I have several but I'm taking it one step at a time and trying to digest as much of the book I picked up as I can.

Thanks again.

 

[RainLover]

This Database may help you to understand Combo Boxes and Relationships.

 

[RainLover]

http://access.mvps.org/access/lookupfields.htm

This is an explanation of where not to use lookups.

This can be misunderstood. Some people think all lookups. In fact it is saying do not use lookups in Tables.

 

[RainLover]

You are using a lot of this but this is the full story.

 

[bfriend5]

Thank you for all of that information! I will definitely be taking a look through all of that!

 

[RainLover]

Good luck with the project and don't forget we are still here to help.