Customer Database - Multiple codes for each record

[matt_12511]

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.

These are some of my ideas...what are yours?

Matt

[MattR]

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

If I'm reading this correctly, then this is the way to go.

[matt_12511]

Thank you MattR.

This is my design. How does it look?

Attached JPG.

Thanks

Matt

[MattR]

If customercode is unique then you can use that as the primary key in CustomerCodes and the foriegn key in CustomerCode-Something.

[matt_12511]

Will do! Thank you for your help!

Matt

[matt_12511]

Ok here is my new Relationships table. I am having difficult with Access (which is my only option here).
How do I make a simple insert statement in access?

I want a form that will allow a user to add a new customer and add the code Id as well. Since there can be multiple Id's how is it best to handle this?

This seems like something that would be done often enough, but I can't seem to find any tutorials about it. Anyone know a good Access tutorial site that might deal with this topic?

Thanks

Matt

ps. I also could not figure out the foriegn key thing in access, is there a problem with using the two primary keys in customercodeindex?

[r937]

new relationships look fine

yes, access supports composite primary keys

in Table Design view, highlight both columns before clicking on the little golden key thingie

[matt_12511]

Thanks for the help r937.

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?

Thanks all,

Matt

[r937]

4 access database --> use linked tables, and then you can use them all in queries

for the spreadsheet, i dunno, i always just import it and then drop the columns i don't need

[matt_12511]

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.

Thanks to all for the help!

Matt

[matt_12511]

Same Project - New Question

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!"

http://developersforhire.co.uk/resou...AccessDatabase

Thanks

Matt

[r937]

new question --> new thread