Query design help....Create Table?

[ZaphodBBB]

Hi all

I have a database with the following tables:
tblEquipment
tblSuppliers
tblManufacturers
tblEquipment_Type
tblModels


Each table has an Auto increment number field that is the ID for that table:

i.e. tblManufacturers has a Field called Manufacturer_ID
tblSuppliers has a Field called Supplier_ID

At present the only place where all these ID Fields 'come together' is in the table tblEquipment, as that holds every instance of every item of equipment.

However I want make up a new table called Identifiers that will bring all the ID Fields from each of the tables together..i.e. the new table will have the following fields:

Model_ID
Supplier_ID
Manufacturer_ID
Equipment_Type_ID

and I need to have it based on the Model_ID field of the Models table...

I tried creating the new table and added all the Model_ID's into it o.k. but then I just couldn't get all the related fields from the other tables to write to the table. I tried using INSERT INTO and I was hoping to do this one column at a time but it just kept failing all the time. I though it would be dead easy (isn't that always the way when you get stumped totally?)

Anyway any advise appreciated.

Cheers
Mike

[NeoPa]

What would Identifiers mean? I can't see how this could make sense Mike.

Would any of these identifiers be linked?

[ZaphodBBB]

Hi NeoPa

The reason for the identifiers is that at the moment the only 'link' between the various tables is via tblEquipment.

So lets say you wish to add a new Model to the database, you can do that but it wont be linked to say a supplier, Manufacturer, Equipment Type, unless you have added at least one item of that model to the Equipment Table.

Also when adding a new Item of Equipment to the database at present you have to either select the various items such as Equipment Type, Supplier etc from ComboBoxes (thus relying on memory of who supplies what etc). When adding a new item of Equipment it is optional to have a supplier, as this may well not be known (suppliers change and there is a lot of old equipment).

Now that there is a fair amount of data in the tables, many suppliers, Equipment types, Manufacturers, I'm modifying the database to automatically provide the Supplier Names, Manufacturers etc if they are known already... but this requires an already existing item of that equipment be in existance to hold all the 'links' to the other tables.

The purpose of the Identifiers table will be to link all the various Model_ID's to their various Manufacturer_ID, Supplier_ID, Equipment_Type_ID fields in the relevant tables Without having to have an actual item of that model currently in the database.

As far as relationships go the tblIdentifiers Fields will have One to One relationships with the Equivelent _ID fields in the other tables.. i.e.

tblIdentifiers.Model_ID will link with tblModels.Model_ID
tblIdentifiers.Supplier_ID will link with tblSuppliers.Supplier_ID
and so on.

Its the only way I can see to be able to link all the various identifying Fields from the various tables where I do not actually have an item of that type entered (as yet) into the database.

PHEW hope it sort of makes sense?

Thanks
Mike

[NeoPa]

I don't mean this as criticism, as you've clearly given it a good shot, but you don't really answer either of my questions Mike.

The reason they were posed is to help you come to a realisation of why what you say is not really going to be helpful. Give me a few minutes and I'll see if I can come up with something that may be a bit clearer to understand.

[NeoPa]

Mike, I'm sorry to say I think your fundamental reasoning is flawed.

Check out (Normalisation and Table structures) for a more complete explanation and things to consider, but for your current situation let me see if I can give a bit of direct guidance.

Let me start off by saying that I think tblIdentifiers is a complete non-starter (sorry). I see no way that it can help. In fact, I can see no way that you can populate it in a way that would be helpful.

What you possibly need to consider is a table structure that reflects the hierarchy it's supposed to match. I'm guessing that the Equipment table has a many to one link to the Model table, which in turn has a separate many to one link to the Equipment Type table. The Model table will have separate links also to both the Manufacture and Supplier tables. Whether or not these are simple many to one links depends on your industry, so I cannot tell you. Let's assume for simplicity that they both are, and we can deal with the possibility that they're not later if necessary.

It seems to me now, that as long as these reference tables (Model; Equipment Type; Manufacturer & Supplier) are properly populated, there should be no issues with the data you enter into tblEquipment. It may be that you need a facility whereby entry of new values (not already included in the database) into tblEquipment automatically triggers off the opportunity to add the value into the relevant reference table on the fly. If so, then that is fine.

This is how things should be fitting together in your database as far as I can see. Reporting on all values is then perfectly possible and correct.

[ZaphodBBB]

Hi Neopa

Yes your dead right as far as the relationships go. The Table tblEquipment has many to one relationships with tblSupplier, tblManufacturers etc.

The problem has been that :

The database is used out in the field, so when new items are being added it may well be that whoever happens to be entering new gear will not know the supplier. Usually they will know the Manufacturer (as its usuallly prominently displayed on the equipment), they'll know what Type of item the new Equipment is a class of etc.

Because of this the database was made with a lot of compulsory fields, but Supplier is not one of them.

So they save an item of new equipment...lets say the model is 200WS (philips monitor) but they do not know the supplier. The new item gets saved with its Serial Number, Equipment Number etc, but no supplier.

Then next time someone comes to enter another item of the same equipment, they select the 200WS from a comboBox and (I guess) because there was no Supplier saved with the original item of equipment, the recordset that is opened or attempted to open to populate the other fields on the Add Equipment form ends up with rs.NoMatch = True, so a Message box pops up saying that there are no details for that item. Even though there is in reality everything but the Supplier details. So the user cannot enter another Model of the same name so it effectively means that another item of the same Equipment cannot be entered until the sup[plier details are known.

I have started working on a work around but it is (for me) long and complicated specially as databases are not my main job..just something I occasionally enjoy doing, if it makes my life easier.

Thanks again
Mike

[NeoPa]

@ZaphodBBB
I'm not sure you read my post carefully enough Mike.

I was suggesting the links to tblSupplier & tblManufacturers be from tblModel (rather than tblEquipment).

It's about separating the definition, from the data.

tblModel should store all the definition data (via its links to other tables etc), and tblEquipment should simply store the actual data and a simple link to the definition data via tblModel.

Does that make sense?

[ZaphodBBB]

Hi NeoPa

O.k. thanks for that. It will be very interesting to try and get column data from the other tables and add it to the Models table, rearrange all queries etc!!

I have no idea how to take existing data from a column of one table and add it to another column of another. Generally all I ever do is add rows (records). Will be even more interesting I guess as specially in the tblSuppliers there will be a fair number of blank entries.

Any basic hints of what to look at as a method goes for copying colums from one table to another?

Thanks again
Mike

[NeoPa]

Only to remember that you are copying rows (records) not columns (fields).

Actually I'm struggling to grasp actually where you're at at the moment. Possibly you can attach a Compact / Repaired and zipped version of your database for me to take a look at.