Calculating totals in a report

[Jamescdawson]

Hello again folks,

I have created a query that includes an expression which calculates Total weights from multiple records in a table. The data is grouped and totaled according to the customer names so that my query is effectively a simple list of customer names in Column 1 and the relevant total weight for that customer in Column 2. The source data for the customer names is held in a CustomerDetails table. The transaction/weights data is held in a separate table only for these transactions. All good. I can view my totals in the query.

Now I have created a report which lists each customer's individual transaction data on a line by line basis. One customer per sheet. The report therefore looks like an invoice or statement. The customer details come from the relationship to the CustomerDetails table and the transaction/weight data comes from the transaction table directly, I'd like to use the calculated total from the Query and drop it onto the bottom of the report showing the total weight received for that customer.

I've tried a variety of expressions and references to the column in the query, but the best I get is a < #Name? > returned. Can anyone point me in the right direction.



Grateful for any suggestions...

Jimbo

[pbaldy]

You shouldn't need that query. A textbox in the group or report footer of:

=Sum(FieldName)

should provide the total of the transactions on the report. If for some reason you need the query value, you'd use DLookup():

DLookup Usage Samples

[Jamescdawson]

Hi again Paul,

Thanks for your suggestions.

I may have given a misleadingly simplistic outline. It would take lots of text to describe fully, so have a look here>>>
https://www.accessforums.net/attachm...0&d=1339192056 I uploaded this earlier.

Please do bear in mind, I'm not a coder/programmer and a relatively new Access user and it therefore may not be as elegant as it should be. However, this was the only way I could see to build it at the time.

It shows the Data entry "grid" that is used to enter each incoming consignment onto the db and ultimately builds the table on which the reports are based. The top part (in light blue) defines the customer and many of the fields are auto filled from the customer file when the customer is selected from the drown down list in the "Customer" combo box.

