Setting up proper relationships

[Argi]

Hello Everyone.

I need some help of setting up the proper realtionships in a few tables. I am new to access so am still trying to get my head around these sort of problems.

I have, to keep it simple 3 table

ItemList
ItemSpecification
Packaging

I thought it would be a good idea to setup Packaging as a table as i have many items with only one type of packaging however we sometimes change the packaging dimensions and thought it would be nice to just cnahge it once in the Packagin table and use Cacade to update the ItemSpecification Table for all Items or records.

I used to have all the fields in Packaging in thr ItemSpecification table but since removed them for thr reason stated above. I am thinking of keeping one of the fields in Packaging the same as in ItemSPecification and using a combo box in a form to select the info form packagin and write it to the same field in ItemSpecification. I do nto have the filed ItemNo which is in ItemList and in IremSpecification actibg as primary key and i am doing same combo box thing stated above in this case.

How do i link Packaging table with ItemSpecification table? Should i do a one to many fro packagin to Itemspecification using a packafing filed? Or should i put ItemNo in the packagin field and link it some way.

Any help is appreciated.

 

[Isskint]

you would need a field in ItemSpecification that is also in Packaging. The logical one may be ItemNo unless you have more than 1 entry per ItemID in ItemSpecification.

Ideally, i would suggest, you want ItemList linked to ItemSpecification linked to Packaging

ItemList - ItemID(PK)
ItemSpecification - SpecID(PK) + ItemID(FK)
Packaging - PackID(PK) + SpecID(FK)

 

[Argi]

Isskint thanks for responding to both my posts. Can you please responnd again to me using the proper fields names, sorry i had not provided them in origianl post as i thought i could figure it out but i may have messed up some of the name. Please see below


Table1: Item List

Fileds
ITEMNO
DESC
Case Gross Weight (KG)
Pack Size Net Weight (KG)

Table2: Zeea Product Spec DB

Some of the fileds in this table are
ItemNo
Case Gross Weight (KG)
Pack Size Net Weight (KG)

There is currently a form called Zeea Product Spec DB with all the fields in the table Zeea Product Spec DB in it

There is a 1 to 1 relationship on ITEMNO between these 2 tables.

The new table i need to link is called Packaging with fields
ID
ITEMNO
Packaging
Pack QTY
Pack Size
Pack Type
Case Length (cm)
Case Width (cm)
Case Height (cm)
Cases Per Layer (TI)
Layers Per Pallet (HI)

All fields in all tables are empty (no data), i added ITEMNO to Packaging for link purposes but its not realy needed from a data perspective. All fileds in Packaging were in Zeea Product Specification DB table but i took them out and created this new table so i can update them easier instead of having to update each record in Zeea Product Specification DB.

I did not quite understand what yo meant when you wrote
temList - ItemID(PK)
ItemSpecification - SpecID(PK) + ItemID(FK)
Packaging - PackID(PK) + SpecID(FK) in previous post, i use relationship GUI to drag and drop fields. Do you think i shoudl link the Packaging filed in Packaging table with the packaging filed in Zeea Product Specification Table? Or should i just link ITEMNO some way? Every ItemNo in the Zeea Production SPecification DB table can only have 1 value from Packaging. So i guess this means 1 to many (from Packagin to Zeea Product Specification for the Packaging Filed), is that correct?

 

[Isskint]

So long as ItemNo refers to the same thing in each table, just set up a relationship to ItemNo in Packaging. It probably does not matter which table you relate to (although some of the brighter guys may have an opinion on that) but i would use ItemNo on Zeea Product Spec DB.

As for the (PK) and (FK) these are Primary Key and Foreign Key. The field in table2 that you relate to a primary key in table1, is called the foreign key (i think)

 

[Argi]

Okay great thanks. So i relate ItemNo on the Packaging Table to ItemNo on the Zeea Product Specification Table and i use a 1 to many? what kind of join and is the join from Packaging to Zeea Spec DB or the other way around (the one part). Or does it matter which way? I hate to ask so many questions but i want to get it right. I am thinking that since the Packaging fields can only apply to 1 ItemNo or a given ItemNo can only have 1 Packaging then it shoudl be a 1 to 1 but a bit confused.

 

[Isskint]

...is the join from Packaging to Zeea Spec DB or the other way around (the one part). Or does it matter which way?

I would say from Zeea Spec DB to Packaging.

...I am thinking that since the Packaging fields can only apply to 1 ItemNo or a given ItemNo can only have 1 Packaging then it shoudl be a 1 to 1 but a bit confused.

