Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-12-2019, 10:28 AM   #1
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
I need to know at list if this is possible

I need to view a report between two dates and using parameters in my query does not work because I have a running sum.
Ex.: start value=100; total for day1 =30; end of day1 =100+30; total for day2 = 17; end of day2 =130+17; total for day3=2; end of day3=147+2 etc. Obviously, end of day3 is correct only if I start from the beginning.
I have tried until now to "filter" only the dates in the report (where condition) but I begin to have doubts that this is the path to take .
I feel like there must be another solution but I can't see it . Can someone point me in the right direction?

senseidan is offline   Reply With Quote
Old 01-12-2019, 10:57 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: I need to know at list if this is possible

Hi. Perhaps you could add an unbound control to your report to calculate the beginning balance for your date range. You can then use this value for each day included in your report.
__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
Old 01-12-2019, 11:50 AM   #3
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: I need to know at list if this is possible

Hello theDBguy. I have such control, problem is that still I need all previous data to calculate. Maybe if I write every day result in a table and use it from thereÖ that could be a solution. I hope however that I can do what I need in a more straightforward way.

senseidan is offline   Reply With Quote
Old 01-12-2019, 12:38 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,133
Thanks: 25
Thanked 551 Times in 539 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: I need to know at list if this is possible

Right. I was thinking the unbound textbox would contain a calculation from all previous records; hence, the idea to call it "beginning balance." Have you tried using DSum()?
__________________
Just my 2 cents...

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.
theDBguy is online now   Reply With Quote
Old 01-12-2019, 11:18 PM   #5
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,628
Thanks: 2
Thanked 2,049 Times in 2,004 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: I need to know at list if this is possible

Show how you want the report and some example data.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 01-13-2019, 04:03 AM   #6
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: I need to know at list if this is possible

for JHB: I will describe the situation as clear as I can. I have a Table with 3 columns: Date, Income, Payments. I have a report for every day which show the sum of Payments for that day, the sum of Income for that day, the sum of Payments until that day, the sum of Income until that day and a total which consists in sumOfIncomeUD-sumOfPaymentsUD(UD=until that day). This is what I already have. Now, I need to view this report between 2 dates, similar to a query with 2 parameters: startDate, endDate. If I use a query, it will cut all my payments/income before of startDate and, consequently, will ruin sumOfIncome/PaymentsUD.
ex:
day1: income=1,2,3 ;payment:1
day1: sumOfIncome=6 ;sumOfPayment:1
day1: sumOfIncomeUD=6 ;sumOfPaymentUD:1
TOTAL day1=5

day2: income=4,5,6 ;payment:3,2
day2: sumOfIncome=15 ;sumOfPayment:5
day2: sumOfIncomeUD=21 ;sumOfPaymentUD:6
TOTAL day2=15

day3: income=10,20,30 ;payment:15
day3: sumOfIncome=60 ;sumOfPayment:15
day3: sumOfIncomeUD=81 sumOfPaymentUD:21
TOTAL day3=60

PROBLEM- if I want to view only day 2 and 3:

day2: sumOfIncomeUD=15 ;sumOfPaymentUD:5
TOTAL day2=10

day3: sumOfIncomeUD=75 sumOfPaymentUD:20
TOTAL day3=55
senseidan is offline   Reply With Quote
Old 01-13-2019, 04:17 AM   #7
senseidan
Newly Registered User
 
Join Date: Jan 2018
Posts: 38
Thanks: 13
Thanked 0 Times in 0 Posts
senseidan is on a distinguished road
Re: I need to know at list if this is possible

to theDBguy: Sure thing, DSum() was my first choice but, if I remember well, I found that is somehow slow even for not so big sets of data (2000 -3000records) Anyway, my report is working very well as it is but what is showing is correct only if it runs from the beginning(day1).

senseidan is offline   Reply With Quote
Old 01-13-2019, 11:43 PM   #8
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,628
Thanks: 2
Thanked 2,049 Times in 2,004 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: I need to know at list if this is possible

If you've a query that gives a correct result, why do you not pick out the days (date) you want from that query?

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   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
bounded list box query on continuous form shows repeated data for all list boxes GregChav Forms 3 08-16-2012 11:00 AM
Value List Table vs. Static List for Drop-Downs dkinley Theory and practice of database design 7 08-01-2008 11:32 AM
dynamic selecton of data in a list box on the basis of another list box in MS Access afsheen Forms 1 04-13-2008 06:02 AM
Combo populated from value list (browse directory) -- Only want to sort list. Jibbadiah Modules & VBA 0 08-18-2006 02:23 AM
How to display filtered records list of the form in the list box? asiamisia Modules & VBA 2 08-08-2004 11:23 PM




All times are GMT -8. The time now is 07:32 PM.


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