Relationships, table _1, UOM, list box

[Opus]

New to Access and trying to get my head around relationships.
Here goes.

I have a table for tblProducts with multiple fields of units of measure(UOM) which will be list boxes for the end user to select on a form:
QuantityUOM
WeightUOM
3x DimentionsUOM

I have a table of tblUOM that I want to conect to each field, I have connected them with tblUOM tblUOM_1 tblUOM_2 etc using the same table.

Is this the correct way to do this

 

[Ranman256]

You wouldnt build a relationship...it sounds like its a self-relating table.
The query is enough.

 

[jdraw]

@opus

Can you give a few examples where UOM will be used with real Product records?

Are you saying for example WeightUOM could/would contain lbs, kg, grams, ounces tons etc? If so, you will need a common UOM to do weight calculations.

A little context for your proposed database would also be helpful.
Getting your tables and relationships designed to meet your business needs is a first step before getting too involved with Access (forms/listboxes...)

 

[Opus]

Thanks for the replies.
I think a picture paints a thousand words, hope this works. please see attached.

Disregard 1Qty & 1UoM, 2Qty & 2UoM fields in the product Table, as you can see I'm playing with a many to many relationship as a conversion table. lol next post.

 

[JLCantara]

@Opus: you are obviously not familiar with DB creation...Here is a solution to your UoM:

Products
Product: autoPK
...
Quantity: integer or single
QuantityUnit: long, FK
...
Weight: integer or single
WeightUnit: long, FK
...
Package: long, FK

QuantityUnits
QuantityUnit: auto, PK
QUnitName: text(12)

WeightUnits
WeightUnit: auto, PK
WUnitsName: text(12)
Conversion: single ' To convert weight unit to a standard one (like Kg)

Packages
Package: auto, PK
Volume: single (standard unit???)
TotalWeight: single (very useful for shipping)

Good luck, JLC.

 

[Opus]

@JLCantara

That’s great, but it still does not tell me how to restrict the UOM descriptions for the end user to select from in numerous fields/list box in one table.
:banghead:

Thought this would be common place in product and order BD's?

I'll have a poke around with quires; I'll get there in the end. lol

 

[Opus]

got it!

Feel stupid now. Trying to run before I can drool

 

[JLCantara]

Come on! It happens to every one (even me...).

 

[Opus]

Lol, I'll get there. My company is paying for a days one on one tutorial, so I wanted to get the basics on my own and most of the labour intensive work out of the way.