Results 1 to 6 of 6
  1. #1
    freddawson is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    5

    Form Recommendations for a Novice

    I apologize in advance for mangling terms and confusing phrases. I am new to Access, and have had a relatively large scale database redesign project dropped in my lap.

    We currently have an Accessdatabase that lists our roughly 3500 sales contacts. We already have a pretty nice form for all of their contact info, etc. I have already added a search form that allows the user to click a "Search" button, and have a dialogue come up. The user can then choose a Field to search, enter their string, and either click or Search, or search another field (I currently have 3 possible). The search form then closes and displays the filtered results in the main form.

    What we would like to be able to do is have a streamlined method of labeling and searching for the products and services that each customer offers. I have a list of about 20 possible products/services, and most companies will offer more than one from the list.

    I have looked at list boxes, radio buttons, etc. and would like a more informed opinion about what would be the best method. Also, I would like to make it an option in my Search form.



    Thanks in advance for any help or advice.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You want to input customer identifier and show all related products/services? Initial thought is to set up unbound multi-column combobox of customer info (ID, Name) as input for search criteria.
    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
    freddawson is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    5
    Thanks for the response. The database that is in place is pretty standard contact info for each company: Name, Title, Address, Email, etc., and is pretty thoroughly filled out.

    The products and services we want to identify are a very simplet list. They don't exist in any form in the database yet. This is something we want to add to the current structure.

    We are a company that sells used steel mill equipment, and we want to identify the different products our contacts manufacture, so we can know what types of offers to send them.

    For instance, the first 5 items on the products list are:
    bar
    beam
    billet
    bloom
    cold coil

    It would be very possible for one manufacturer to make several of those. We are looking to identify what products, and then be able to have it be a searchable field somehow.

    For instance, we already have a data field for the individual's position in the company. So a reasonable expectation of the usage would be to be able to search for all of the Maintenance people of facilities that maufacture bars and beams.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not sure what you are asking for. Do you need guidance on designing data structure?
    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.

  5. #5
    freddawson is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2012
    Posts
    5
    I posted this issue on several different sites, and I've since been informed that my issue is not really with my form, but with my data manipulation. That being said, I still have no idea how to move forward. I guess the reason I asked the question on the Forms forum is because my design basis has been from a visual standpoint.

    Based on the articles I was pointed to about data normalization, it seems like I'll need to add a table for the products/services. Please tell me if this seems like a functional idea:

    Our current database is just one giant table of contact info. It contains the headings you'd expect: name, company, address etc, and 3500 entries. Then we have one form to display all the info for each contact, and allow the user to search it.

    Based on what I've read, I think I need to add a field to the CustomerTable with an identifier, mayber just a CustomerID, numbered 1-3500. Then maybe create a second table for Products/Services. What I was thinking of doing was duplicating that CustomerID number, and then having columns for each product/service. Then for each customer, it would be a Yes/No statement of whether they provided it.

    Am I at least heading on the right track?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not quite. Multiple similar columns (the products) with Yes/No value are often an indication data is not properly normalized.

    First you need to clarify the relationship type of the entities customers and products.

    Many-to-Many: Each customer can have more than one product and each product can have more than one customer? Requires junction table to associate the entities. Table structure would require two foreign key fields, other fields optional: CustomerID, ProductID

    One-to-Many: Each customer can have many products but each product associates with only one customer? Have customer ID field in Products table.

    One-to-One: Each customer has one product and each product has one customer? This can be one giant table.

    Review this thread http://forums.aspfree.com/microsoft-...es-208217.html
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Design recommendations
    By billgyrotech in forum Database Design
    Replies: 1
    Last Post: 08-11-2011, 06:24 PM
  2. Form Combo Box assistance for Novice
    By piper in forum Forms
    Replies: 1
    Last Post: 04-22-2011, 12:53 AM
  3. Very Novice User needs HELP with Form
    By Insanity Queen in forum Forms
    Replies: 1
    Last Post: 12-02-2010, 01:20 AM
  4. Any recommendations for a manual?
    By timmyjohnson in forum Access
    Replies: 0
    Last Post: 04-18-2010, 05:30 PM
  5. Replies: 1
    Last Post: 02-27-2006, 07:46 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