Welcome to SPN

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

Sign Up Now!

Form design question - one more try

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

  1. hilz

    hilz
    Expand Collapse
    Guest

    Hi.
    I asked this question before, and did not get any answers.
    I hope someone will answer me this time!


    I have the following two tables:

    CONTRACT
    ********
    ID (primary key)
    CUSTOMER_ID1 (foreign key)
    CUSTOMER_ID2 (foreign key)
    CUSTOMER_ID3 (foreign key)
    AMOUNT
    some more columns...


    CUSTOMER
    ********
    ID (primary key)
    NAME
    some more columns...



    I have a relation many-to-one from each of the three
    CONTRACT.CUSTOMER_IDs to CUSTOMER.ID.

    So each contract can have up to three customers.

    I am having trouble displaying the customers in a contract form.
    I tried with a subform but that does not work.
    what can i do to display those three customers in the contract form?

    thanks
     
  2. Loading...


  3. Jeff Cox

    Jeff Cox
    Expand Collapse
    Guest

    hilz wrote:
    > Hi.
    > I asked this question before, and did not get any answers.
    > I hope someone will answer me this time!
    >
    >
    > I have the following two tables:
    >
    > CONTRACT
    > ********
    > ID (primary key)
    > CUSTOMER_ID1 (foreign key)
    > CUSTOMER_ID2 (foreign key)
    > CUSTOMER_ID3 (foreign key)
    > AMOUNT
    > some more columns...
    >
    >
    > CUSTOMER
    > ********
    > ID (primary key)
    > NAME
    > some more columns...
    >
    >
    >
    > I have a relation many-to-one from each of the three
    > CONTRACT.CUSTOMER_IDs to CUSTOMER.ID.
    >
    > So each contract can have up to three customers.
    >
    > I am having trouble displaying the customers in a contract form.
    > I tried with a subform but that does not work.
    > what can i do to display those three customers in the contract form?
    >
    > thanks


    First, I suggest that you remove the Customer_ID from the first table.
    Then add a Contract_ID to the Customer Table. This will allow for more
    than 3 customers if that ever happens.

    Then build a form based on the Contract table. Add a subformcontrol to
    the mainform with the Customer table as the recordsource. Make this
    visible in Datasheet view if you like. Link them master-child as ID and
    Contract_ID.

    This will show all the customers in the subform when they contain the
    same Contract ID as the contract ID in the mainform.

    Hope this helps,

    Jeff Cox
     
  4. hilz

    hilz
    Expand Collapse
    Guest

    Jeff Cox wrote:
    > hilz wrote:
    >> Hi.
    >> I asked this question before, and did not get any answers.
    >> I hope someone will answer me this time!
    >>
    >>
    >> I have the following two tables:
    >>
    >> CONTRACT
    >> ********
    >> ID (primary key)
    >> CUSTOMER_ID1 (foreign key)
    >> CUSTOMER_ID2 (foreign key)
    >> CUSTOMER_ID3 (foreign key)
    >> AMOUNT
    >> some more columns...
    >>
    >>
    >> CUSTOMER
    >> ********
    >> ID (primary key)
    >> NAME
    >> some more columns...
    >>
    >>
    >>
    >> I have a relation many-to-one from each of the three
    >> CONTRACT.CUSTOMER_IDs to CUSTOMER.ID.
    >>
    >> So each contract can have up to three customers.
    >>
    >> I am having trouble displaying the customers in a contract form.
    >> I tried with a subform but that does not work.
    >> what can i do to display those three customers in the contract form?
    >>
    >> thanks

    >
    > First, I suggest that you remove the Customer_ID from the first table.
    > Then add a Contract_ID to the Customer Table. This will allow for more
    > than 3 customers if that ever happens.
    >
    > Then build a form based on the Contract table. Add a subformcontrol to
    > the mainform with the Customer table as the recordsource. Make this
    > visible in Datasheet view if you like. Link them master-child as ID and
    > Contract_ID.
    >
    > This will show all the customers in the subform when they contain the
    > same Contract ID as the contract ID in the mainform.
    >
    > Hope this helps,
    >
    > Jeff Cox




    But if I do that, then i will not be able to have more than one contract
    for any given customer.
    Ideally, I can have a join table that allows a many-to-many relation.
    but that is an overkill, and will probably complicated the situation. I
    don't really need more than 3 customers per contract, and I don't see
    the need for that in my business process.

    Actually, I am trying to create a report and not a form (I made a
    mistake by saying a form), but it shouldn't be that much different i guess.

    So after the subreport approach failed, I tried to create text boxes to
    display properties of every customer, but i was not able to create a
    query that can look up the values from the customer table based on a
    given CONTRACT.CUSTOMER_IDx because the textbox "Control Source" allows
    me to enter expressions and not queries.
    I am sure I am missing something somewhere.
    I hope someone can help!

    thanks


    the source
     
  5. Ron2006

    Ron2006
    Expand Collapse
    Guest

    possible way.

    Create a work table
    wrkTable
    tlbContractID
    tblCustomerID

    To make the report

    1) delete query to empty work table
    2) append query to append Contract ID customer1 id if customer1id is
    not null
    3) append query to append contract id customer2id if customer2id is not
    null
    4) append query to append contract id customer3id if customer3id is not
    null

    Use this work table is the primary table to drive the report with the
    real contract table and the customer table linked by contractid and
    customerid
     
  6. hilz

    hilz
    Expand Collapse
    Guest

    Ron2006 wrote:
    > possible way.
    >
    > Create a work table
    > wrkTable
    > tlbContractID
    > tblCustomerID
    >
    > To make the report
    >
    > 1) delete query to empty work table
    > 2) append query to append Contract ID customer1 id if customer1id is
    > not null
    > 3) append query to append contract id customer2id if customer2id is not
    > null
    > 4) append query to append contract id customer3id if customer3id is not
    > null
    >
    > Use this work table is the primary table to drive the report with the
    > real contract table and the customer table linked by contractid and
    > customerid
    >



    But this wrkTable is the same as the join table i mentioned in my
    previous post, which i am trying to avoid.
    I don't really need a many-to-many relation between the customer and the
    contract.
    There are only 3 customers for any given contract, and creating a join
    table is not really necessary, and will complicate things a bit.
    My dilema is how to show these three customers that are related to a
    given contract in the report of that contract.

    thanks for any help
     
  7. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    hilz wrote:
    > Ron2006 wrote:
    >> possible way.
    >>
    >> Create a work table
    >> wrkTable
    >> tlbContractID
    >> tblCustomerID
    >>
    >> To make the report
    >>
    >> 1) delete query to empty work table
    >> 2) append query to append Contract ID customer1 id if customer1id
    >> is not null
    >> 3) append query to append contract id customer2id if customer2id is
    >> not null
    >> 4) append query to append contract id customer3id if customer3id is
    >> not null
    >>
    >> Use this work table is the primary table to drive the report with the
    >> real contract table and the customer table linked by contractid and
    >> customerid
    >>

    >
    >
    > But this wrkTable is the same as the join table i mentioned in my
    > previous post, which i am trying to avoid.
    > I don't really need a many-to-many relation between the customer and
    > the contract.


    If I understand your data correctly you have a many to many relationship
    in real life. I suggest that having the same in Access is the way to go.

    > There are only 3 customers for any given contract, and creating a join
    > table is not really necessary, and will complicate things a bit.
    > My dilema is how to show these three customers that are related to a
    > given contract in the report of that contract.
    >
    > thanks for any help


    --
    Joseph Meehan

    Dia duit
     
  8. Ron2006

    Ron2006
    Expand Collapse
    Guest

    do it only for the report.

    Another possible way.

    In a query, put in your contract table then put in three customers. one
    linked to each of the customer ids. That now gives you one query with
    the customer information for each of the three customers.
     
  9. gee664@gmail.com

    gee664@gmail.com
    Expand Collapse
    Guest

    Use combo boxes, one for each CUSTOMER_IDs.

    The Control Source for the combo box is CONTRACT.CUSTOMER_IDs and for
    Row Source use your Customer table (select id, name from customer order
    by name).
     
  10. hilz

    hilz
    Expand Collapse
    Guest

    gee664@gmail.com wrote:
    > Use combo boxes, one for each CUSTOMER_IDs.
    >
    > The Control Source for the combo box is CONTRACT.CUSTOMER_IDs and for
    > Row Source use your Customer table (select id, name from customer order
    > by name).
    >


    That is a great idea, and it worked in the form. Thanks.
    Now what do i do in the report?
    how do i show the three customers in a contract report?

    a simple description as what you gave above is good enough.

    thanks
     
  11. gee664@gmail.com

    gee664@gmail.com
    Expand Collapse
    Guest

    Create a query with tbl.contract and add tbl.customer 3 times, one for
    each customer. Basically, do what you would do for 1 customer, then
    repeat 2 more times.
     

Share This Page