I know a lot about working with existing Access databases, but I’ve never been through a class (well a 2-day class 15 years ago doesn’t count) so I need suggestions on “normalizing” my data. I think I'm just not seeing the relationships because all I can see is the original table. :banghead:
Right now I’ve got a spreadsheet that is to be the basis for a new database. The problem is there is no unique identifier for each record and almost any field can be null in any given record. Fields and explanations:
OEM (unless we acquire another company this is currently limited to 3 choices, typically will not be null)
Product Number (currently a list of 1900 unique records, want as a lookup in form but need to be able to add new from form if not already in table, this should never be null)
Product Description (1 to 1 with product number, want this to auto populate in form when product number is selected, and need ability to add with product number if it is not already in table, this should never be null)
Lot Number (1 product can have multiple lot numbers affected – I think this should be a data entry field this can be null or can be a word like “multiple” or “all”)
Application (one application can relate to multiple products, cases and notifications, but each product can have only one application - currently a list of 100 or so, want as lookup in form but need to be able to add new from form if not already in table – also can be null)
Qty Complaints (a number that will be entered, should never be null)
Complaint number (each product can have more than one complaint, and one complaint can apply to more than one product, also can be null)
Problem (memo field- long statements, shouldn’t be null)
Review Date (date field will be entered, can be null)
Date Missing Explanation (reason there was no review, if date field is blank- can be null but should be required if review date is null)
Action taken/Conclusion (memo field- long statements, should never be null)
Notification ID (each product can have more than one notification or complaint, each complaint can apply to more than one product or notification, each notification can apply to more than one product or complaint, also can be null)
Notification Start (date field will be entered, can be null)
Notification Completed (date field will be entered, can be null)
Qty Products Packed (number field will be entered, can be null)
Qty Products Sold (number field will be entered, can be null)
Qty Customers Notified (number field will be entered, can be null)
Qty Distributors Notified (number field will be entered, can be null)
Associate (there are currently about 15 associates, would like to be able to choose from list in form, but need to be able to add easily if/when an associate is replaced or a new one is hired- any one associate can be responsible for any number of different products, complaints and notifications, and more than one associate can be responsible for the same product, complaint and notification, can be null)
Notes (memo field will be entered, should not be null)
DateAdded (auto date when record is added)
DateEdited (auto date when record is updated)
So I’ve got five tables-
OEM – OEM field and autonumber primary key
Products – 3 fields; product number, description and autonumber primary key
Application – Application field and autonumber primary key
Associate – Associate field and autonumber primary key
Notification Records – autonumber primary key and all fields (including those with their own tables because I don’t know how to separate and relate)
Where do I go from here?!?!
Right now I’ve got a spreadsheet that is to be the basis for a new database. The problem is there is no unique identifier for each record and almost any field can be null in any given record. Fields and explanations:
OEM (unless we acquire another company this is currently limited to 3 choices, typically will not be null)
Product Number (currently a list of 1900 unique records, want as a lookup in form but need to be able to add new from form if not already in table, this should never be null)
Product Description (1 to 1 with product number, want this to auto populate in form when product number is selected, and need ability to add with product number if it is not already in table, this should never be null)
Lot Number (1 product can have multiple lot numbers affected – I think this should be a data entry field this can be null or can be a word like “multiple” or “all”)
Application (one application can relate to multiple products, cases and notifications, but each product can have only one application - currently a list of 100 or so, want as lookup in form but need to be able to add new from form if not already in table – also can be null)
Qty Complaints (a number that will be entered, should never be null)
Complaint number (each product can have more than one complaint, and one complaint can apply to more than one product, also can be null)
Problem (memo field- long statements, shouldn’t be null)
Review Date (date field will be entered, can be null)
Date Missing Explanation (reason there was no review, if date field is blank- can be null but should be required if review date is null)
Action taken/Conclusion (memo field- long statements, should never be null)
Notification ID (each product can have more than one notification or complaint, each complaint can apply to more than one product or notification, each notification can apply to more than one product or complaint, also can be null)
Notification Start (date field will be entered, can be null)
Notification Completed (date field will be entered, can be null)
Qty Products Packed (number field will be entered, can be null)
Qty Products Sold (number field will be entered, can be null)
Qty Customers Notified (number field will be entered, can be null)
Qty Distributors Notified (number field will be entered, can be null)
Associate (there are currently about 15 associates, would like to be able to choose from list in form, but need to be able to add easily if/when an associate is replaced or a new one is hired- any one associate can be responsible for any number of different products, complaints and notifications, and more than one associate can be responsible for the same product, complaint and notification, can be null)
Notes (memo field will be entered, should not be null)
DateAdded (auto date when record is added)
DateEdited (auto date when record is updated)
So I’ve got five tables-
OEM – OEM field and autonumber primary key
Products – 3 fields; product number, description and autonumber primary key
Application – Application field and autonumber primary key
Associate – Associate field and autonumber primary key
Notification Records – autonumber primary key and all fields (including those with their own tables because I don’t know how to separate and relate)
Where do I go from here?!?!
