Very different items in a single table?

[MandarkDC]

I am a total novice at access, who wants to move his inventory from Excel to Access, and add some functionality. One question that I haven't really seen answered in the general introductions to Access is, what to do if I need a table with very different types of objects. It could be resistors, where I need to know things like 'value,' ' voltage rating,' and 'footprint.' While something like a switch only has physical dimensions. Both items will have some things in common, like 'price' and 'location'. I've read somewhere, that you cannot make a single query on unrelated tables, and I want to query my inventory through a dropdown menu, where I can choose what kind of item to search for. What is the best way to organize the data? A single table with some optional fields, or perhaps different tables for each type of items?

I'm just looking for a general answers, which might help me google my way forward :)

MD

[Jim Doherty]

I am a total novice at access, who wants to move his inventory from Excel to Access, and add some functionality. One question that I haven't really seen answered in the general introductions to Access is, what to do if I need a table with very different types of objects. It could be resistors, where I need to know things like 'value,' ' voltage rating,' and 'footprint.' While something like a switch only has physical dimensions. Both items will have some things in common, like 'price' and 'location'. I've read somewhere, that you cannot make a single query on unrelated tables, and I want to query my inventory through a dropdown menu, where I can choose what kind of item to search for. What is the best way to organize the data? A single table with some optional fields, or perhaps different tables for each type of items?

I'm just looking for a general answers, which might help me google my way forward :)

MD

Hi

Welcome to Bytes :)

Firstly you should look at the whole concept of relational database design namely the way data is 'broken down into its smallest entity and stored. Once you have grasped this then practice makes perfect, or as near that goal as you can make practically. Where you might not know a particular direction to take on a specific point then feel free to ask :)

Don't believe too much you hear about what you can and cannot do unless it is someone who knows the rules and principles already. You can join data together in many ways but... if it does not follow a rule that is when the spurious results show themselves.

The following is an illustration of relationships and tables structures to start you on your road:

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

[MandarkDC]

specifically

Hi Jim, thank you for the reply. I've read the link you sent me, and I shall probably need to refer to it during the entire design :)

I do have a more specific question though, if that's okay. The items that need to go into the inventory need different fields for describing them. So if I add a resistor to the inventory list, I'll need to add data to a "value" field, and a "rating" field. While an aluminium case has 3 or 4 different physical dimension fields. My understanding so far is that it would best be done by making a separate table for resistors, adding different resistors to that table, and then bringing the resistors as a foreign key into the inventory table. Then a separate table for aluminium case and so forth. Is this a good approach? (It does uphold the rules for normalization, I think).

Secondly (and this seems to me will become a bit more tricky), could you make a single form with a dropdown menu, where I can choose which type of component to add (resistor, casing, whatever), and then have the relevant fields pop up underneath?

[Jim Doherty]

Hi Jim, thank you for the reply. I've read the link you sent me, and I shall probably need to refer to it during the entire design :)

I do have a more specific question though, if that's okay. The items that need to go into the inventory need different fields for describing them. So if I add a resistor to the inventory list, I'll need to add data to a "value" field, and a "rating" field. While an aluminium case has 3 or 4 different physical dimension fields. My understanding so far is that it would best be done by making a separate table for resistors, adding different resistors to that table, and then bringing the resistors as a foreign key into the inventory table. Then a separate table for aluminium case and so forth. Is this a good approach? (It does uphold the rules for normalization, I think).

Secondly (and this seems to me will become a bit more tricky), could you make a single form with a dropdown menu, where I can choose which type of component to add (resistor, casing, whatever), and then have the relevant fields pop up underneath?

Your paragraph 2 is spot on....think of breaking your data down into its logical elements. Do not be intimidated by the theory, remember this, even seasoned database designers amend data models part way through as things take shape where matters could not have been foreseen.

Your last section about displaying information on a form, do not worry to much about that. For the most part you can pretty much display whatever you want as you will no doubt discover, as you become proficient.

You might even choose to break the normalisation model in certain cases in favour of a particular strategy but until you fully understand the the model may I suggest that you try to implement the theory of it fully.....after that the pros and cons concerning design are subject to debate under the general analogy of 'design' versus 'performance'

