Hello everyone!, and can I get your critiques? (1 Viewer)

Hello All again,

Ten years ago I created a somewhat half-decent custom database for my place of employment, and after some 9 or so years of not working with Access, I fooled myself into thinking that I could very simply create a database again. I had NO IDEA how big a fool I was!

This simple, easy database is a BIG nightmare.

I'm volunteering Saturdays at a local mechanic's shop just to get out of the house and to learn some new skills, and while I was sorting and organizing oil filters, I thought that it might be handy to have a database that would keep track of it all, and that it might give me a chance to play with Access again and re-learn my lost Access almost-skills.

It's surely child's play for you, which is why I'm asking if someone might have a few mins. to just look at it quickly and suggest what I'm doing right, or wrong, in the basic design and in the way I'm going about implementing the database functionality.

I have spent many days trying to get the darned thing to work... I DO NOT AT ALL care about "prettying" it up at this point in time, I know it looks like sh*te, but that's irrelevant, I'll work on that later, I just need figure out how to best make the darned thing work at this point in time. Later I will make a switchboard which will tie together all the forms.

The most important part is that I want to be able to go to a form, select a Auto Manufacturer, then select an Auto Model, from there select a Filter Manufacturer, and then select the corresponding Filter Number. By doing this, I enter into a table the filter numbers that correspond to certain auto models. Then ideally, one could go to the View form of said data, search a certain auto model, and get a list of all filter numbers that will be acceptable for use with that vehicle model. Why is this sooooooo difficult to accomplish?????

Am I imagining things, or have I read on many forums that one should not store the actual text in certain tables, but rather the corresponding table's ID number instead? I'm asking 'cause one of the tables, to store the information I've just outlined in the paragraph just above, stores the Auto Manufacturer ID, the Auto Model ID, the Filter Manufacturer ID, and the Filter Number, so the table is comprised of a auto-number ID, and the four above-mentioned IDs. Now when I want to see the text equivalents of the above IDs, I can't seem to figure out how to get that done.

Any suggestions very appreciated.

You are storing redundant data. Auto model should be unique. The manufacturer will be a field in the automodel table holding a foreign key to the auto manufacturers table.

Similarly the filters.

Once that is cleaned up, there should be a junction table with just AutoID and FIlterID fields to store all the auto/filter combinations instead of the four fields. I would not bother with an autonumber field but rather use a composite PK on those two fields. They will already need to be indexed to prevent duplicate combinations anyway so adding another index just wastes space and processing power.

Galaxiom said:

You are storing redundant data. Auto model should be unique. The manufacturer will be a field in the automodel table holding a foreign key to the auto manufacturers table.

Click to expand...

Thank you Galaxiom. I am not certain I understand, however, since it seems as though what you mention here is exactly what I've done. As I understand it:

* The fAMID_ID field in the tblAutoModels table is the foreign key field that links back to the auto manufacturers table.

* The fAModelName field in the tblAutoModels table is the one text field in this table that holds the name of the automobile model, and since I want to be able to uniquely identify each auto model later on...

* ... I had to create a fAModelAN autonumber field with which to uniquely identify any one particular automobile model name is this table.

Since I could have two or more auto models in the tblAutoModels table with the same duplicate number in the fAMID_ID field of said table, I cannot use this field as the unique identification number for any particular auto model in this table, so the fAModelAN field was the only way I could figure out how to do it.

Since I won't have any duplicate auto model names in this table (the fAModelName field is indexed to disallow duplicates), it would be nice to use each auto model name as the unique ID, but normally when you do any expressions/code/criteria, you're always comparing "if a = b", and then always get a "type mismatch" error if you're not comparing numbers. Maybe I just don't know how to use the string value of a combo box to do these comparisons?

I'm not certain how else to do it, perhaps there is a better way to accomplish what I'm after? Have I misinterpreted what you were suggesting above?

Galaxiom said:

...there should be a junction table with just AutoID and FIlterID fields to store all the auto/filter combinations instead of the four fields.

Click to expand...

Ok, so you're suggesting that in the composite table I've created, I should store only the auto model, and then only the filter number... alright, that makes perfect sense, from the model name I can query to find out the corresponding auto manufacturer, and similarly, from the filter number I could likewise find out who the filter manufacturer is. In the frmAutoModelsFiltersView form I tried to do just this, but I couldn't figure out how to get the text boxes to display the text values instead of the ID values.

Galaxiom said:

I would not bother with an autonumber field but rather use a composite PK on those two fields. They will already need to be indexed to prevent duplicate combinations anyway so adding another index just wastes space and processing power.

Click to expand...

I've looked at information about composite PKs (didn't even know they existed), so then all you're saying here is that I will composite PK the AutoModelID and FilterNumberID fields of the junction table to assure/prevent the duplication of a same auto model/filter combination, and also to create this one composite index instead of indexing these two fields separately. Ok, I'm fairly certain I can do that. Lemme know if I'm misunderstanding here again please.

Thanks again for your assistance.