A somewhat complex relationship problem

[Buckie]

So consider this:

I have a table of Clients. There's also a table of Orders and Suborders. Each order contains one or more suborders. Each suborder has a Status field the value of which can be either 'new' or 'returning': the second suborder by date 'knows' it's 'returning' thanks to a self-relation by Order ID sorted by date.

 

I need to generate a list in the Clients layout on the fly that shows a total of "new" and "returning" for each client broken down by month starting from the current month and going back a year.

 

So I tried to create an unstored calculation field in the Clients table that says 'new' and relate it to Suborders by client ID and that Status field. It didn't work because Status is an inherently unstored calculation field. The original idea was to go further and create a set of 12 relationships corresponding to months. Actually the latter part worked if I took out the Status field.

 

It seems to be a dead end. Can anyone suggest a general solution to this problem? No need to write anything for me, just explain how you would do this. The easy solution would be to store the statuses but I don't want to/can't do that for a number of reasons.

[LVie]

Buckie,

 

Without knowing why you can't store the status field its hard to answer the question. In general to accomplish this rather than using an unstored calc for status in the suborders table use an auto-entered text field with 'Do not replace existing value of field (if any)' unchecked. That way you can use a set field or trigger to change the status field when necessary and still allow it to be indexed for the relationship. Can you give more detail about why you don't want to/can't store the status?

 

Levi

 

 

-- 

Laura (Levi) Vie | [email protected]  | 573-874-5176 | iChat:  leevie