Subforms in subforms

[Alan Bloom]

I'm designing a db which stores client details but each client has multiple
'occasions'. For each occasion there is a section which has items purchased
(there are sometimes none, sometimes several). But the main difficulity I'm
having is when I try to do stock control stuff to keep track of how many of
each individual item has been sold.

The basis I've got so far is a client table an occasion table, which is
linked to the client ID, which i working fine (ie multiple occasions per
client). There is a big form with client details and in that form is a
subform, containing the occasion stuff.

-->I thought of having another subform within the occasion subform and
trying to make a relationship where multiple items purchased are linked to
occassion ID (and therefore, the client details can be worked out). But
this isn't working. I've tried all sorts of variations on the relationship
type and changing which fieds are linked but I'm not having any success.

Is there a much better approach? Has anyone made subforms in subforms?
Many, many thanks for any help
Alan Bloom

[Allen Browne]

Good idea: an Occasion table containing the ClientID and date of the
purchase, and an OccasionDetail table containing one row for each of the
products purchased on that occasion.

For an example of how to do that, open the Northwind sample database that
installs with Access. You will see a Customers table, an Orders table, and
an Order Details table. The Relationships windows (Tools menu) shows how
they link together. The Orders form shows how to select the client for the
occasion, and enter the detail rows for the occasion in a subform.

Ultimately, the quantities of each product sold end up in the Order Details
table in the Northwind example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alan Bloom" <al********@hot mail.com> wrote in message
news:3f******** @mk-nntp-1.news.uk.world online.com...

I'm designing a db which stores client details but each client has multiple 'occasions'. For each occasion there is a section which has items purchased (there are sometimes none, sometimes several). But the main difficulity I'm having is when I try to do stock control stuff to keep track of how many of each individual item has been sold.

The basis I've got so far is a client table an occasion table, which is
linked to the client ID, which i working fine (ie multiple occasions per
client). There is a big form with client details and in that form is a
subform, containing the occasion stuff.

-->I thought of having another subform within the occasion subform and
trying to make a relationship where multiple items purchased are linked to
occassion ID (and therefore, the client details can be worked out). But
this isn't working. I've tried all sorts of variations on the relationship type and changing which fieds are linked but I'm not having any success.

Is there a much better approach? Has anyone made subforms in subforms?
Many, many thanks for any help
Alan Bloom