Appending records to one of many tables through a form, based on a combo box?

[esrigby]

Hi everyone! I'm making a stock control database at work and I'm caught up on this one feature.

Each part we keep in stock for manufacturing has a part ID, PartRefID, which is formatted as 00-0000. The first two digits are the part type code. For instance, 11 is metalwork, 12 is hardware, 13 is resistors, and so on.

Therefore I've made a different table for each part type, so that we can use the autonumber feature to generate new part IDs which all begin with the correct two numbers. I've then used a union query to pull together all this data and make it searchable in a form (say, to find all parts from Farnell regardless of part type).

However of course, this means that when we want to add a new part through a form, there are multiple part tables the new record could be added to, but only one of which it should be added to.

What I'm trying to do is create an input form for new parts, which has a combo box for part type. The end user then only has to select "metalwork" or "resistors", enter the rest of the part information (PartName, MinStockLevel, CostPrice, Supplier, etc), and click "save".

The idea here is that selecting "metalwork" would append that new record to tbl_metalwork, while selecting "hardware" would append it to tbl_hardware, and so on.

Please could anyone here give me some advice on how this can be done? Thank you!

 

[MarkK]

The idea here is that selecting "metalwork" would append that new record to tbl_metalwork, while selecting "hardware" would append it to tbl_hardware, and so on.

This doesn't make sense to do. Just add a field for the type distinction and put all the data in one table.
It is difficult to have to consult multiple tables for data that has the same structure. It is trivial, however, if all the data is in one table, to use a query to return subsets of that data. As a result, it is best practice to put all rows that have the same structure in the same table. If they have a distinguishing characteristic or dimension, add a field which stores that distinction, in your case, metalwork vs. hardware. That should be data in a field, not a different table.
hth
Mark

 

[plog]

Yeah, time to properly normalize (https://en.wikipedia.org/wiki/Database_normalization) your tables. All your parts need to go into one table.

Can you post a screenshot of your relationships tool?

 

[esrigby]

This doesn't make sense to do. Just add a field for the type distinction and put all the data in one table.
It is difficult to have to consult multiple tables for data that has the same structure. It is trivial, however, if all the data is in one table, to use a query to return subsets of that data. As a result, it is best practice to put all rows that have the same structure in the same table. If they have a distinguishing characteristic or dimension, add a field which stores that distinction, in your case, metalwork vs. hardware. That should be data in a field, not a different table.
hth
Mark

Hi Mark! Thanks for your reply.

I'll admit you're right that it would be a much easier solution to do that.

Unfortunately, the purpose of our part IDs isn't just to have a unique identifier for each record in the database. Our part IDs get used in practical situations outside of a database, like assembly diagrams, or labels on stock drawers.

If every part was just a four digit number with no type identifier, each new part we received in stock, as it was added to the database, would be almost randomly labelled. 0345 might be a transistor, 0346 a front panel... our drawers might still have their part names on them but they wouldn't be grouped in numerical order by type, which makes organising on limited shelf space a messy business.

For labels on diagrams, "0021", for instance, doesn't give you any information about what manner of beast you're dealing with, whereas "11-0021" is definitely metalwork. Now of course if you (or I, as my main job is the assembly) had a tablet or smartphone with this database open, you could as easily search "0021" and find that it's a Mentor 1271.1010 light pipe shield. But that's still not as much information, as concisely presented, as being able to glance at the diagram, see a 11 code, and know that I'm definitely after a piece of metalwork here.

Of course, if there's no way to do this at all then I'll definitely have to rethink the design!

 

[plog]

Dissassociate how you present data from how you store data. Those are two different things. In tables, you store each discrete piece of data seperately. Your PartRefID (e.g. 11-0021) contains 2 pieces of data, therefore it needs to be stored in 2 different fields:

PartType - this would contain the first 2 digits and link to another table to turn that number into words: 11 = 'Metal"

PartNumber - this would contain the data after the dash.

You stated the purpose of this is to have a unique identifier for every part. An autonumber accomplishes this for you. You should use an autonumber for the PartNumber field.

When you want to present data, you make a query and include a field like so:

PartReference: PartType & "-" & PartNumber

That combines those 2 fields into one field that you can then present to people.

 

[MarkK]

In table design you would never make a MalePeople table and a FemalePeople table. Rather, you would create a Person table with a field called Gender.

Similarly in table design you would never make a HardwareParts table and MetalworkParts table. Rather, you would create a Parts table with a field called PartType.

hth
Mark