Hello everyone,
First of all, I am very new to Access and this is my first time working in it. I currently have a database in Excel that I would like to implement in Access.
I am trying to create a customer database that consists of 4 different tables:
- Customer Contact Information
- Customer Profile Form
- Customer Order Form
- Customer Order Content
I want my primary key, I believe that is the correct term, to be the Customer ID. I would like this to be a calculated field in the "Customer Contact Information" table and it should be manually inputted into the other tables. The "Customer Contact Information"
table and the "Customer Profile Form" should have the same exact number of rows since each customer will have only 1 profile. If possible, I would like the Customer ID field to just copy over to the "Customer Profile Form".
The "Customer Order Form" and "Customer Order Content" will have varying rows since each customer could place multiple orders or have multiple items per order. I would like a unique Order Number to be generated in the "Customer Order Form" and the Customer
ID, which would be manually inputted, would still be the primary key that ties it back to the "Customer Contact Information" table. Then, in the "Customer Order Content" I would like the Customer ID and Order Number to be manually inputted as well. The Order
Number would l link back to the "Customer Order Form" and the Customer ID would still be the primary key.
My goal is to create a form on top of all of this so when customer data needs to be inputted, Customer ID is simply automatically generated and pastes to the appropriate fields in the other tables. The user will ultimately be able to click "New Customer"
and then begin inputting all the fields for the "Customer Contact Information", "Customer Profile Form", and "Customer Order Form". Then, the user will have to come back later to input the "Customer Order Content" since everything is purchased based on their
preferences. This is where it gets confusing for me since I have to keep all the tables related based on Customer ID, but 2 of the tables will have different numbers of rows.
The problem with the current system is that it gets difficult to share the database and input the data with a few users since there are so many columns and no real interface.
Any help in setting up this database in Access would be greatly appreciated or if anyone has any other suggestions.
Thank you for taking the time to read this.