Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22

    Need to attach one Title Record to up to Four Different Customers

    Hello all,

    I am VERY new to Access, and I am using version 2010. I only know the basics, and I have no experience with code, sql, macros, etc....I hope you all can help me. Please forgive me if I use incorrect terminology.

    I have a customer table with basic contact info (first name, last name, address, email, phone, etc..)

    I also have a title transfer table with info such as (transfer date, purchase price, vin number, etc..)

    I need to be able to attach up to four customer records to each title transfer. (A customer can have many title transfer records OR none at all. EACH title transfer MUST have at least 2 customer records associated but can have up to 4 customers associated...I'm assuming this is a many to many relationship??).

    A title transfer will always consist of an OWNER and a SELLER, and sometimes it will have a CO-OWNER and/or CO-SELLER......all of which are customers that should be in the customer table.

    How do I go about doing this so I don't have to enter customer records twice (once in the customer table, then again in the title transfer table)?

    I would like the end result to be....when I click on a customer record, I will be able to see the title transfers they are associated with, and if they were the buyer/seller, etc..

    PLEASE HELP!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Save only the CustomerID into the title transfer table. Can use a combobox to display a list of customers. In case you are unfamiliar with multi-column combobox, review http://www.datapigtechnologies.com/f...combobox3.html

    Form options:

    1. one form with two comboboxes for selecting customer and title

    2. main form bound to Titles and subform bound to TitleTransfer with combobox to select customer

    3. main form bound to Customers and subform bound to TitleTransfer with combobox to select title
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    Thank you. I am familiar with combo boxes, but I'm still a little confused on your form option instructions. When you say "titles" are you referring to my vehicle titles, or are you referring to the customer titles such as owner, seller, etc.....?

    Here is what I want to do.

    1. open my "Title Transfer" FORM
    2. Enter the details of the Owner
    3. Enter the details of the Co-Owner (if applicable)
    4. Enter the details of the Seller
    5. Enter the details of Co-Seller (if applicable)
    6. Enter the date of the title transfer took place
    7. Enter additional information about the transfer (the vin number, license plate number, etc...)
    8. Save Record.

    After doing that......I want all four of the names I entered to automatically be in the customer table.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a data model for your database design? (tables and relationships) If so can you post it as a jpg?
    From your description, the Customer table is playing up to 4 roles in your set up:
    Seller, CoSeller, Owner,CoOwner
    I'm not sure that is the best set up, but you might consider a slightly modified design.

    Can you have Customers who are not Owners or Sellers? If you do, then you might consider the
    supertype/subtype model.

    Code:
    Customers (supertype)
    CustomerID PK
    
    where
    Owner  CoOwner  Seller  and CoSeller are potential subtypes.
    WE really need to know more about your business rules and processes.

  5. #5
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    the way I initially had it set up was:

    CustomerTable
    CustomerID - Autonumber
    CustomerFirstName - text
    Etc...

    OwnerTable
    OwnerID - AutoNumber
    OwnerFirstName - Text
    OwnerLastName-Text

    CoOwnerTable/SellerTable/CoSellerTable - all set up exactly the same as the OwnerTable

    TitleTransferTable
    TitleTransferID - AutoNumber
    OwnerID - Number
    CoOwnerID - Number
    SellerID - Number
    CoSellerID - Number
    TitleDate - Date/Time
    VINNumber - text
    (and a few more text fields)

    In my relationships window, I drug the OwnerID from the OwnerTable and dropped it on the "OwnerID" in the title transfer table. I set referential integrity, and I made the join so that both are equal.

    I then made a form based off of my Title Transfer Table. I brought in the owner/seller ids from the other tables

    Everything was working fine.

    Problem is, I DO have customers who are not owners or sellers. I offer other services at my business (notary, graphic design, administrative)

    I had a separate table for those customers but I don't want it set up like that. I only want ONE customer list. If I want to see how many times a customer has been to my office, and what services they received, I would have to check all of these tables (customer, owner, seller, etc...).

    I also tried just using the customerid four times in the title transfer form, but I'm having issues with that as well.

  6. #6
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    I forgot to mention that the relationship I created between the ownertable and the titletransfer table is the same thing I did with the other tables (coowner, seller, coseller)

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is this business?

    Why not just a table of customers (seller, owner, whatever). Surely a person could be a seller in one transaction and an owner in another? Why relegate them to separate tables?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    that is exactly what I want, just a table of customers......I just can't figure out how to attach those customers to the title transfer record correctly.

    it is primarily a notary business. I do basic notary, as well as title transfers, vehicle registrations, etc.... .....I also do copy, fax, graphic design and administrative services. my customers can have many of these services. I pretty much figured out how to relate them to the other services. the title transfers are giving me a hard time because I need to have multiple relationships between 2 tables (the customer table and title transfer table).

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is basic notary?
    In overview, you have a Company that offers Services to Customers. You have to flesh this out and describe the business and processes in order to see WHAT things are key to your business and how they relate.
    Code:
    Services include:
        Title Transfers which in detail means....
        vehicle registrations which in detail means
        etc????
        copy  what details/processes are involved?
        fax..................
        graphic design................
        administrative services.....
    
    What information do you need in your business for a Customer? 
    What additional info is required for an Owner, a coOwner, a Seller, a coSeller....? 
    What about vehicles, or Titles what do you need in your business about these??

  10. #10
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    "Basic Notary" means a customer comes in with a will, power of attorney, or some other legal document that needs a notary seal, and I notarize it for them.
    In the database, I would need to track the customer first and last name, address, phone, email, date of the notarization, the type of notary act I performed (affidavit, acknowledgement, certificate, oath, verification), and notes/comments

    "Title Transfer" means I have AT LEAST two customers in front of me, four AT MOST. One customer is selling a vehicle to the other customer. In the database, I need to have contact info for both the buyer and seller of the car (and possibly a cobuyer and coseller if it applies) (the same type of contact info as above; name, address, etc..)...the date I did the title transfer, the vin number of the vehicle transferred, the plate number I issued.....I need to be able to pull out WHO the buyer/cobuyer was and WHO the seller/co seller was. When it's all said and done, I need to be able to see info these different ways:

    When looking up title transfers:

    "John Doe and Jane Doe purchased a 2014 Ford Focus from Donald Duck on April 5, 2014 and plate number XXX-1111 was issued"

    When looking up customer records:

    April 5, 2014 - John Doe was in the office to buy a car
    April 7, 2014 - John Doe was in the office to get a will notarized

    April 5, 2014 - Donald Duck was in the office to sell a car

    April 5, 2014 - Jane Doe was in the office to purchase a car
    April 20, 2014 - Jane Doe was in the office to get some copies made
    April 25, 2014 - Jane Doe was in the office to get some business cards designed

    Does that make sense? I need to be able to track my title transfers, while also tracking all of my customers visits to the office.

    I really appreciate you all taking the time out to help me, and again, I apologize if I'm not making myself clear. I'm not fluent in access lingo just yet :-)

  11. #11
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    As far as title transfers, ONE customer can have many title transfer records. Title transfer records can have up to 4 customers for each one. Never any less than 2, never any more than 4.

    Examples
    Donald and Daisy Duck are selling their vehicle to Fred and Wilma Flinstone. (Fred is the owner, Wilma is the coowner, Donald is the seller, Daisy is the coseller)

    One month later: Fred and Wilma decide to sell the car to Mickey Mouse. (Fred is the seller, Wilma is the coseller, Mickey is the owner...there is no coowner)

    One Week Later: Daisy Duck wants to sell another car that she has to Bugs bunny. (Daisy is the seller, Bugs is the owner. there is no coowner or coseller)

    In the meantime, Fred Flinstone has come in to get several documents notarized, and Bugs Bunny has come in to get a flyer designed.

    Does this give you all a better idea of what I need to track, or did I confuse you even more?! lol

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You provide notary service for a fee? Seems to me the notary info is incidental to tracking the finance info. I go to my bank for notary service (free). The individual is the notary, not the bank. The individual has to keep a log book of notarizations. You want to have a digital representation of the log book as well as track the related financial information for the service? For each document notarized, is payment made by only one party?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    yes, I would like to create a "digital" log of my notarizations....but that part is easy. I have that figured out (at least I think I do). I'm only struggling with the title transfers.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is title transfer the only case with multiple names?

    I just noticed in your post "the plate number I issued" - why would you issue a plate number?

    If title transfer is so different from any other notarization, I am starting to think will need another child table for the title-specific info.

    Ever notarize any real estate documents, like a quit claim deed? How much detail would you have to document?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    NotaryEtc is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2014
    Posts
    22
    yes, title transfers are the only service that will be linked to more than one customer. I notarize deeds, but I do not actually transfer them, so I only need to track the same info as I would any other notary. I need to track plate numbers because I issue license plates. when a person buys a vehicle from someone, they are required to get new plates and registration for that car.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can't attach to email
    By newtoAccess in forum Access
    Replies: 10
    Last Post: 12-03-2010, 02:10 PM
  2. Attach Email to Form
    By Huddle in forum Access
    Replies: 2
    Last Post: 07-22-2010, 12:39 PM
  3. Replies: 1
    Last Post: 05-05-2010, 10:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums