Form design question - one more try

#1 (**permalink**) 28-Jul-2006, 08:08 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9966-form-design-question-one-more-try.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
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 (**permalink**) 28-Jul-2006, 08:08 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
same Contract ID as the contract ID in the mainform.

Hope this helps,

Jeff Cox

#3 (**permalink**) 28-Jul-2006, 08:08 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
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

#4 (**permalink**) 28-Jul-2006, 08:08 AM

possible way.

Create a work table
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
wrkTable
tlbContractID
tblCustomerID

To make the report

1) delete query to empty work table
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
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

#5 (**permalink**) 28-Jul-2006, 08:08 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
> 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
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

#6 (**permalink**) 28-Jul-2006, 08:08 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
>> 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

#7 (**permalink**) 28-Jul-2006, 08:08 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
the customer information for each of the three customers.

#8 (**permalink**) 28-Jul-2006, 08:09 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
by name).

#9 (**permalink**) 28-Jul-2006, 08:09 AM

gee664@gmail.com wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
> Use combo boxes, one for each CUSTOMER_IDs.
>
> The Control Source for the combo box is CONTRACT.CUSTOMER_IDs and for
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9966
> 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