Layby Design Help!

  • Thread starter Thread starter Jenny Lee via AccessMonster.com
  • Start date Start date
J

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
 
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.
Click to expand...

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

hth
 
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.
 
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
 
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
 
Back
Top