I need help with Table Design and Relationships

[Thomas]

I'm trying to make a Products Database. Each product has multiple specifications. Some specifications are numbers with varying precision while other specifications are text. I currently have the data type set to text and that seems to work, but I lose functionality if I ever want to do some math using those numbers...that are formatted as text. Also, later on, when I'm not doing the data entry, it might get confusing for a new person to figure out which field gets 3 digits of precision and which one gets 5 digits of precision...or which one just gets a text string. I would like to choose a specification from a combo box and then have the appropriate text box...allowing either only text, 0.000 or 0.00000...or just 0.0. I've attached a snip of my current design. I hope one of you may be able to point me in the right direction.

Product: 1 Vendor, 1 Resource, 1 Name, 1 Product Type and MANY Specifications
Vendor: 1 Vendor Number, 1 Name, MANY Products
Resource: 1 Resource Number, 1 Resource Name, 1 Resource Type, MANY Products
Product Specifications: 1 Specification Name, 1 Specification Value (But, each specification has a need for its own Data Type, i.e, Melt Index = 0.000, Density = 0.00000, Appearance = "Clear and Bright"

I'm stumped and need your help. I'm new here, so please pardon any rookie mistakes. Thanks.

[CJ_London]

but I lose functionality if I ever want to do some math using those numbers...that are formatted as text.

use the eval function e.g.

eval("10" & "*" & "15") will return 150

with regards precision, that is not possible at the table design view, you will need to use some code in a form to amend the validation or to recalculate. But you can include a field to direct that validation/calculation

It's not clear from your post where that might go but might for example be in the product specifications table called say specType and then populated with input mask strings such as @, 0, #.00, #.000

then in the enter or got focus event for a control (e.g. mycontrol) on a form you would have code

mycontrol.inputmask=specType

or instead of using input mask strings, use format strings (or just specify the number of dp, leaving blank for text) and in the after event of the control put

mycontrol=format(mycontrol,specType)

[orange]

Thomas,

Can you provide some sample data to show us what "specifications" and a few of your terms involve?
You may also find that describing a day in the life of your business --who does what, when ....-- will paint a useful picture of what you are trying to accomplish.
I see COA and railcar but have no idea where or how these fit and/or relate to one another.

Is this your first database?
Good luck with your project.

[Thomas]

Thanks for the quick reply Ajax. After reading your reply, I think using either input mask strings or format strings might be what I need to do. I'm just not sure how I would accomplish that. In my Product Specifications Table, I would have a Field named SpecType, but how would I populate it with the input mask strings? Sorry, I'm just getting my arms around table relationships. Thanks.

[CJ_London]

how would I populate it with the input mask strings

having created the field, just type them in. To learn about them, see these links

https://stevechasedocs.wordpress.com...k-cheat-sheet/
https://support.office.com/en-us/art...2-4a47832de8da
https://www.quackit.com/microsoft_ac...oft_access.cfm

[Thomas]

Thanks for your reply.

This is my first database. I've been using Excel for a few years, but am now trying to teach myself Access. My plan is to convert the attached Excel Worksheet into an Access database with some of the bells and whistles.

A Day In The Life: We have Resources, our raw materials that we convert into plastic sheeting. We buy Products from Vendors. A resource may have one product or many products. We may buy one product or many products from a vendor. Each combination of Product and Vendor may have one or many Specification. Each specification has its own data type, mostly a number, but sometimes a text string. Each product arrives from the vendor with a Certificate of Authenticity (COA). We have to have that COA on file before we can unload the product from a railcar into one of our silos. We have to compare the vendors in-house testing results, reported on the COA, with our specifications. If all is well, we unload the resin and make plastic. We keep track of when we start unloading the railcar and when we finish.

I have already figured out how to make dependent cascading combo boxes and that works really well, especially since we have so many products from varying vendors. I'd like to have a combo box that offers me a choice of specifications. Once I choose that specification a text box is formatted to receive the number (with precision) or text value...whichever is required based on the specification I chose.

Thomas

[CJ_London]

I'd like to have a combo box that offers me a choice of specifications

create a table called say tblSpecifications with fields SpecName and Spec. Because this will be a short table, suggest make Spec the primary key

then create your combo based on this table for the SpecType control.

[Thomas]

Thanks Ajax. Your links provided good information. I know a little about input masks. I guess I was just confused because I have a lot of specifications. I was thinking your suggestion was to give one specification several input masks. Just shows how little I really know. Anyway, I'm now thinking I need a table for Specifications with a Field for SpecificationNames and a related table with SpecificationTypes...or something like that. Thanks. I think I may be in over my head.

[orange]

@Thomas,

Here is a link to articles and videos on Database Planning and Design that you may find helpful.

Good luck with your project.