SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

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

    Matt

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you MattR.

    This is my design. How does it look?

    Attached JPG.

    Thanks

    Matt
    Attached Images Attached Images

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If customercode is unique then you can use that as the primary key in CustomerCodes and the foriegn key in CustomerCode-Something.

  5. #5
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Will do! Thank you for your help!

    Matt

  6. #6
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Attached Images Attached Images

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  11. #11
    SitePoint Addict matt_12511's Avatar
    Join Date
    Feb 2004
    Location
    Michigan
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    new question --> new thread

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •