Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Combination of table information

Discussion in 'Information Technology' started by Rudi, Jul 28, 2006.

  1. Rudi

    Rudi
    Expand Collapse
    Guest

    Hi. I'm a noobie in the MS Access world and I need a little help.

    I have 2 tables that look something like this:

    Customer
    - ID_customer (Autonumber)
    - Name
    - ID_respresentative

    Representative
    - ID_representative
    - Name_representative

    The relation between them is like this: a customer can have none, one
    or more representatives. A representative can represent only one
    customer at a time.

    How can I create a table that would show all the customers (the
    customers that have representatives and the ones that don't) and their
    representatives information.

    example:

    ID_cusomer | Name | ID_representative | Name_representative

    1 | Customer1 | 1 | Representative1
    1 | Customer1 | 2 | Representative2
    2 | Customer2 | |
    3 | Customer3 | 3 | Representative3
    4 | Customer4 | |

    Any help would be greatly appreciated.
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Rudi, do you need to track:
    a) the different customers a representative handled over time, or
    b) just the current customer for each rep?

    If (b), just add an extra field to your Representative table:
    - ID_customer
    On the form, add a combo box so you can choose the Customer for the rep. Job
    done.

    If (a), you need an extra table:
    CustomerRepresentative table:
    - ID_customer (Number)
    - ID_representative (Number)
    - StartDate Date/Time
    Then create a subform in your Representative form, bound to this table.
    Each record in the subform represents the customer that the rep handled. The
    most recent date is the current one.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Rudi" <rudi.zupan@gmail.com> wrote in message
    news:1148385469.202427.237840@g10g2000cwb.googlegroups.com...
    > Hi. I'm a noobie in the MS Access world and I need a little help.
    >
    > I have 2 tables that look something like this:
    >
    > Customer
    > - ID_customer (Autonumber)
    > - Name
    > - ID_respresentative
    >
    > Representative
    > - ID_representative
    > - Name_representative
    >
    > The relation between them is like this: a customer can have none, one
    > or more representatives. A representative can represent only one
    > customer at a time.
    >
    > How can I create a table that would show all the customers (the
    > customers that have representatives and the ones that don't) and their
    > representatives information.
    >
    > example:
    >
    > ID_cusomer | Name | ID_representative | Name_representative
    >
    > 1 | Customer1 | 1 | Representative1
    > 1 | Customer1 | 2 | Representative2
    > 2 | Customer2 | |
    > 3 | Customer3 | 3 | Representative3
    > 4 | Customer4 | |
    >
    > Any help would be greatly appreciated.
     
  4. Rudi

    Rudi
    Expand Collapse
    Guest

    Hi.

    Thank you for responding!

    I need to track the customers and their representative(s) (if any -
    some customers don't have a representative). This way I will be able to
    pick a certain customer with a certain representative via a combo box
    on my order form.

    I can't use the suggested solutions.

    In the (a) suggestion the new CustomerRepresentative table will track
    only customers that have a representative. But I also have to track the
    customers that don't have a representative.

    The (b) solution only tracks none or one representative for a certain
    customer. But I also have customers with multiple representatives.

    If you think of any other solution PLEASE let me know. Becouse I'm
    starting to lose my hope about this. :(
     
  5. Rudi

    Rudi
    Expand Collapse
    Guest

    @Allen Browne

    P.S. - I checked out your Access webpage
    (http://allenbrowne.com/tips.html). Great stuff. I will certainly look
    it over when I'll have a little extra time.
     
  6. Rudi

    Rudi
    Expand Collapse
    Guest

    Correction. The (b) suggestion doesn't include the customers that don't
    have a representative.
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Actully, Rudi, it does cover those cases.

    A customer who has no rep, has an entry in the Customer table, but none in
    the CustomerRepresentative table.

    A rep who has no customer (in transition?) has an entry in the
    Representative table, but none in the CustomerRepresentative table.

    When you create a query, you will need to use outer joins to get these
    returned. See:
    The Query Lost My Records!
    at:
    http://allenbrowne.com/casu-02.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Rudi" <rudi.zupan@gmail.com> wrote in message
    news:1148489946.342039.227830@38g2000cwa.googlegroups.com...
    > Correction. The (b) suggestion doesn't include the customers that don't
    > have a representative.
    >
     
  8. Rudi

    Rudi
    Expand Collapse
    Guest

    :D It works. Thank you very much, Allen. As I said I'm still wet behind
    the eares when it comes to MS Access. Thank you again. I owe you a
    Foster's ;)
     
  9. Rudi

    Rudi
    Expand Collapse
    Guest

    :( no wait. Another problem :(

    I tryed to link the CustomerRepresentaive table to a combox. The
    problem is that if I select a CustomerID, the displayed customer data
    is allways the selected customer with his first Representative (even if
    I select the (same) customer with the second (or third)
    representative).

    I think this is happening because every time I select a record I only
    select the CustomerID. The selection is not telling the program which
    Representative I selected so it allways display's just the firs
    Representative.
    I tryed to mend this by adding a ID_Number in the
    CustomerRepresentative table, so that every record has a unique number
    that sets the individual records apart. The new CustomerRepresentaive
    table now looks like this:

    ID_Number (Autonumber)
    ID_Customer (Number)
    ID_Representaive (Number)

    Unfortunately this solution has another problem. The ID_Number (in the
    CustomerRepresentative table) is an Autonumber fieled. And the
    Autonumber field creates a number only for the entrys in the
    CustomerRepresentative table. Thus the Customers without the
    representatives (entrys that are only in the Customer table) don't get
    a ID_Number and can not be selected in the Combobox.

    HELP!

    How can I make this work? How can I have a combobox that will let the
    user select a certain customer with a certain representative?
     
  10. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Your interface will be a main form bound to the Customer table, and a
    subform bound to the CustomerRepresentative table. The subform will show
    only those records that match the Customer in the main form. It will contain
    a combo for selecting the representative for the customer. This combo's
    RowSource will be the Representative table.

    Perhaps you have a main form for the Representative, with a subform bound to
    the CustomerRepresentative table. The subform will show only records that
    match the Rep in the main form. It will contain a combo for selecting the
    customer for the rep. This combo's RowSource will be the *Customer* table
    (not the CustomerRep table.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Rudi" <rudi.zupan@gmail.com> wrote in message
    news:1149013461.903031.295910@i40g2000cwc.googlegroups.com...
    > :( no wait. Another problem :(
    >
    > I tryed to link the CustomerRepresentaive table to a combox. The
    > problem is that if I select a CustomerID, the displayed customer data
    > is allways the selected customer with his first Representative (even if
    > I select the (same) customer with the second (or third)
    > representative).
    >
    > I think this is happening because every time I select a record I only
    > select the CustomerID. The selection is not telling the program which
    > Representative I selected so it allways display's just the firs
    > Representative.
    > I tryed to mend this by adding a ID_Number in the
    > CustomerRepresentative table, so that every record has a unique number
    > that sets the individual records apart. The new CustomerRepresentaive
    > table now looks like this:
    >
    > ID_Number (Autonumber)
    > ID_Customer (Number)
    > ID_Representaive (Number)
    >
    > Unfortunately this solution has another problem. The ID_Number (in the
    > CustomerRepresentative table) is an Autonumber fieled. And the
    > Autonumber field creates a number only for the entrys in the
    > CustomerRepresentative table. Thus the Customers without the
    > representatives (entrys that are only in the Customer table) don't get
    > a ID_Number and can not be selected in the Combobox.
    >
    > HELP!
    >
    > How can I make this work? How can I have a combobox that will let the
    > user select a certain customer with a certain representative?
     
  11. Rudi

    Rudi
    Expand Collapse
    Guest

    I allready had my application set up the way you described. And it
    works great.

    Now I wanted to use this Customer, Rep, ... data in a new form called
    Order. In this interface the user would input order info. And among the
    order information the user would also select the specific customer and
    one of his (if he has any) representatives via a combobox. Here is
    where the problem lies. First I made a query that would include all the
    Customer, Representative information (Customers without Rep, Customers
    with Rep). So far so good. But when I bind this query to a combobox in
    my Order form, the combobox selects only the CustomerID. This way, if
    the Customer has more than one Rep, the user can't select the second or
    third Rep. The selected CustomerID allways displays only the default,
    first Representative.

    I think this problem occures, becouse there is nothing to set the
    entry's with the same Customer (and different Rep) apart. I tried to
    mend this by creating a new table and using an append query (and delete
    query), filling this new table with the query info. In the new table I
    created a Autonumber for each entry that would set the "problematic"
    entrys appart.

    And it works OK but the problem is the Autonumber field. Everytime the
    delete query deletes the old records and inserts the new, the
    autonumber doesn't reset. The autonumbers start counting from the last
    number that was deleted and not from 1.
    I know that reseting the Autonumber requires me to compact the
    database.

    Is there a better way? Do you know of a code that would create a
    sequence number for each entry? Or a different solution for my "Order
    form combobox" problem? I know I'm a pain. :) I apologize in advance

    Allen Browne je napisal:
    > Your interface will be a main form bound to the Customer table, and a
    > subform bound to the CustomerRepresentative table. The subform will show
    > only those records that match the Customer in the main form. It will contain
    > a combo for selecting the representative for the customer. This combo's
    > RowSource will be the Representative table.
    >
    > Perhaps you have a main form for the Representative, with a subform bound to
    > the CustomerRepresentative table. The subform will show only records that
    > match the Rep in the main form. It will contain a combo for selecting the
    > customer for the rep. This combo's RowSource will be the *Customer* table
    > (not the CustomerRep table.)
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Rudi" <rudi.zupan@gmail.com> wrote in message
    > news:1149013461.903031.295910@i40g2000cwc.googlegroups.com...
    > > :( no wait. Another problem :(
    > >
    > > I tryed to link the CustomerRepresentaive table to a combox. The
    > > problem is that if I select a CustomerID, the displayed customer data
    > > is allways the selected customer with his first Representative (even if
    > > I select the (same) customer with the second (or third)
    > > representative).
    > >
    > > I think this is happening because every time I select a record I only
    > > select the CustomerID. The selection is not telling the program which
    > > Representative I selected so it allways display's just the firs
    > > Representative.
    > > I tryed to mend this by adding a ID_Number in the
    > > CustomerRepresentative table, so that every record has a unique number
    > > that sets the individual records apart. The new CustomerRepresentaive
    > > table now looks like this:
    > >
    > > ID_Number (Autonumber)
    > > ID_Customer (Number)
    > > ID_Representaive (Number)
    > >
    > > Unfortunately this solution has another problem. The ID_Number (in the
    > > CustomerRepresentative table) is an Autonumber fieled. And the
    > > Autonumber field creates a number only for the entrys in the
    > > CustomerRepresentative table. Thus the Customers without the
    > > representatives (entrys that are only in the Customer table) don't get
    > > a ID_Number and can not be selected in the Combobox.
    > >
    > > HELP!
    > >
    > > How can I make this work? How can I have a combobox that will let the
    > > user select a certain customer with a certain representative?
     
  12. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Your Order form has a CustomerID combo, and a RepID combo.
    The CustomerID combo is bound to the Customer table, so that's easy.
    You want to limit the RepID to only reps for the selected customer.

    That will involve writing some code that changes the RowSource of the RepID
    combo to an SQL statement that limits it to only reps for that customer. The
    kind of code you need is illustrated in this article:
    Limit content of combo/list boxes
    at:
    http://www.mvps.org/access/forms/frm0028.htm

    If the RepID's bound column is visible (not zero-width), put the code into
    the Enter event of the combo.

    If it is zero-width, you need it to execute even when the combo does not
    have focus, so use the RepID's Exit event to restore all values.
    (Alternatively, you have to use the AfterUpdate event procedure of the
    CustomerID combo, and also the Current event of the form, and also the Undo
    event of the form (which has to use the OldValue of CustomerID.) And then
    you still have trouble with the combo not displaying correctly, due to
    timing in Access.)

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Rudi" <rudi.zupan@gmail.com> wrote in message
    news:1149068697.695468.326170@h76g2000cwa.googlegroups.com...
    >I allready had my application set up the way you described. And it
    > works great.
    >
    > Now I wanted to use this Customer, Rep, ... data in a new form called
    > Order. In this interface the user would input order info. And among the
    > order information the user would also select the specific customer and
    > one of his (if he has any) representatives via a combobox. Here is
    > where the problem lies. First I made a query that would include all the
    > Customer, Representative information (Customers without Rep, Customers
    > with Rep). So far so good. But when I bind this query to a combobox in
    > my Order form, the combobox selects only the CustomerID. This way, if
    > the Customer has more than one Rep, the user can't select the second or
    > third Rep. The selected CustomerID allways displays only the default,
    > first Representative.
    >
    > I think this problem occures, becouse there is nothing to set the
    > entry's with the same Customer (and different Rep) apart. I tried to
    > mend this by creating a new table and using an append query (and delete
    > query), filling this new table with the query info. In the new table I
    > created a Autonumber for each entry that would set the "problematic"
    > entrys appart.
    >
    > And it works OK but the problem is the Autonumber field. Everytime the
    > delete query deletes the old records and inserts the new, the
    > autonumber doesn't reset. The autonumbers start counting from the last
    > number that was deleted and not from 1.
    > I know that reseting the Autonumber requires me to compact the
    > database.
    >
    > Is there a better way? Do you know of a code that would create a
    > sequence number for each entry? Or a different solution for my "Order
    > form combobox" problem? I know I'm a pain. :) I apologize in advance
    >
    > Allen Browne je napisal:
    >> Your interface will be a main form bound to the Customer table, and a
    >> subform bound to the CustomerRepresentative table. The subform will show
    >> only those records that match the Customer in the main form. It will
    >> contain
    >> a combo for selecting the representative for the customer. This combo's
    >> RowSource will be the Representative table.
    >>
    >> Perhaps you have a main form for the Representative, with a subform bound
    >> to
    >> the CustomerRepresentative table. The subform will show only records that
    >> match the Rep in the main form. It will contain a combo for selecting the
    >> customer for the rep. This combo's RowSource will be the *Customer* table
    >> (not the CustomerRep table.)
    >>
    >> "Rudi" <rudi.zupan@gmail.com> wrote in message
    >> news:1149013461.903031.295910@i40g2000cwc.googlegroups.com...
    >> > :( no wait. Another problem :(
    >> >
    >> > I tryed to link the CustomerRepresentaive table to a combox. The
    >> > problem is that if I select a CustomerID, the displayed customer data
    >> > is allways the selected customer with his first Representative (even if
    >> > I select the (same) customer with the second (or third)
    >> > representative).
    >> >
    >> > I think this is happening because every time I select a record I only
    >> > select the CustomerID. The selection is not telling the program which
    >> > Representative I selected so it allways display's just the firs
    >> > Representative.
    >> > I tryed to mend this by adding a ID_Number in the
    >> > CustomerRepresentative table, so that every record has a unique number
    >> > that sets the individual records apart. The new CustomerRepresentaive
    >> > table now looks like this:
    >> >
    >> > ID_Number (Autonumber)
    >> > ID_Customer (Number)
    >> > ID_Representaive (Number)
    >> >
    >> > Unfortunately this solution has another problem. The ID_Number (in the
    >> > CustomerRepresentative table) is an Autonumber fieled. And the
    >> > Autonumber field creates a number only for the entrys in the
    >> > CustomerRepresentative table. Thus the Customers without the
    >> > representatives (entrys that are only in the Customer table) don't get
    >> > a ID_Number and can not be selected in the Combobox.
    >> >
    >> > HELP!
    >> >
    >> > How can I make this work? How can I have a combobox that will let the
    >> > user select a certain customer with a certain representative?
     
  13. Rudi

    Rudi
    Expand Collapse
    Guest

    Good idea about the 2 comboboxes. Thank you.

    Allen Browne je napisal:
    > Your Order form has a CustomerID combo, and a RepID combo.
    > The CustomerID combo is bound to the Customer table, so that's easy.
    > You want to limit the RepID to only reps for the selected customer.
    >
    > That will involve writing some code that changes the RowSource of the RepID
    > combo to an SQL statement that limits it to only reps for that customer. The
    > kind of code you need is illustrated in this article:
    > Limit content of combo/list boxes
    > at:
    > http://www.mvps.org/access/forms/frm0028.htm
    >
    > If the RepID's bound column is visible (not zero-width), put the code into
    > the Enter event of the combo.
    >
    > If it is zero-width, you need it to execute even when the combo does not
    > have focus, so use the RepID's Exit event to restore all values.
    > (Alternatively, you have to use the AfterUpdate event procedure of the
    > CustomerID combo, and also the Current event of the form, and also the Undo
    > event of the form (which has to use the OldValue of CustomerID.) And then
    > you still have trouble with the combo not displaying correctly, due to
    > timing in Access.)
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Rudi" <rudi.zupan@gmail.com> wrote in message
    > news:1149068697.695468.326170@h76g2000cwa.googlegroups.com...
    > >I allready had my application set up the way you described. And it
    > > works great.
    > >
    > > Now I wanted to use this Customer, Rep, ... data in a new form called
    > > Order. In this interface the user would input order info. And among the
    > > order information the user would also select the specific customer and
    > > one of his (if he has any) representatives via a combobox. Here is
    > > where the problem lies. First I made a query that would include all the
    > > Customer, Representative information (Customers without Rep, Customers
    > > with Rep). So far so good. But when I bind this query to a combobox in
    > > my Order form, the combobox selects only the CustomerID. This way, if
    > > the Customer has more than one Rep, the user can't select the second or
    > > third Rep. The selected CustomerID allways displays only the default,
    > > first Representative.
    > >
    > > I think this problem occures, becouse there is nothing to set the
    > > entry's with the same Customer (and different Rep) apart. I tried to
    > > mend this by creating a new table and using an append query (and delete
    > > query), filling this new table with the query info. In the new table I
    > > created a Autonumber for each entry that would set the "problematic"
    > > entrys appart.
    > >
    > > And it works OK but the problem is the Autonumber field. Everytime the
    > > delete query deletes the old records and inserts the new, the
    > > autonumber doesn't reset. The autonumbers start counting from the last
    > > number that was deleted and not from 1.
    > > I know that reseting the Autonumber requires me to compact the
    > > database.
    > >
    > > Is there a better way? Do you know of a code that would create a
    > > sequence number for each entry? Or a different solution for my "Order
    > > form combobox" problem? I know I'm a pain. :) I apologize in advance
    > >
    > > Allen Browne je napisal:
    > >> Your interface will be a main form bound to the Customer table, and a
    > >> subform bound to the CustomerRepresentative table. The subform will show
    > >> only those records that match the Customer in the main form. It will
    > >> contain
    > >> a combo for selecting the representative for the customer. This combo's
    > >> RowSource will be the Representative table.
    > >>
    > >> Perhaps you have a main form for the Representative, with a subform bound
    > >> to
    > >> the CustomerRepresentative table. The subform will show only records that
    > >> match the Rep in the main form. It will contain a combo for selecting the
    > >> customer for the rep. This combo's RowSource will be the *Customer* table
    > >> (not the CustomerRep table.)
    > >>
    > >> "Rudi" <rudi.zupan@gmail.com> wrote in message
    > >> news:1149013461.903031.295910@i40g2000cwc.googlegroups.com...
    > >> > :( no wait. Another problem :(
    > >> >
    > >> > I tryed to link the CustomerRepresentaive table to a combox. The
    > >> > problem is that if I select a CustomerID, the displayed customer data
    > >> > is allways the selected customer with his first Representative (even if
    > >> > I select the (same) customer with the second (or third)
    > >> > representative).
    > >> >
    > >> > I think this is happening because every time I select a record I only
    > >> > select the CustomerID. The selection is not telling the program which
    > >> > Representative I selected so it allways display's just the firs
    > >> > Representative.
    > >> > I tryed to mend this by adding a ID_Number in the
    > >> > CustomerRepresentative table, so that every record has a unique number
    > >> > that sets the individual records apart. The new CustomerRepresentaive
    > >> > table now looks like this:
    > >> >
    > >> > ID_Number (Autonumber)
    > >> > ID_Customer (Number)
    > >> > ID_Representaive (Number)
    > >> >
    > >> > Unfortunately this solution has another problem. The ID_Number (in the
    > >> > CustomerRepresentative table) is an Autonumber fieled. And the
    > >> > Autonumber field creates a number only for the entrys in the
    > >> > CustomerRepresentative table. Thus the Customers without the
    > >> > representatives (entrys that are only in the Customer table) don't get
    > >> > a ID_Number and can not be selected in the Combobox.
    > >> >
    > >> > HELP!
    > >> >
    > >> > How can I make this work? How can I have a combobox that will let the
    > >> > user select a certain customer with a certain representative?
     

Share This Page