Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Location
    Ontario, Canada
    Posts
    8

    Smile Unanswered: How can I fill list/combobox with multiple table fields?

    Hi all,
    I searched the history and didn't find this one, so here is a brain twister...at least it is twisting mine!

    I have a table for a service provider that may provide more than one service. The table is set up to hold the selection of up to 8 different services. Table fields are:

    CompName
    Location
    ProvServ1
    ProvServ2
    ProvServ3, etc.

    My form allows me to pick the company from a combobox, the company info fills in on the form and I have 8 comboboxes so that I can select a variety of services. All the comboboxes pull from a List table, and once a selection is made it saves it back to the main table under ProvServ1, ProvServ2, etc.

    We take in questionnaires about the company's and have the services rated, therefore I have a client form that accommodates all of the questions and option boxes to match the form, but I need to specify each time what particular service for that company they are rating.

    Therefore, I need the Client Form to have a listbox or combobox that we can select the service particular to that company using the fields originally selected and entered into the main table.

    So if my main table shows me the company name and a separate field for each service they provide, how do I combine those separate table fields into a list/combo box that will then be available on the Client Form?

    Company A could provide M, P & Q
    Company B could provide X, Y and Z

    When I am entering on the Client Form I will open my main form, pick the company of choice (A) and then open the Client Rating Form linking some of the data (company name, etc.) from the main form. My combo box should now be only supplying me the options of service M, P or Q.

    Have I missed something in how I set this up? I thought this way made sense....but now I am truly stuck!

    All help appreciated!! Thanks in advance.
    Sharon

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by novelideas
    Hi all,
    I searched the history and didn't find this one, so here is a brain twister...at least it is twisting mine!

    I have a table for a service provider that may provide more than one service. The table is set up to hold the selection of up to 8 different services. Table fields are:

    CompName
    Location
    ProvServ1
    ProvServ2
    ProvServ3, etc.
    Stop right there.

    You need to create another table to track services. I also see no primary key, you're not using CompName as a key... are you?

    You may want to read this...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    So if my main table shows me the company name and a separate field for each service they provide, how do I combine those separate table fields into a list/combo box that will then be available on the Client Form?
    Or for the RowSource property of your combobox you might want to go with a union query (SQL statement) which can combine the results of more than one query (SQL statement).

    Union
    http://www.w3schools.com/sql/sql_union.asp
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Feb 2005
    Location
    Ontario, Canada
    Posts
    8

    Thumbs up

    Quote Originally Posted by Teddy
    Stop right there.

    You need to create another table to track services. I also see no primary key, you're not using CompName as a key... are you?

    You may want to read this...
    Hi,
    Thank you to both people who replied.....
    Firstly, yes I do have a primary key in the table that I omitted listing called SSPID (Autonumber). The main information form reads directly from the main table, so if I get your idea correctly, I should have a separate table setup to track the selections we make. How about if I have the main form of info that includes a subform that pulls from a List and saves back to a separate table using the SSPID primary key as the links to have everything flow.

    Then when I am entering rating data into the ClientForm, it will have a combobox showing selections particular to that service provider.

    It makes sense and everything has kind of linked together in my head even though I probably haven't explained it here very well!! I thought I was missing something but couldn't see my way clear. If I got the basics of the idea I will run with it and consider my question answered. If I have just complicated my life instead....I am open to suggestion!

    Thank you,
    Sharon

  5. #5
    Join Date
    Feb 2005
    Location
    Ontario, Canada
    Posts
    8
    Quote Originally Posted by Bullschmidt
    Or for the RowSource property of your combobox you might want to go with a union query (SQL statement) which can combine the results of more than one query (SQL statement).

    Union
    http://www.w3schools.com/sql/sql_union.asp
    Hi,
    I will be trying the union query idea first! Thought I would save myself some trouble as I can use my other idea as backup!!
    Thanks,
    Sharon

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you know every possible service that your application uses in perpetuity then your model may well meet your requirements. If there is the slightest possibility that it may not then I'd suggest you ditch this design as soon as possible - if your requiremnts change then this model will become an absolute pain to support in the short to medium terms.

    As a general rule the table design you have fails the basic rules behind normalisation. Apologies if you have already followed the ref Teddy cites, but its well worth a read. Howver you also need to bear in mind the a rule is only a rule if you can occasionbaly break it - its your app, and in some circumstances you can genuinely know upfront that there will never be more than 8 services - in which case your design may make sense.

    just my 2 1/2d

  7. #7
    Join Date
    Feb 2005
    Location
    Ontario, Canada
    Posts
    8

    Unhappy Converting Table Columns to ComboBox info

    Okay, I'm still stuck. What am I missing here? I appreciate the note from healdem but you were rather vague on your recommendations other than telling me what isn't good practice.

    This almost seems like an issue on whether the data goes down rather than across. The data in a table column goes down and adapts to lists/combo boxes very well. On that note, I did try the other ideas and the union query info is good if you are combining info from more than one table to show in the same list. I did get it to work showing me the info fields in the one table but even the WHERE part of the statement didn't define the individual records based on the company ID #. I got all of the info out of the first column regardless and nothing else.

    My data is in a string going across in one row of table data. The idea of the separate table for the services is one idea, and yes there could be more than 8 services in the future. Unlikely, but possible therefore creating the situation of amending the input form and the services table holding the selections.

    What is so tough about specifying multiple services a company does and then show these individual selections later in a drop down box? This isn't rocket science but it is eluding me....maybe I'm working too hard!

    My first table had all of the service fields (1-8) in the main company table (as described in my first post), then I tried only having a serviceID # that linked to a secondary table holding the CompanyID#, the ServiceID# (primary Key) and the 8 fields for the services provided. Either way, I'm back at square one trying to get multiple fields (in design view) or columns (in datasheet view) into a table list.

    What else can I try?
    Thanks,
    Sharon

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by novelideas
    What is so tough about specifying multiple services a company does and then show these individual selections later in a drop down box? This isn't rocket science but it is eluding me....maybe I'm working too hard!
    That's what SQL is for...

    then I tried only having a serviceID # that linked to a secondary table holding the CompanyID#, the ServiceID# (primary Key) and the 8 fields for the services provided.
    Uhhhh... what?

    Why do you need to have 8 service fields in the secondary table? Did you read that link I provided? it might clear things up for you...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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