Summaries

[GrantSymon]

Hi,

I'm sure this is really simple for some of you ... but I've been struggling with it for hours. I'm assuming that this is a calculation problem ... although it might turn out to be a relationship problem. :

Here's what I'm trying to do :

I'm altering my petty cash file to produce a sort of double-entry accounting system (or so my accountant tells me)

I have a Table for entering debits and credits

I have another Table in which I will set the Position at Bank for each fiscal year (I'm thinking the best way to do this is to have a single record per bank account per year. I have 5 bank accounts

What I can't manage to do :

I would like to have a running summary which shows the related bank account's current status (running summary) in each record. (IOW, which adds debits and credits to the appropriate Position at Bank total in the related Table). To this end, in list view, I have a bank account summary field for each of the 5 bank accounts.

For example, if there is a debit in the Euro account, then that is reflected in the Euro account summary, however the other account summaries alongside it, should remain as they were in the previous record.

I'm having trouble making the relationship work, so that the credit or debit is only taken into account when it should be ... i.e. for that particular bank account.

Does this make sense?

Any suggestions will be much appreciated and will aid in my quest to stop my brain from smoking.

Grant

[transpower]

Your Relationships Graph should show a connection between bankID_pk (the bank table) to bankID_fk (the debit/credit table).

Each debit/credit record will have a bankID (value list by field). The debit/credit table will have one summary field for each bank. Put this summary field on the layout of the bank table.

[GrantSymon]

Hi Transpower,

thanks for the reply.

> Your Relationships Graph should show a connection between bankID_pk (the bank table) to bankID_fk (the debit/credit table).

Yup ... that's what I've got. I've used the account name with the year date (e.g. Euro 2004)

> Each debit/credit record will have a bankID (value list by field). The debit/credit table will have one summary field for each bank. Put this summary field on the layout of the bank table.

I have also done this, although, the summary field is in the Credit/Debit table, because that's where I will generate the reports ... there are virtually no records in the bank table, just the one per account, per year.

I must be missing something ... because this doesn't work. The relationship works, but it's not filtering out the other accounts. Hmmm.

[transpower]

For the summary field, I don't think you want the "running total." Try it with that box unchecked. Then with this field in the layout of the bank table, you should see the totals for each bank.

[GrantSymon]

Thanks again for the reply

I'm probably not explaining myself very well.

The running summary is what I want, not a Total. The problem I'm having is not with getting a running total, rather it's with doing it for 5 separate bank accounts. I'll try to lay it out graphically for 3 bank accounts (in the interests of simplicity!)

This is what I have in the PaBank Table. I've tried having a single record with 3 fields and 3 records with 1 field. I have the same problem both ways.

____________________________________________________________________________

Position at Bank January 1st 2003 :

Euro........... USD............GBP

1,000........2,000..........3,000

____________________________________________________________________________

Next, some example records in the Credit/Debit Table :

Credit/Debit..........Amount.........Account......PaBankEuro....PaBankUSD.....PaBankGBP

_________________________________________________________________________________

..... Debit ................200..............Euros..............800.............2000..............3000

......Debit................1000..............USD...............800.............1000..............3000

......Debit..................500..............GBP................800.............1000..............2500

......Credit.................750..............Euros...........1550.............1000..............2500

......Credit...............2000..............Euros............3550............1000..............2500

Is this any more clear?

The problem I'm having is in making the relationships work, so that the credit and debit amounts are only applied to *one* relationship and not all of them at the same time.

Can you make any more suggestions about how to go about this?

Many thanks,

Grant

[transpower]

OK, try the GetSummary function, with the breakfield set on bankID.

[GrantSymon]

Hi,

thanks again for the help.

Sadly this isn't what I'm after ... As far as I can see, this will only return a final total and not a running total, which is what I want.

I'm sure that this is blindingly simple, but I can't get the relationships to be 'restrictive'.

Grant

[transpower]

OK, Grant, upon rereading the above posts, it's clearer. Have fields on the debit/credit record for the bank account values at the time of this debit/credit--do a lookup for the bank account values. The new debit/credit amount will just change one of them, which could be posted by script at the time the record is committed (with the particular bank value updated on the current record). Then in Table View, you'll see how the various values change as the year progresses.