Layby Design Help!

[Jenny Lee via AccessMonster.com]

Hi,

Just wondering if anyone can help. Im trying to design a layby system. I am
having so much trouble. This is what I have so far.

CUSTOMER
CUST_ID(pk)
CUST_LNAME
CUST_FNAME
etc

LAYBY LAYBY_RECIEPT
LAYBY_ID(pk) REC_ID(pk)
CUST_ID(fk) LAYBY_ID(fk)
LAYBY_DATE LAYBY_PAYMENT
LAYBY_DUEDATE

LAYBY_DETAIL
LAYBY_ID(pk, fk)
ARTWORK_ID(pk,fk)

ARTWORK
ARTWORK_ID(pk)
etc

A Customer may have many Laybys
A Layby may contain many artworks ( but each artwork is unique so therefore
each artwork can only be in one layby)
Each Layby recieves many payments

And then on the final payment of the layby an invoice is written.

The way I have it, it just doesn't seem to work. I am a student so Im only
learing. Can anyone please give me some suggestions.

Cheers
Jen

 

[tina]

your table design looks correct to me. it matches a common example of
relational design: Customers, Orders (LayBys), Products (Artwork),
OrderDetails (LayByDetails), and OrderPayments (LayByReceipts).

Customers 1:n Orders
Orders 1:n OrderDetails
Products 1:n OrderDetails
Orders 1:n OrderPayments

The way I have it, it just doesn't seem to work.

pls describe the problem(s) you are experiencing in some detail.

hth

 

[Jenny Lee via AccessMonster.com]

Hi
Thanks for your quick response. I had no problem creating the layby form
and and making payments, The problem is in my report. The report shows
customer details layby details and payments recieved. The trouble is that
if there is one artwork on layby and that layby has many payments, the
report shows that particular piece of artwork as many times as there is a
payment. example: if Layby#25 has recieved 5 payments then on the report
the Artwork is listed 5 times.

 

[Duane Hookom]

Consider using a subreport for the payments.

 

[tina]

sounds like the Artwork and Payment controls are in the report's Detail
section. try opening the report in design view. click on the Artwork control
to select it, and open the Properties box. set the HideDuplicates property
to Yes.

or, as Duane said, consider using a subreport for the payments.

hth

 

[Jenny Lee via AccessMonster.com]

Thanks,

You are a legend! I have been racking my brains for so long and your
solution was so simple!! I set the Artwork property box to hide duplicates
and now everything is working as it should.

Thanks again
jen

 

[tina]

you're welcome!