Summary function on relational records?

March 25, 200421 yr

I hope someone can point me in the right direction because i am going mad here.

I am working on a database for entering invoices. Each invoice (record) has an ID and fields for entering job codes and price, and then the total is shown at the bottom. So typical records would look like this:

-----

ID: X1

CODE PRICE

c 100

d 200

a 100

c 50

TOTAL : 500

----

ID: X2

CODE PRICE

c 100

d 200

c 20

b 50

TOTAL: 380

------

There are 2 db files. The master one holds each "invoice" and ID and shows the codes and prices in a portal (as shown above) from the related file.

What i need to do, on the layout above (master file), is show the total for one of the specific codes, EG for C - as you see that on each invoice there may be more than one. For example, invoice X1 C total would be 200.

I have now reached the point where i have thought about this too much and can't rationally think the process through anymore. Can anyone help. Much appreciated.

BASIC EXAMPLE FILES ATTACHED IN ZIP FILE

Version: Developer v6

Example.zip

March 25, 200421 yr

Author

OK, i worked it out myself, but i thought i would post the answer here just in case anyone else has the same problem. In the end it was horribly easy - i think it was a case of thinking too much at the beggining which wore me down. Best answer for these things is to go go away and ntot think about it for a couple of hours.

To solve the problem created a new COMBINE field which was made up off the ID and CODE, eg X1C

Created this calulation in both databases and then set up a relationship between the two with the COMBINE field. This meant in the master file i could knock up a portal based on this relationship which would only show X1C prices and then sum these prices. Has the advantage of not have to do any sub-summary stuff which woudlhave required sorting of the other database which i wanted to avoid.