The construction of your database obviously relies on what it is you you need to record in accordance with your business needs. One might consider a 'resistor' to be a component part of something larger for example a circuit board or indeed something else. Straight away then we see a resistor as 'one' of 'many' items that could be added to a circuit board a classic one to many relationship where the term 'circuit board' fits higher up the data hierarchy.... if I am making myself clear. (I will not speak about 'Many to Many' relationships at this juncture in favour of not getting bogged with complexity until you understand the basics)

You might consider it relevant to store the resistor in a table called tblComponents. If your business was primarily concerned with selling circuit boards you would happily want to see it as 'one' of your main product lines and therefore store it in a table called tblProducts. There would then be a one to many relationship defined between a ProductID field as the primary key on the 'one' side with a ProductID field in the tblComponents table as well (the Foreign key) having cascade update/delete options set between the keys (see the relationships window to see how this works).

We therefore have a simple 'one to many' relationship between a tblProducts table and a tblComponents table both of which have a record in them (one circuit board and one resistor) From this point onwards you can add as many components as you wish to the components table where there exists a main product on the one side against the foreign key on the many side. Its as simple as that really.

Now I hear you say.."hey but any one of my my resistors can have a 'rating' that can be anywhere between the range of 1-10. Does this data value of 1-10 in itself have to be stored in a table independantly?"

The answer is 'No!' the result you want to store against the resistor can be easily stored with the resistor on the same data row as the resistor.

You might wish to consider that the singular 'pickable' data values themselves existing between the range 1-10 can rightly be stored independantly in their own table and 'looked up' by the use of a dropdown combobox. In this case you would create a single field table called 'tlkpRatings' with a field called 'Ratings' and populated with the data values of 1,2,3,4,5,6,7,8 ,9,10 one value for each row.

We see here then, that by breaking the data into simple entities that we consider to have ONE to MANY attribute status we are creating main tables, sub tables and lookup tables taboot that are interacting with each other.

You mention an inventory table well lets call this tblInventory. Your inventory table will have its own primary key lets call it InventoryID that might be the autonumber datatype. But an inventory can mean an inventory of products or inventory of components etc etc. Here the term 'Inventory' is best thought of as an individual item in itself that has a specific purpose identified by the type of entory that it is. You might consider adding a field to the main inventory table called 'InventoryType' Thereafter each individual inventory record should have a sub list attached to it containing the vidual items for listing in that inventory record. Each item should be contained in a separate table as individual records.

So create a new table called tblInventoryIte ms with its own Primary key and having a foreign key created in that table that matches the datatype existing in the tblInventory table. Hopefully here you see yet again 'one to many relationship in action.

All you need to add to that tblinventoryIte ms table is the primary key for the individual resistor from the tblComponents table, nothing else! ok?

Now at this point there is a flaw in this design if you are intend to rely on numeric primary keys to identify products and components that both get added to an inventory table list. It will become rather obvious and that is this:- duplicate values that may well exist in multiple rows if each inventory record item is identified merely by numeric key data values.

I think it is important to layout your own principle understandings before advancing into the realms. Test a few things out, see how any of this works set against your understanding of listboxes,combo box dropdowns. In particular look at how queries work particularly the feature Access calls 'AutoLookup' which whilst a feature is purely SQL doing its work as normal.

Given that you are new to Access, may I point you in the direction of looking at and studying an object naming convention namely a standard by which you refer to any objects you create within any given database. It is a disciplined approach, appreciated by others trying to work to a given standard and helpful when writing code, SQL and and other related material. I personally recommend the Roddick/Lechinsky naming convention.

In addition to this look at 'non-usage' of 'reserved' words. Both of these areas I feel will give you a good start in forming a working approach to using Access.

http://en.wikipedia.org/wiki/Leszynski_naming_convention

http://bytes.com/topic/access/insights/659167-reserved-words-access-especially-date-time

Regards

[MandarkDC]

Thank you, good sir

Excellent reply. I am of course still a little confused, but this has really helped me on my way.

Cheers

[Jim Doherty]

Excellent reply. I am of course still a little confused, but this has really helped me on my way.

Cheers

You are very welcome :)