One cell in tbl 1 refers to sum of cells in tbl2

[Keeperen]

Hey,

I'm new at this and do not know if I posted this in the right toppic.

Anyway I have an invoice where the total cost cell is the sum of a number of cells in another tbl.

Example:
Man hours 200£
Plasterboard 200£
Administration fee 10£

Total cost: 410£

In other words with each invoice total cost there can be many lines in another table I need to sum up and make the connection to the invoice.

How do I do that ?

Thanks

Keeperen

 

[plog]

You do this in an aggregate query (http://www.599cd.com/tips/access/aggregate-query/) and you do not store the total in a table--you run that query everytime you want the total.

If you need more specific help, post the name of your table and the relevant fields of each table.

 

[Keeperen]

Hello,

Have tried to do what you described, but I cant seem to get it up and running.

In the tbl T_aftaleseddel this i a columb called Faktureret beløb these are the cells that I want the summed up value of the sub tbl Tbl_Underark_aftaleseddel columb Salgspris within the different values of the columb Aftaleseddel_ID.

Eks: Aftaleseddel_ID value =25 (3 posts) will return the value 707,51.

As you can see I have made one query, but I do not know how to get the results from that query update in the target cell Faktureret beløb.

How do I do that ?

Thanks in advance.

Keeperen

 

[plog]

Put this as the control source of the field on the form in which you want the total to appear:


=DLookUp("SumOfSalgspris","Fore_tbl_underark_sum_aftaleseddel","[Aftaleseddel_ID]=" & [ID])

 

[Keeperen]

Hmm, I have tried and tried to finde a solution to the error that is typed in the field, where I typed in the Dlookup() command. It says #Navn?

How do I solve that ?

In advance thanks.

Keeperen

 

[RainLover]

I can see nothing wrong with storing the Invoice Value in the Invoice Table.

Much more efficient and would better satisfy an Audit.

 

[Keeperen]

So you are saying calculate the value in the tabel instead ?
But is that possible when you are talking about a tabel and a subtabel - one to many.

When I try to calculate the field in the invoice tabel I cannot seam to access the subtabel...

How do I do that ?

 

[RainLover]

Do you have a Main Form for the Invoice and a subForm for the Items.

If so then you add a Control on the Sub that Sums the values of the items. This is Unbound and not saved.

On the Main form for the Invoice you store the Summed value in the appropriate Control. Something like InvoiceTotal or Total. This is from a Field in the Invoice Table.

 

[Keeperen]

Still same error. Please see attached.

What do I do wrong ?

 

[RainLover]

I cannot read 2007. Can you rePost with Access 2003.

 

[Keeperen]

:-( I can't. It says that different features and calculations will be lost if I save my 2010 Access in a 2003 format.

But thanks anyway :-(

 

[RainLover]

Don't worry. Someone with 2007 will come along and help.

If they don't, create a new Database with just the bare essentials exported into it. You will be able to save that in 2003.