Building tables for jewelry; multiple one to manys

[bigalpha]

A ring can be made from different metals and can also have different gemstones.

Ring 1 can have different 'varieties' (Var):
Var 1: ring 1, metal 1, gemstone 1, gemstone 2
Var 2: ring 1, metal 2, gemstone 1, gemstone 2
Var 3: ring 1, metal 1, no gemstone
Var 4: ring 1, metal 2, no gemstone
Var 5: ring 1, metal 1, semi-precious
Var 6: ring 1, metal 2, semi-precious

I can't figure out the best way to record this data so I can pull Var 1, ring 1 without getting the other Vars included. The goal is to build a report that shows each Var separately.

Current set-up:
tblGem
GemPK

TblGemLink
GemFK
InventoryFK

tblInventory
InventoryPK

tblMetal
MetalPK

tblMetalLink
MetalFK
InventoryFK

I feel like I'm missing something simple here.

 

[RainLover]

A ring can be made from different metals and can also have different gemstones.

Ring 1 can have different 'varieties' (Var):
Var 1: ring 1, metal 1, gemstone 1, gemstone 2
Var 2: ring 1, metal 2, gemstone 1, gemstone 2
Var 3: ring 1, metal 1, no gemstone
Var 4: ring 1, metal 2, no gemstone
Var 5: ring 1, metal 1, semi-precious
Var 6: ring 1, metal 2, semi-precious

I can't figure out the best way to record this data so I can pull Var 1, ring 1 without getting the other Vars included. The goal is to build a report that shows each Var separately.

Current set-up:
tblGem
GemPK

TblGemLink
GemFK
InventoryFK

tblInventory
InventoryPK

tblMetal
MetalPK

tblMetalLink
MetalFK
InventoryFK

I feel like I'm missing something simple here.

tblGem
GemPK
InventoryFK
MetalFK

The other tables are where data is stored like Inventory. This feeds a ComboBox

If this is too confusing then post a Database with Tables only and the relationships set as best you can.

When posting, please use 2003 or older format. We don't all have the latest.

 

[bigalpha]

tblGem
GemPK
InventoryFK
MetalFK

The other tables are where data is stored like Inventory. This feeds a ComboBox

If this is too confusing then post a Database with Tables only and the relationships set as best you can.

When posting, please use 2003 or older format. We don't all have the latest.

Yeah, I don't quite understand.

I also tried to convert my db into a 2003 and older version but kept getting an error "can't save in an earlier format because it's using feature not available in earlier versions". I got this error even after i deleted everything out of my database. I've uploaded the 2007 version for now.

 

[bigalpha]

tblGem
GemPK
InventoryFK
MetalFK

The other tables are where data is stored like Inventory. This feeds a ComboBox

If this is too confusing then post a Database with Tables only and the relationships set as best you can.

When posting, please use 2003 or older format. We don't all have the latest.

Ok wait - do you mean to store the MetalFK in tblGemLink instead of in tblMetalLink?

 

[RainLover]

I downloaded your file but could not find any tables.

Has anyone else been successful and can post a copy of this in MS Access 2003.

The other solution is to post a pic of the Relationships.

 

[RainLover]

It would appear that you may have a split system and that you need to post the back end which is the one with the tables. The front end only has a link to real data.

 

[bigalpha]

It's not split. I kept it all together because I was working on it from two different work stations.

I've re-uploaded the Access 07 version.

 

[RainLover]

I looked at tblJewelryInventory. It looks good to me and is how you should be building the other Tables.

I am referring in particular to the use of Foreign Tables/Keys.

So if you build your current table in a similar manner then you would be correct.

Having said that I feel as though there is more to the problem or I have misunderstood.

Please let me know.

 

[bigalpha]

With the way the relationships and tables are set up right now, I can't differentiate between different variants of the same ring.

Think of it like shoes: each shoe can have multiple sizes and each size can have multiple colors. I don't know the correct way to associate each unique size and color to each shoe. In my case, each unique combination of metal and gem to the jewelry.

Do I need to store the MetalFK inside tblGemLink?

 

[RainLover]

Do I need to store the MetalFK inside tblGemLink?

Please explain what these are using simple English.

I can't differentiate between different variants of the same ring.

This also needs explaining.
Also variant is a reserved word so please do not use it.

Are you trying to set up some sort of inventory control.?

 

[bigalpha]

Please explain what these are using simple English.

Sorry, I used terms that I outlined above - they are specific to my tables.

This also needs explaining.
Also variant is a reserved word so please do not use it.

