How to connect data

[Ms Kathy]

We have an Items table (item number is PK) that contains general information about an item (color, weight, description, etc.). There are also Production tables which allows us to enter the Item number and a depositor (both PK) so that we can enter different settings (however, in the same fields); there are several other fields in this table as well. My challenge: We have one line that not only has different settings, but also has different FIELDS based on the depositor selected. "Depositor 1" may have 15 fields whereas "Depositor 2" may have 50 fields. I can create the tables with the required fields, but how do I link everything together? Thank you!

 

[David R]

I suspect a normalization problem, but we need more information.

What is the difference between Depositor1 and Depositor2? Are the other 35 fields just optional, or are they two different 'classes' of Depositor?

 

[Ms Kathy]

They are required fields - two different types of depositors

 

[Ms Kathy]

I'm thinking that perhaps the best thing I can do at this point is just create another production line table - one that holds all the data/fields needed for Dep.1 and the other that holds the data/fields needed for Dep. 2.

 

[David R]

Why does one have so many more fields than the other? And are the other 15 fields in common between both types?

If so, you could save space by having a table_Depositors_Additional which was optionally joined 1:1 to table_Depositors. Put your additional fields there, and you'll save space in the main table. The LAST thing you want is to have tableDepositors1 and tableDepositors2!

 

[Ms Kathy]

That's the issue - there are no common fields. The settings are completely different because the machinery is different.

 

[David R]

Okay, can you give a NSFW (i.e. not real data) summary of the fields? Doesn't have to be all 50, but give us an idea how they differ and how they are similar...

 

[Ms Kathy]

Settings (to enter) on Depositor 1 (for each applicable item):
Exhaust 1
Exhaust 2
Oven Damper
Zone 1

Settings (to enter) on Depositor 2 (for each applicable item):
Deposit length
Volume
Speed
Synchro begin
Synchro end
Head location
Rotation degrees
Pitch length
Etc.

 

[Ms Kathy]

More info to come - please wait

 

[Ms Kathy]

On all the other production line tables the depositor settings are different based on the item being produced; however the fields are the same (we use the same equipment). However, on this line the equipment is entirely different. My thought is that I should make 2 production line tables (and not have the depositor field) because of all the differences . . .

 

[David R]

For some reason I had hallucinated that this was some sort of banking deposit software... sorry!

I'm actually not certain how you should proceed in this case, then. My instinct is with yours to have two separate tables after all (two subforms, too). Then you can make your form swap in/display the correct subform for the ItemType/ProductionLine (not sure on your terminology). Each one would be attached One-to-many (LEFT JOIN) to your main table, allowing you to fill out one or the either. You may need to do some fiddling with dlookup if your productionIDs have to be sequential, of course...

 

[Ms Kathy]

scary to think someone like me would be designing bank deposit software!

Yep, I'm going to proceed with the 2 tables. It seems to make the most sense to me since the information is so different. I feel much more confident now that you are in agreement.

Thank you kindly for your attention!

 

[jdraw]

I'd like to see a brief overview of the business processes involved in order to understand the issue. My suggestion is for you to work through this tutorial to get your tables and attributes organized and to set up your relationships.

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

You should not be guessing, or doing trial and error, as to how many tables you need.

 

[Ms Kathy]

I will check out the information in this link and proceed from there. Thank you kindly.