Access 2010 - Totals Row Issue (1 Viewer)

[Porisius]

I've been banging my head against this one, but I have a query that I setup for inventory use.

In that query, some fields use a formula. I, then, added a Totals row to it.

I have a few, like the one shown below, that I set to Sum, but it won't show it. It will show Average/Min/Max/etc, but it will only not show the sum. It is setup to be a currency field with a fixed decimal of 2.

Inventory_Dollar_Actual: [Stock]*[Unit_Cost]

I don't know if it matters, but Unit_Cost is an actual field from a table while Stock is "Stock: IIf(IsNull([SumOfStock]),0,[SumOfStock])"

It will, in each record, show the correct value for that record. So, I have no clue what is going on. Any help would be appericated.

Thanks,
Chris

 

[KenHigg]

I'm thinking you need to nz() the starting values to force 0's so you won't have to try and null test stuff. I think that is the problem.

 

[Porisius]

Ken,

It was there on a couple of them, then I switch to Design View to try to get the others to work and somehow it said, "Screw you, I ain't working..." Sending SQL script for the query. Some fields I did a little different because I hate that LastOf or SumOf in the title... It messes me up, so I got rid of it those.

SELECT 
 
qryMasterGoals.ArticleNum, 
qryMasterGoals.LastofType AS Type, 
qryMasterGoals.LastofDescription AS Description, 
qryMasterGoals.LastofPlanner AS Planner, 
qryMasterGoals.LastOfParentPartNum AS ParentPartNum, 
qryMasterGoals.LastOfParentPartDesc AS ParentPartDesc, 
qryMasterGoals.LastOfBillMatQty AS BillMatQty, 
qryMasterGoals.LastOfUnit AS Unit, 
qryMasterGoals.SumOfAnnual_Demand AS Annual_Demand, 
qryMasterGoals.LastOfSupplier AS Supplier, 
Nz([dohtarget],0) AS DaysonHand_Target, 
[Stock Groups].Class, 
[Stock Groups]![lastofstockgroup] AS Stock_Group, 
qryMasterGoals.LastOfCustomer_Group AS Customer_Group, 
Nz([LastOfUnit_Cost],0) AS Unit_Cost, 
Nz([Annual_Demand]/240) AS Daily_Demand, 
Nz([SumOfStock],0) AS Stock, 
[Daily_Demand]*[DaysonHand_Target] AS Inventory_Qty_Goal, 
[Stock]-[Inventory_Qty_Goal] AS Inventory_Qty_Variance, 
[Inventory_Qty_Goal]*[Unit_Cost] AS Inventory_Dollar_Goal,
[Stock]*[Unit_Cost] AS Inventory_Dollar_Actual,
[Inventory_Dollar_Actual]-[Inventory_Dollar_Goal] AS Inventory_Dollar_Variance, 
[Stock]/[Daily_Demand] AS Inventory_DaysOnHand_Actual, 
[Inventory_DaysOnHand_Actual]-[DaysonHand_Target] AS Inventory_DaysOnHand_Variance
 
FROM Supply 
RIGHT JOIN ((qryMasterGoals 
LEFT JOIN qryInventory ON qryMasterGoals.ArticleNum = qryInventory.ArticleNum) 
LEFT JOIN [Stock Groups] ON qryMasterGoals.LastOfStock_Group = [Stock Groups].lastofstockgroup) ON Supply.Source = qryMasterGoals.LastOfSupplier;

I am going to keep going, but figured it wouldn't hurt to have a second pair of eyes.

Thanks,
Chris

 

[Porisius]

Correction,
Nz([Annual_Demand],0)/240 AS Daily_Demand,

Error that I saw... I don't know if it will have an effect or not

 

[Porisius]

Same issue... Got Average/Min/Max, no Sum...

 

[Porisius]

I tried just making a new query for the totals, and I get an error message "Division by Zero" How, I don't know, but it does give me a lead...

 

[KenHigg]

So you have a query that has as the last row a columns total?

