Im creating my first Access db from scratch and i am having some normalization and table relationship issues. i learned how to use Access backwards in that i started by creating queries and forms and what not from existing tables through ODBC, and not by designing my own db's

anyway, i've got an Access 07 textbook hear that i am using, but i figured you guys would be much more help.

ive attached an image of what i have so far. as you can see it is a very simple database.

the user just needs a form so he can add additional records to the all records table as they come in.

my problem is data duplication and designing the proper tables from the info in all records, and getting them normalized.

The gpdata_store table is through odbc so no need to worry about that.

any help would be great.

I just realized you all probably need a lot more info here.

Attached is sample data from table 'All Records'

The inspection_id is an autonumber PK, because each entry to this table will have a unique combination of 'Store' and 'Inspection Date'. No single store will be inspected twice in one day, but a store can be inspected more than once over the course of the year.

The table 'GPDATA_STORE' contains all the info I need on Stores, so do I need a separate 'Stores' table? I only want to directly store (poor word choice) the Stores that have been inspected in the actual database, not all of the stores within the GPDATA_STORE table.

Not sure what else might help. Please ask away.

i realize im sort of talking to myself here, but hopefully someone will chime in.

a big problem ive discovered is that i need to put FK's in my tables to related them rather than using the PK from the main table as a PK in my other tables as that forces 1-1 relationships...

i think.

ok, ive made some progress since this morning.

ive attached my new Tables and Relationships.

im wondering specifically what to do with tblAll Records. Should i have the primary key of each table listed there as an FK in that table, and then make relationships back to each individual table? Or, since they are all tied together somehow, do I not need to do this?

Is there anybody out there? Do I need to post in a different forum?

maybe, someone could tell me a better way to phrase my questions or inform what it is about my post that is scaring you away :confused:

either way, i will continue this little conversation with myself because it seems to be motivating.

here is a most recent view of my Relationships.. i thikn it looks pretty good, but i really dont know.

One question in your table "tblPassFail" could you have more than one error per record? If so you might want to consider creating another table for errors with a link to to the pass fail table.

OK, I just noticed one problem between your tables "tblAllrecords" and "tblPassFail" I think you want to link the Identification IDs together and remove the passfailID from your tblAllrecords.... Also you have a space in the table "tblAll Records". I suggest removing that space.

Edit: You have excess IDs all over the place..... Define your relationships into one-to-many bits.... Then put the ID of the "one" side into the "many" side as a foreign key and relate the tables that way.... remove the fkID reference of the "many" side table from the "one" side table, it doesn't belong.

Thank you Kryst51!!!!!!!!!!!
sorry, just a little excited to get a reply. i know, patience is a virtue.


Yes, i should have noticed that one, and i think you are right.

as far as the 'Errors' field in tblPassFail - it is just a number from 0-xxx so there will be only one entry per record.

Edit: Ok, too many ID's. Gotcha. I will see what i can do and repost the new relationhips.

Thank you Kryst51!!!!!!!!!!!
sorry, just a little excited to get a reply. i know, patience is a virtue.


Yes, i should have noticed that one, and i think you are right.

as far as the 'Errors' field in tblPassFail - it is just a number from 0-xxx so there will be only one entry per record.

OK to the errors, that's good.

No problem about the excitement. More than likely people think that you already have help. :) Correct the links and relationships and repost.

You have excess IDs all over the place..... Define your relationships into one-to-many bits.... Then put the ID of the "one" side into the "many" side as a foreign key and relate the tables that way.... remove the fkID reference of the "many" side table from the "one" side table, it doesn't belong.


When you say I have too many ID's, are you referring to the Inspection_ID's in tblFines and tblChecks?

With the other ID's, i thought i was doing just that by making the "one" side PK a FK in the "many" side.. For example, "Fine_ID" as a FK in tblPass/Fail.

I was wondering if i needed "Inspection_ID" in each of the tables so as to identify each record. Or, should i create relationships between "tblAllRecords" and "tblFines" and "tblPass/Fail" using their PK's as FK's in tblAllRecords?

I was wondering if i needed "Inspection_ID" in each of the tables so as to identify each record. Or, should i create relationships between "tblAllRecords" and "tblFines" and "tblPass/Fail" using their PK's as FK's in tblAllRecords?

