The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Customer Database - Multiple codes for each record

This should be a simple fix.

Here is the breakdown for my problem. I need a customer db with all the usual info. I need to add a field for storing a code (example: PO for police officer TE for teacher) some of the customers are both PO (police officers) and CG (community groups). I need a fix so that each customer can have multiple codes.

I could add each field to the customer record and have a yes/no field.

I could have another table with a customerID and a CustomerCodeID that links to a table with CustomerCode Information.

I could allow duplicate records and remove duplicates with a query when I need to do a mailing.

I got the two primary keys now and finally set my subform on to be in spreadsheet view (a choice in the form wizard) and that solves the problem. Now the user can add an unlimited number of codes to one customer.

Its amazing that I was working on this off and on last week and knew it should be easy but couldn't get the right answer until 5 minutes AFTER I post a new question.

...Next and Final Step.
I have 5 databases of customer contacts from past events. I need to combine them all into my new database. 4 access databases and 1 excel spreadsheet. I have tried "Get External Data">>>"Import" but that only brings the whole table in. I want to bring select columns of each table into my customer table. Any Ideas of how this is can be done?

The only thing I can think of is to write code that will read in the database and loop through it writing new records with the selected fields. I can do this, but is there an easier way?

I found a company called 4top at: http://www.4tops.com that has a software package that will allow you to import excel spreadsheets into an Access DB. They have a free trial that will allow you to move 4 records at a time. If you have thousands of records to transfer, pay the $99 and you can do it all at once. I also had a comma delimited text file that I imported into Excel and then used 4top's software to import it into Access. The same with the Access tables, I exported them to Excel and imported them again to Access using 4top. It lets you pick which fields to use and which to skip. It also creates an error log for any records that had to be truncated.

I have located an article on the web that says MS Access can be shared accross a LAN. I will have a maximum of 5 people entering new customers at any one time. Should I be concerned? The article says "If you wish to use the multi-user functionality of MS Access, you must split your database!"