The bottom (in dark blue) was created to make it simple for the data entry person and mimics the government paperwok we are obliged to use (i.e. 10 spaces into which individual elements are entered. The user selects a description from a drop down list of about 30 descriptions and again most of the line is autofilled form another table. The key element is the weight in column 3 of the grid. This is what I have to total. Sometimes there is only one line completed. Other times, many lines are completed and over the month, there can be three of four consignment from that customer. These weights must added to give a total consignment weight at the bottom of the monthly/quarterly reports..

The reports consist of all COMPLETED lines in each consignment and I need a total at the end of each group, so I don't think I can use =Sum(fieldName) because the report can consist of a variable number of lines.... e.g. 4 x weight1; 3 x weight2; 2 x weight3 etc.

Having created the query to sum all the weight1; weight2; weight3 etc and built an expression add them all together in a grand total, I think I need to get at that Grandt total in my query and drop it into a text box at the bottom of the report.

What do you think ?

Jimbo

[pbaldy]

What do you think ?

I think you have made the normalization mistake of having repeating fields.

Seriously, in a properly normalized database the data you have in the detail section would be in a separate table, related one-to-many to the existing table. An analogy would be a standard product sales application. You'd have an order "header" table, with the basic info like customer, date, etc. In an order "details" table related by order number you'd have the products purchased on that order, where every product was a separate record. That let's me sell one product or 100, and the application dynamically adapts to it. What are you going to do when somebody has 11?

To stay with what you're doing, you'd have a textbox like:

=Weight1 + Weight2 + ...

probably using the Nz() around each because of the potential Null values.

[Jamescdawson]

Paul,

Thanks for the prompt analysis and reply .... I had a sneeky suspicious the repeating fields could be done in a better manner and that you were going to tell me that ! When I go for the major re-build, I'll re-look at that part of the system and attempt to build in the manner you suggest.

If we get a consignment with 11 elements, we'll have two consignment records (replicating what happens at the moment on paper.)

I used the Query builder to sum all Weight1s, Weight2s etc and then added the expression to sum the sums. Wrong ?

The total at the bottom of the input form on each consignment uses the =Nz(weight1)+Nz(weight2)....

I've got all the math, but cant get the result onto the report.

I'll re-think and try to find a way to add the totals on the form

Thanks again for your input. I know the assistance is freely given and appreciate it very much.

Regards

=Jimbo=

[pbaldy]

Unless I misunderstand, summing the Weight1's would be like summing the transactions of different orders. Doesn't sound appropriate. Can you post the db here?

[Jamescdawson]

Yes Paul, That's right. It sounds odd, but the critical requirement here is that we need a total weight for each customer for a defined period of time.

Let me try one more pitch of the core requirement before I post the db. (I'd prefer not to - I'm sure you'd laugh !)

I do need to keep records of individual consignment total weight, and the nature of the item or component representing that weight... but we've got that (as represented by the data input form I posted earlier - supported by its underlying table). The rows/fields in the grid on the data entry form captures all the required data and the consignment total weight calculation appears at the bottom of that form. All Good.

But also....I need a grand total weight (comprising all the weights in all the rows in all the consignments that that customer sent to us in a particular period.) That grand total is fed back to the client at the bottom of a report listing each and every consignment and each and every component. So the customer gets a well laid out report listing all component rows grouped in date and consignment number order. (I've done that and it's good - just missing the total weight.

By way of example, If client A sends us four consignments during the period, then I need to add all the weight1s, weight2s weight3s etc together (taking Null values into account) for all four consignments and have just one figure. The nature of the item with that weight is irrelevant in terms of calculating and feeding back the total weight.

I do understand the concept of One-to-Many repesented by your example of product sales application, but I thought my grid to facilitate easy data entry would work (until I stumbled with the reporting requirement.)

If on the other hand, I go for summing weights 1 - 10 in each consignment, and then use these totals to arrive at my required grant total, I would need to store the consignment totals in the table, but I understand from other forum advice that storing calcs is not good practice (?) and I haven't been able to post that consignment total to the table anyway (!) So the alternative to all the angst described above, is for me to find the right way to SUM all the consignment totals which appear as a calc at the bottom ofthe data input form.

Phew ! There you have it.

I need Scotch whisky and my bed !

[pbaldy]

If you're going to have Scotch whiskey, the least you can do is share!

As I mentioned earlier, if you have a query that returns the appropriate amount, you can use a DLookup() on the report to grab the value from it.

[Jamescdawson]

Hi Paul,

If you're going to have Scotch whiskey, the least you can do is share!

You got there before me, but I promise you, If I crack this with your assistance tonight, there's a bottle of finest Scotch Single Malt on its way to you...and by the way, Scotch doesn't have a "e" in Whisky. It's the Irish, Canadian and American stuff that has the "e" in whiskey... You need to work on getting your syntax right !!

Now.. in regard to syntax, I can see that Dlookup IS the way to go but I can't get it to work... I've looked at numerous examples on line today but I think I'm screwing it up on the criteria syntax

Can you help?

I created a text box on the report to present the total and into the control source for the text box I put

DLookUp("[Totals]" , "[Qtytotals]" , "[Customer] = Report![Customer Name]")

Totals = the field Name in the Query that contains the figure I need in the report.
Qtytotals = The name I've given to the Query - which contains a column headed "Customer" and a column headed "Totals"
Customer = The field in the Query that I want to match to the customer name in the report.
Customer Name = the filed in the report (Yes, I know that I didn't use proper naming convention here. There's a space between Customer and Name in the name of the field)

I'm trying say here.... Insert the content of the Totals field from the QtyTotals Query into the text box when the customer name in the Query matches the customer name in the Report.

The customer name field on the report is populated from the Customer details table.

Unfortunately, it doesn't return any figures in the text box

I've also tried this expression on my Data entry form, substituting the word Report for Form, but this only returns the #Name? prompt.

Any final suggestions ?

Jimbo (trying hard)

As I mentioned earlier, if you have a query that returns the appropriate amount, you can use a DLookup() on the report to grab the value from it.[/QUOTE]

[pbaldy]

Try this (I'm assuming the Customer field is text):

=DLookUp("[Totals]" , "[Qtytotals]" , "[Customer] = '" & [Customer Name] & "'")

Also make sure you don't have a textbox named "Customer Name", which can often confuse Access when you create a formula (pet peeve, since the wizard creates just that).

Blame the spelling error on the spell checker in Firefox; it didn't like your spelling.

My daughter lives in England and they did a Scotch Whisky tasting during a trip to Edinburgh. She liked it...a lot.

[Jamescdawson]

Paul,

Regret no success on either the report or the form.

to confirm... the 3 quote marks together in the criteria are Single then Double and the final 5 quote marks are Double single Double.

Not sure I understand your comment about a text box called Customer Name. I have such a text box on the report containing... strangely enough, The customer name.

I also had a (redundant) text field set up on the table in which the data is stored called Customer Name which I have now deleted.

I've tried checked the syntax and changed a few things things, but no joy.

=Jimbo=

[pbaldy]

If the name of the textbox is "Customer Name", the same as the field name, it can cause Access to get confused about which you mean and the formula can error. Try changing the textbox name to "txtCustomerName" and see if the formula works.

[Jamescdawson]

Somehow, My Query (QTYTOTAL) has also now gone wrong now and it has stopped working !!

I'm now being asked to enter parameter values when I run it.

Arggh

[pbaldy]

The parameter prompt is Access saying it can't find something (the prompt should tell you what it is). Typically either a form the query relies on isn't open or something is misspelled.

[Jamescdawson]

Not getting this. The Text box on the report called Customer Name displays the customer name which it picks up from the underlying table. I have no other fields or text boxes called Customer Name, so I'm not sure what you are suggesting I change.

I'll have another play around with the Dlookup tomorrow morning. It's 11pm here now,and I have an grumpy wife...

Perhaps we can pick up tomorrow (assuming this isn't becoming too tedius)

I was serious about the single malt too

Best Regards and Thanks

=Jimbo=