Begin by defining your one-to-many relationships.... If a relationship is between two tables and neither of those tables is the inspection table then you do not need the inspcetionID as you can find that using a query. It is linked through other tables.

Ok, Newly restructured relationships attached. and changed tblAll Records to tblAudit.

Hmm.... I am not sure that this is necessarily incorrect, but your tblStores and GPDATA_STORE seem to have the same pk and refer to the same thing. Is GPDATA_STORE a linked table? If so, You might consider giving tblStores its own pk, and putting the pk from GPDATA_STORE as an fk in tblStores. And link them that way.

Also I would remove the special character "#" from your table and field names... use "Num" or "No" or the like instead. Also, yoru field names should not have spaces. Read up on naming conventions.

I like to have no spaces or underscores but capitalize the first letter of each new word, and if I use a prefix then it is ALL lowercase.

for instance:

tblVendors

pkVendorID - Autonumber, pk
VendorName
fkAmountID - number
VendorNotesForAnythingElseYouNeedToStore

Is GPDATA_STORE a linked table? If so, You might consider giving tblStores its own pk, and putting the pk from GPDATA_STORE as an fk in tblStores. And link them that way.

Yes its linked through ODBC. I see what you mean, and i think that would make more sense than having it linked to "tblAudits". thanks again.

Yes its linked through ODBC. I see what you mean, and i think that would make more sense than having it linked to "tblAudits". thanks again.

You only want to link it to audits if that is the table the info is related to....IF the table is actually an extension of your ODBC, then that is really what you want to link to.... then your Audit table is linked to one or the other, either the ODBC or your extension table.

I meant to say that i think it makes sense to link the ODBC table GPDATA_STORE to "tblStores" because gpdata_store is where i get store information such as city state region, etc. at least that makes sense to me...

and as far as table and field names, yes i need to go through and clean those up a bit.

Relationships part 30:

How does this look?

Consider using the store number to drive things as opposed to the locationID....Isn't the store number more indicative of how a store is identified in real life? That is just a suggestion though.

Also, I notice that you have district already stored in your GPDATA_STORE table, AND you have it in your tblstores.... you don't need it both places... If any other of your fields in tblStores can be found in your ODBC table, you don't need them in two spots so remove it from tblStores

thanks.

Consider using the store number to drive things as opposed to the locationID
i would much prefer to do it this way, but i did not know if i could link GPDATA_STORE to tblStores if StoreNum is the PK in both, however i just made the change and Access allowed it so I suppose thats a good thing.

As for the Stores issue. The only reason i have GPDATA_STORES linked is to ensure that when the user enters a StoreNum into tblStores, for example, that it is an actual StoreNum as per the ODBC connected table. also, i wanted to have a localized recordset of only the stores that have had Audits performed on them.

If any other of your fields in tblStores can be found in your ODBC table, you don't need them in two spots so remove it from tblStores
yes, all of this can be found in the ODBC table.

Then you do not need your tblstores.... You can ALWAYS pull the data from your ODBC table.

Edit: I would use your ODBC table for the info that it has.... It's a great way to avoid having to enter info that you already have available. You can use the linked table in queries and forms.... You can also use your tblStores as a way to filter audited... like a junctin table.... with only the following

locationID - pk autonumber
Storenum - which is linked to the ODBC table
AuditedYesNo - yes/no field that can be filtered on in a query.

But if you only want it for reference, its your decision of course. :p

Great, thank you so much for the help. ill be sure to add to rep.

attached is my most recent (at least for today) relationship view.

Great, thank you so much for the help. ill be sure to add to rep.

attached is my most recent (at least for today) relationship view.

That looks fantastic! If you do want to filter for "Auditable" if there is no way to identify those in the ODBC table you can create a junction table that will do that for you.... Or you can use the tblStores if you didn't delete it completely an just use the autonumberID, and storenum linked. Then if the store number is in that table it means it is an auditable store. and you can use that table in queries.

Again, thank you manys!!

that is something i will most likely add as this db is being built.

Again, thank you manys!!

that is something i will most likely add as this db is being built.

You are very welcome! Good job with your design and good luck.