Are you trying to set up some sort of inventory control.?

I haven't used 'variant'; was just using it to try to explain what I'm trying to do.

Yes, it's an inventory of jewelry. I don't know the best way to set up the tables to do so. The way it's currently set up does not differentiate between different unique combinations of metal and gems for each piece of jewelry.

This table set up can be likened to shoes: 1 style has many types of sizes and each style and size has many types of colors.

For me: 1 piece of jewelry has many types of metals, and each type of metal can have many types of gemstones.

Edit: I was also able to finally get this converted to 2003 format. I've attached it.

 

[RainLover]

For me: 1 piece of jewelry has many types of metals, and each type of metal can have many types of gemstones.

Therefore ONE piece of Jewelry, say a bracelet is made up of 4 different types of metals and each type of metal can have say 8 gemstones.

So the one bracelet has 4 metals and (4*8) 32 gemstones.

This is what you are telling me. Is this correct.

Also can you fill in the part of your profile that says where you are. It helps to know this because this tells me when you may be online compared to me.

If anyone else can help please chime in.

 

[RainLover]

I had a look at some of your other posts.

It would appear that you have gone too far before getting the basics correct.

e.g. Why are you using a cross tab query. These are not normally used by beginners or do I have the incorrect. Maybe you have just made a simple mistake somewhere in the beginning.

 

[bigalpha]

Therefore ONE piece of Jewelry, say a bracelet is made up of 4 different types of metals and each type of metal can have say 8 gemstones.

So the one bracelet has 4 metals and (4*8) 32 gemstones.

This is what you are telling me. Is this correct.

Also can you fill in the part of your profile that says where you are. It helps to know this because this tells me when you may be online compared to me.

If anyone else can help please chime in.

Yes, you are correct.

 

[bigalpha]

I had a look at some of your other posts.

It would appear that you have gone too far before getting the basics correct.

e.g. Why are you using a cross tab query. These are not normally used by beginners or do I have the incorrect. Maybe you have just made a simple mistake somewhere in the beginning.

That's probably a different database. That's one that I maintain for work.

This one (jewelry) is something I'm helping a friend from and had a brain block when setting this up.

 

[RainLover]

Thanks for entering your location. I thought it we somewhere else based upon the times I get notifications.

I have to ask you this question from post # 14.

One bracelet has up to or could very well have more than 32 gemstones.

And you want to track the location and number of all these various bracelets.

Please think about this before answering.

This does not appear practical to me unless you have a very large number of bracelets in stock.

 

[bigalpha]

One bracelet has up to or could very well have more than 32 gemstones.

And you want to track the location and number of all these various bracelets.

Please think about this before answering.

This does not appear practical to me unless you have a very large number of bracelets in stock.

I don't think any of the jewelry will have 32 or more gemstones in it. I can't imagine there being more than 6 types (at most) of gems per each piece of jewelry.

However, I don't quite understand by what you mean when you say that it's not very practical?

 

[RainLover]

There are two solutions as far as I can see.
One is "one to many" and the other is a "many to many" structure.
The actual names you have given like GemLink and Gem, Metal Link and Metal don't give me a full understanding of what is what. I realise you understand because this is your business. Other names like FindingLink, ShowLink GemLink and so on have me totally lost.
A few year ago I wrote a Demo database on Many to Many together with an attached explanation. I don't believe I finished it but I have attached it in the hope that it may help you.
You have this Union Query. Do you understand what it is for. If you do could you please explain.
Other than I am running out of ideas on how to help. Let's hope that the attached helps.
　

 

[bigalpha]

So, I think that I figured this out properly. Thanks to your help and other stuff I've read and such I think this is the correct way to build the tables.

Essentially, the JewelryInventoryLink creates a subinventory of findings, metals and gems, then that is what gets associated with the main inventory. This should allow me to create any number of variations of each piece of jewelry.

Now if I can just get my subforms to work correctly, I'll be peachy (I think).

Thanks again for your help and patience, It's massively appreciated.

 

[RainLover]

I don't understand some aspects of this. However you are using Proper Names like "Finding Link" and I have no idea how this applies in the real world.

The point is at the moment is, do you understand and is this going to give you what you want.

When you get to this stage, you should be able to describe the inputs and Results that this data base will deliver. You should be able to do this using the name of each table and some keys to explain the flow of data.

I will be around should you need further help. Or perhaps if you have a particular question then feel free to start a new thread for something specific. This will also bring in different ideas from the other members rather than just my opinion.