Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2019, 03:36 AM   #16
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Running Total

Stage 1 of the mission complete.
Because a few products can be used in the same production batch it obviously gives them the same Timestamp.
My REALLY UGLY work around was to use the GoodsID number and turn that into seconds. Then add that to the Timestamp. This meant every Timestamp in production a different Time by a second or two.
It then worked perfectly.
I know you all have your head in your hands cursing me, but... It worked.

itownson1 is offline   Reply With Quote
Old 11-05-2019, 03:45 AM   #17
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Running Total

Creative , ut as long as it works. Well done.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 11-05-2019, 03:54 AM   #18
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Running Total

Don't go away Gasman, we have stage 2 - stage 20,000 to complete yet!!

itownson1 is offline   Reply With Quote
Old 11-05-2019, 04:51 AM   #19
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Running Total

Stage 2!!! Do I put this under a new topic or is it ok here?

How do I group the query with a running sum. When I filter the data by product, it still running sums the entire datasheet rather than what I am filtering.
itownson1 is offline   Reply With Quote
Old 11-05-2019, 05:09 AM   #20
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Running Total

Include the product in the summing criteria?, however I'm not sure you will get a running sum now, as you will only get one entry per product, so a simple sum will do that.?
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 11-05-2019, 05:16 AM   #21
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Running Total

It is only sample data I have put in, so can add more data to get the running total.

Where would I add the "product" to the summing criteria for it to group by "product"?

RunningSum: Sum((Select Sum([QtyAdjusted]) from [QCombinedInventory] as a where a.[Timeadjust]<=[QCombinedInventory].[Timeadjust]))
itownson1 is offline   Reply With Quote
Old 11-05-2019, 05:33 AM   #22
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Running Total

As I mentioned, if you group the data, I do not think you will ever get a running sum as there will only ever be one record per group, unless you group by other fields, but then a running total is not really going to make any sense, to me at least.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 11-05-2019, 05:35 AM   #23
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Running Total

Ok, Just to throw an absolute curve ball into the mix here.

I have realised I can do a running sum in a report that will work with a filter in the query.
The only issue is it doesn't work with [Qty] but will work with every other field such as GoodsID.

Why does Access hate me so much?!?!?
itownson1 is offline   Reply With Quote
Old 11-05-2019, 05:38 AM   #24
itownson1
Newly Registered User
 
Join Date: Mar 2019
Posts: 42
Thanks: 3
Thanked 0 Times in 0 Posts
itownson1 is on a distinguished road
Re: Running Total

I don't understand why there would be one record per group.
If I group by product that product would have numerous "Goods In" and "Production" data to create a running sum.
At the moment I have it working for all products, but I want to be able to filter it to one specific product to check on the stock.
itownson1 is offline   Reply With Quote
Old 11-05-2019, 05:39 AM   #25
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,332
Thanks: 432
Thanked 790 Times in 766 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Running Total

I can only guess that Qty is somehow defined as text/date, anything that cannot be summed.?

Upload a sample DB.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Self-Referencing running total used to calculate next total in a query xav78 Queries 9 07-24-2015 12:52 PM
Need hoursWorked from a query - sum,total,running total? helmerr Queries 19 02-06-2014 11:56 AM
Running Total Edgarr8 Queries 1 08-04-2009 04:23 PM
Running total spoole Queries 19 01-25-2008 03:07 AM
[SOLVED] HELP! running total Barry Collins Queries 3 02-06-2002 04:41 AM




All times are GMT -8. The time now is 08:03 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World