Layby Design Help!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jenny Lee - 15 May 2005 03:27 GMT

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

Reply to this Message

tina - 15 May 2005 04:31 GMT

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

> Hi,
>
[quoted text clipped - 33 lines]
> Cheers
> Jen

Reply to this Message

Jenny Lee - 15 May 2005 04:58 GMT

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.

Reply to this Message

Duane Hookom - 15 May 2005 05:11 GMT

Consider using a subreport for the payments.

Signature

Duane Hookom
MS Access MVP

> Hi
> Thanks for your quick response. I had no problem creating the layby form
[quoted text clipped - 4 lines]
> payment. example: if Layby#25 has recieved 5 payments then on the report
> the Artwork is listed 5 times.

Reply to this Message

tina - 15 May 2005 08:12 GMT

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

Reply to this Message

Jenny Lee - 15 May 2005 13:01 GMT

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

Reply to this Message

tina - 16 May 2005 01:45 GMT

you're welcome! :)

> Thanks,
>
[quoted text clipped - 4 lines]
> Thanks again
> jen

Reply to this Message