Think you answered yourself there Argi

 

[Argi]

I still need to be a bother and clarify something. The whole i dea of the Packaging table was so that when our packaging changes i can go into the table and change a field that would update automatically all records from the Zeea Product Spec DB. This way i wodl not have to go through every record in Zeea Product Spec DB and change packaging criteria. I am not sure this will be acheived with the join. If i create a join from Zeea Product SPec DB to Packaging then wont the packaging Table have a record for every ItemNO? If so then when packaging changes i will need to go into Packagin table and change every record. Is this right? If i join Packaging filed in the packaging table to the packaging filed in the Zeea Product Spec DB with a 1 to many with referential intergrity and cascade checked will this acheive my goal? will it work? do i even need ItemNo in the Packaging field? I still have a hard tiem gettinng my head around the relationship side of the DB's but it is the most important i guess. What do you think Isskint?

 

[Argi]

Isskint i tried the 1 to many from Packaging to Zeea Product SPec DB on the packagin field, it seems to work, i tested and seems okay. I am still a bot worried i did not miss anything. I removed the ID field and the ItemNo field and made the Packaging field the primary field (there will only be 10-20 records in here at any time), what is your opinion on this? DO i need the ItemNo field in the table? Thanks.

 

[Isskint]

Based on the info supplied so far, I would have said you need ItemNo - what else joins the 2 tables together? However if it is working the way you want, that is half the battle won. Try 'breaking' it - put in data that would confuse or duplicate and check it still works.

 

[Argi]

Pat im starting to get it, makes sense on the cascade. Also thaks for the point on embeddded spaces and characters, i have made most of those changes already, just need to do the special characters now as i missed it during first read.
About the golf ball example i need to ask you for clarification, in our case we would sell orange golf balls, red golfballs and blue gold balls. Any one if these items can only be packed in 1 type of packaging, so blue golfballs can only be in a 6 pack and red golfballs would be in a 12 pack and orange goldballs also in a 6 pack. But what happens is we may change the dimensions of the golf packaging, so our 6 pack box may end up being 1 inch taller as we changed the desing of the box. I wante to be able to go into 1 record, the record with the attributes for the 6 pack and change the one filed, say case height and have it update the whole databse or if desinnged properly that 1 filed update would be reflected in all forms, reports and records. I have attached the BB however it is missing the special character changes which i will do shortly. Hope you have time to take a peek at it.

 

[Argi]

This is the updated DB file. Please ignore last one

 

[Argi]

Here is update with relationships, any adivse appreciated. Thanks.

 

[Argi]

What happened to my support team . Did everyone lose interest already? Is there somethign wrong?

 

[Isskint]

Boo

Sorry Argi, been a little busy.
I can not help much further as i can not view all the necessary objects on your DB (i am poor and only have A2007). I tried saving as an A2007 accdb but there is some functionality in one of your objects (table, form etc) that will not allow me. I am sure Pat will check it out and come back to you in good time.

 

[Argi]

Isskint after a bit of research i figure out a way to 2003 the 2010 DB however fomr did not import so i assume it has the problem with the save as as it was not working. I looked it over and tables seem to be all the same. Please try to open this one. Let me know, thanks.

 

[Isskint]

Argi, thanx for that.
I have had a look but for some reason it keeps crashing. However what i saw, based on what you have explained and what Pat has pointed out to you, looks on track. The form i could not open (without crashing) and the same with the relationships window.

Not wishing to throw a curve ball at this stage, but did you consider an update query to achieve the updating function you are trying to coerce cascade in to?

 

[Argi]

ISSkint no i never considered anything as i have little knowledge on available options. I never imagined displaying data from diffirent tables on the same form would be such a problem for me. I always thouhgt it was as simple as choosing the filed from the other table and entring into the form, if the relationship was correct , then it should work. But turns out is is nothing like that. The query builder makes it easy to pick all required fileds, tables, operators, functions etc.. i can easily find the fileds in the other tables through this mechanism however getting them to display is some other issues. The DLOOKUP seemed an easy way to get this doen however it does nto work for me. I have 2 ItemNo in my form, one of them from the combo box, the other one the field that is being written to, but know tables have changed and required data is linked throgh PackagingID table, see attached.

 

[Argi]

I Think i am understanding how this is suposed to work, please see attached adn comment. I think it is more organized and you are completely right, 1 to 1's were doing nothing. ANy more pointers on the layout? Or anything else?