Subform in a subform

[Maggieanp22]

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

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your subforms set up should mirror your table set up. E.g.:

Clients -> Clients form
Occasions -> Occasions subform on Clients form
Purchases -> Purchases subform on Occasions subform

Your tables should be set up to show their dependencies (foreign
keys):

CREATE TABLE Occasions (
OccasionID COUNTER NOT NULL,
ClientID INTEGER NOT NULL,
OccDate DATE NOT NULL, -- When the occasion occurred
-- other columns ??
CONSTRAINT PK_Occasions PRIMARY KEY (ClientID, OccDate),
CONSTRAINT FK_ClientID FOREIGN KEY (ClientID) ON Clients,
CONSTRAINT UNIQUE (OccasionID)
)
Note: COUNTER is the AutoNumber datatype in JET dbs.

CREATE TABLE Purchases (
PurchaseID COUNTER NOT NULL ,
OccasionID INTEGER NOT NULL ,
ProductID INTEGER NOT NULL ,
-- other columns ??
CONSTRAINT PK_Purchases PRIMARY KEY (OccasionID, ProductID),
CONSTRAINT FK_OccID FOREIGN KEY (OccasionID) REFERENCES Occasions,
CONSTRAINT FK_ProdID FOREIGH KEY (ProductID) REFERENCES Products,
CONSTRAINT UNIQUE (PurchaseID)
)

Link fields between forms:

frmClients sfmOccasions sfmPurchases
- ---------- ----------- ------------
ClientID ClientID
OccasionID OccasionID

HTH,

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8LHloechKq OuFEgEQLH6ACfQW SZS4fMo4B11F27W qHMu6nMgP8An3vR
Y2+tT6HWChxxIEX 0v4LMsAO4
=qm3l
-----END PGP SIGNATURE-----

Maggieanp22 wrote:

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

[TC]

You need to seperate the database design issues, from the user interface
issues.

If a client can have several occasions, and an occasion can have several
items, the classic design would be as follows.

tblClient
ClientID (PK)
name, address etc.

tblItem
ItemID (PK)
name, descrition etc.

tblOccasion
ClientID ( composite )
OccDate (primary key)

tblPurchasedIte m
ClientID ( composite )
OccDate ( primary )
ItemID ( key )

In this case, the composite keys are a little unwieldy. So instead, you
could have:

tblOccasion
OccasionID (PK) (auto#)
ClientID
OccDate
with a "no duplicates" index on ClientID + OccDate

tblPurchasedIte m
OccasionID ( composite )
ItemID (primary key)

Having got the fdatabase design correct, you then focu on the UI issues.
Certainly you could have a main form with clients, a subform with
poccasions, & a subform (within that subform) for the purchased items.

HTH,
TC

"Maggieanp2 2" <ma*********@ao l.com> wrote in message
news:20******** *************** ****@mb-m25.aol.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