We all have a thirst for wonder. It's a deeply human quality... There's wonder and awe enough in the real world. Nature's a lot better at inventing wonders than we are.
This will be lengthy but I will try to spell out what i have done and accomplished and where I need help. Created a DB for ownership to vehicles and tracking of repairs so a little history there. 1. I have created a table for the owners. 2. Created a table for the vehicles. 3. Created a table for the purchase information. 4. Created a table for the repair's. And while inputting data in for the vendors(Mechanics and Garages) I realized a lot of these are the same and I am excessively having to be redundant in putting the information in (so I know there are errors probably). The fields I have created are name, address, city, state, zip, phone. I then decided to make a table with all the vendors info in it. Is there a way and if so I want to put all that info in that table into all those fields by just one entry( i.e. just maybe put a vendor number in and it populates all those fields automatically. I am very new to Access. THANKS for any help.
Kevin Yip
@Reply 3 years ago
First you create a vendor table with vendor ID, vendor name, address, etc. Then, when you put the vendor into the other tables, you DON'T populate those fields. You just put in the vendor ID. The vendor ID is all the info the other tables need about that vendor. An Access database needs to be normalized, meaning every info should appear in only one place. Therefore, vendor name, address, phone, etc. should all only appear in the vendor table, and nowhere else. Only the vendor ID gets to appear in multiple tables, as a "foreign key."
Lee ShastidOP
@Reply 3 years ago
Kevin
First of all thank you for all your help. I understand what you are saying above. I have added a vendor to the vendor table for trial. I added a repair to the repair table. Now do i just create a relationship between these two tables? I have made a 1 to many but when i go to the repair table and put the vendor number in it does not fill in the other fields with the address and so on.
Lee ShastidOP
@Reply 3 years ago
Kevin Yip
@Reply 3 years ago
You don't need to copy vendor addresses from one table to another because that would not be a normalized setup, which you always want to strive for. Every piece of data should be in one place only. See the picture below as an example. These tables are from my old job. Note that the order table only has customer IDs, but not customer addresses. Only the customer table has addresses. If you want to show addresses in your forms and reports, you can set up fields specifically for displaying such info. The picture below shows my order entry form where I enter only the customer ID. The customer address shown below it is for display purpose only, and is not actually stored in the order table. The address is retrieved from the customer table via either the DLookup() function or the combo box's Column property.
Kevin Yip
@Reply 3 years ago
Lee ShastidOP
@Reply 3 years ago
Thank you. Then apparently my whole DB is wrong then. Maybe I shouldn't try this any longer. There is something I am not understanding from the word GO. I appreciate all you r help. So I guess what I am having a hard time understanding is this. I having trouble understanding . So I guess I just need to create the vendor table as is and then in the repair table just put a vendor number to designate the repair facility and delete all the add info. So when I am in the repair table how do I get the vendor number without having to go to the vendor table and get it?
Lee ShastidOP
@Reply 3 years ago
Thank you to all who helped. I think I may have been overthinking it. I THINK I have found out how to do thing now.Kevin Yip you have been very good and responsive. Thank you
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Visitor Forum.
