trailing grand summary using fields from another database

[JoW]

Can anyone tell me if it is possible to create a summary of a field in a database where the field is linked from another database. I basically want to be able to create a running total in the grand summary area but am struggling with the field in question being from another database. I can get it to total for one record but I want to get the grand total for all the records searched. I have had to create the field using a calculation but as I say it isn't giving me a running total just a total for the one record. I hope that made sense and someone smart can help me. Many thanks Jo

[bcooney]

Hi Jo, welcome to the Forum!

Summary fields (type summary) total record values across the current found set of the same table. If placed in a subsummary part, they will display the sum of the field by the break field. If placed in a grand summary part, they will display a grand total of the field across all the found records.

If you have two related tables, you can use:

sum (relationship::numberfield) to calc a total.

hth,

Barbara

[JoW]

Hi Barabara,

Many thanks for your welcome and information.

The calculation you mention is the one I have, with the field in the trailing grand summary area, but it only uses the information from the last record in the search. I have tried it with a 'leading summary' too and that uses the information from just the first record in the search.

I wasn't sure whether you just couldn't do this via a realationship, but that is obviously not the case as you say the same calculation as I tried.

Anymore bright ideas pleaaase?

I may resort to starting to build the database again and see if that works, just only really want to do that as a last resort! ;-)

Many thanks for your help

Jo

[bcooney]

If you are using the Sum ( ) as I describe above, placing it in a summary part does nothing!

How about you tell us a bit about your structure and describe the report you're trying to create?

[JoW]

Hi Barbara,

What I am after doing is.....

Take some figures (but not all) from one database (Nos 1.) those figures are like costs in and costs out on job specific items, and pull those into another database (Nos 2).

Database Nos 2, has further records in it that are not job specific. I then want to be able to total up figures within database Nos 2. for example be able to total up VAT - costs - expenses etc, over a searched period of time. But as a lot of items in Database Nos. 2 are not job specific I don't want them in Database 1 ideally.

Perhaps I have to put everything in Database Nos 1...?

I hope that made sense - I thought the trailing grand summary option would have solved the problem, but I guess it doesn't work through a relationship.

I appreciate your persistance with this for me Barbara - look forward to hearing from you again.

Jo

[bcooney]

Well, that was a bit more, but not specific enough. Can you list the relevant tables and their relationships. Why do you have two FM files (databases)? Why not one file, and multiple tables?

Sounds like your structure for the Job and its costs is correct. You could create a field in Jobs that sums the other costs, using a multi-predicate relationship that filters the other costs by a data range (the startdate and enddates for the filter on the parent side could be global fields).

[JoW]

Happy to use tables but doesn't that gives me the same problem with the grand summary not working.

My database (1) was built to manage jobs through to conclusion, so there are costs in against a job and then the cost out to client are all on one record. Because the costs in and out have to be itemised sometimes and have a description with them too I have done this part of the record as repeating fields. These itemised costs are always different. The itemised fields are then used on the invoice to client, so I don't have to double entry etc. the invoice is generated in another database that generates the next invoices number and the relevant information is copied over from Database (1) - that has all worked OK for a number of years.

However I am now wanting to add some extra functionality to the system, hence me becoming a bit unstuck. I want to persevere however as it seams crazy logging and double entrying the following information in other systems.

So far we have talked about Database (1) being job specific - I am now wanting to be able to log all costs/purchases that may not be job specific and I am sure there must be a way of logging, say petrol receipts in a list in one record rather than having a new record for each receipt, I will have a lot of records if not!

Once I have logged all this information (hopefully!) I want to be able to have a report that gives me total spend/purchase cost etc over a given time - I can sort the search but it is how I do the running total or the searched records.

I then have another big catch....... once I have run this report (if you can figure out a solution ;-)) I need to be able to export it to 'excel' itemised and ideally with totals. I think this is going to be a problem because of the repeating fields?

Could I just be trying to do too much?

[bcooney]

The repeating fields are a mistake, and separating the system out into separate files is a mistake. I don't have time right now for more detail, but quickly what you want is to add expenses to a table and then assign those to a job. Also, I created an invoice layout that let me pick from the expenses for that job, and assign them to the invoice (I even let expenses split across invoices). Expenses had types ( such as timesheets, materials). Expenses could also be added that were not assigned to a job.

Why export to Excel? Comfort-level? It can be done, but the report can easily come from the Expenses table.

[JoW]

The excel requirement is not mine it is for an external body. I will be keeping an eye on things day to day and use the system for information myself but I then have to export elsewhere periodically.

Thankyou for your reply - it would be really useful to hear more when you have time.

Many thanks Jo