Form design question - one more try

[hilz]

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

 

[Jeff Cox]

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

 

[hilz]

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

 

[Ron2006]

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

 

[hilz]

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

 

[Joseph Meehan]

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.

 

[Ron2006]

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.

 

[gee664]

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).

 

[hilz]

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

 

[gee664]

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.