5    100   500
3     98   294
6    110   660
--------------
          1454

 

[Porisius]

Yes, for whatever brilliant idea, they have the same part number for different types.

In their old DB, they imported 2 excel files, used a MakeTable query to do what my 2 SelectTable queries do now. Then used a second accdb to run reports and charts. So, I am combining the two, plus some of the tables were in German... So, I am overhauling their system, again...

I do feel like someone from the Jetsons hanging out with the Flintstones sometimes... haha, well, I can't insult Wilma, she, at least, had common sense.

I do know one way to fix it, but I don't think they would like that too much... but it is tempting to say "screw this" and go home to a nice cold beer...

 

[KenHigg]

I have never used column totals in a query, I always do that in the form or report. Is that a option?

 

[boblarson]

Why are you using LAST in the first query? First and Last don't really do what they say. They do, in fact, return a random selection. Are you thinking it gets the totals row? My suggestion, if you are going to be using a query in something else and it is not the final destination is TURN OFF THE TOTALS ROW. It is only going to cause problems. (another one of Microsoft's fantastic ideas which seems like a good thing until you try to use it).

 

[KenHigg]

So Bob, He's saying the row total calculation is working but it won't total the total column?

 

[Porisius]

I think I am going to go with Bob's suggestion and ditch the First/Last. For inventory purposes, it shouldn't be used... then again, they should be using multiple part numbers. I appericate both of your efforts, and yeah, Bob, I've been working with Microsoft since DOS 3.3. They come up with great intentions, but some just don't work out...

I did it because they had it setup that way, and I was mirroring it... Guess I was the "Fool Who Followed Him"...

Again, thanks to you both...

Chris

 

[boblarson]

So Bob, He's saying the row total calculation is working but it won't total the total column?

The OP said Totals Row in their first post.

In that query, some fields use a formula. I, then, added a Totals row to it.

And, from the use of LAST I am thinking that is the case but would be good to see the original query as well.

Totals Rows, as far as I know, are only good in the item you have open. I've not seen the ability to use them in something else. I could be wrong but in this case it would be just ignore the totals rows and do the totals in the sum/grouping.

 

[KenHigg]

You're welcome!

 

[boblarson]

They come up with great intentions, but some just don't work out...

They seem to be coming out with more and more of those ideas which, on the surface, seem to be a great idea but then end up causing more pain and suffering from everyone, including the new people who use it, experienced people who try to use it and fail, and the people who work at answering questions on the forums and have to tell people to abandon them. And the main issue I have with it all is that the MVP's have continually told them (the Access Dev Team at Microsoft) to NOT DO IT but they keep doing it anyway because they aren't interested in making it a good program for development and people who do it for a living. They want to get more people to buy it (which means new people buying and then getting disillusioned when they can't do what they thought they were going to be able to do).

 

[KenHigg]

Marketing must tell them they get good press by touting the qty of all the new features, regardless how good they are.

 

[Porisius]

Good ol' Micro-Shaft... and there's nothing Micro about the Shafting, too...

I got into programming a few years ago, so I am still considered new by most programmers, (I'm fine with that). I have been building/repairing PCs since I was 6, now 27. So, yeah, I know... I know... at least some people are willing to say "HEY DUMBASS", too bad they don't listen... If you are ever in Sumter, SC, look for a tall guy who limps around... I'll buy you guys a round...

 

[boblarson]

Marketing must tell them they get good press by touting the qty of all the new features, regardless how good they are.

That is something I can believe. And based on the conversation at the last MVP Summit this past February, I would say you are pretty close.

 

[KenHigg]

Not to hijack the thread, but did you see the feeble Win 8 / surface launch video Balmer did - ? Jeeze, he needs to give it to some younger guys...

 

[boblarson]

Not to hijack the thread, but did you see the feeble Win 8 / surface launch video Balmer did - ? Jeeze, he needs to give it to some younger guys...

No, I don't like Win8 and am not going there unless forced.