3 tables with varied info all about the same customer

[Andy]

I have imported from Excel Three tables into Access 2007 (table 1,2 and 3)One
table refer to customer contact, the second too products purchased by that
customer, and the third other odd information about the customer. Last
invoice date, last time ordered, last time paid etc. There were many columns’
that had useless information which was not need and I have deleted these
columns. However, when I tried to delete some columns I was warned “If you
delete this column you will delete indexes†so I didn’t delete them but I’d
like to.
I want to bring all the customer info together in one form with sub forms
so I can check on say products order, then in another sub form on the
financial side see when a customer last ordered and when last paid, etc.
The tables (certainly two of them) are linked by RecID how do I sort all the
records so they end up with the correct customer as they should?
What is the best way to do this? I’m fairly new to Access 2007 easy steps
would help.

Sorry its a bit long.....

Many thanks.

Andy

 

[ErezM via AccessMonster.com]

hello andy
when designing a database what you need to think is of having each table deal
in a single object (and it's properties) so a Customers table, could hold the
customer id, first name. last name, date of birth, first date of contact and
all other things that are defining the cusotmer by a single value (although
you may have more than 1 field like phone1 and phone2 or things like that)
oppose this to orders (or purchases) the customer made: one customer can have
0 to an infinite number of orders, so that fits in an orders (or purchases)
table that has (as a foreign key) the customer id as one of it's fields (and
order date, and sum paid and product ordered etc...)
that kind of relashionship is called one to many (one record in customers may
have 0 to many records in orders)

many beginner-programmers use the customr name as means to identify him in
the database. this is a bad practice and eventually will always have to
change in business applications as it might induce errors (people with the
same name, or misspelled name that produces nothing...)
so as a beginner even if you take this single advice and no other: always
have a primary key set to an AutoNumber - at least until you're familiar
enough to set it to other types of data

now for your quest: if you have a primary key in customers table (we'll call
it custID) and this field exists also in purchases table and in OtherStuff
table then yopu can reasily build a form with all customers table data
and then another,. seperate form with purchases table data, and yet another
one with OtherStuff data
and finally, open form1 in design view, and add two subforms to it. form2 and
form3
the wizard will suggest to link the subforms to the main form by the mutual
field it will find (and if you have a CustID field in all of them, the wizard
will find it and suggest the link automatically

and voila, youre on your way

 

[Andy]

Very many thanks. Still a bit confused but I'll work through your
recommendations and revert if needs be.


It seems I crossed posted sorry about won't happen again!
Andy..