UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Bi-weekly Query, Access 2016    
 
   
ordnance1
post Aug 7 2019, 11:42 AM
Post#1



Posts: 667
Joined: 7-May 11



I have a Profit/Loss table, shown below, and I would like to have a bi-weekly query so I can smooth out my chart. With payday every other weeks it causes peaks and valleys that I feel are not truly representative.


Any thoughts on how this might be done?



Attached File  Untitled_picture.png ( 309.65K )Number of downloads: 8


Go to the top of the page
 
theDBguy
post Aug 7 2019, 12:04 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,315
Joined: 19-June 07
From: SunnySandyEggo


Hi. I am not sure I understand your request. Are you able to post a mockup of the result you want out of the table? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Aug 7 2019, 12:35 PM
Post#3



Posts: 3,296
Joined: 27-February 09



either a calculation to determine the biweekly grouping or a calendar table, and then join that to this table, and sum & group?
Go to the top of the page
 
ordnance1
post Aug 7 2019, 12:48 PM
Post#4



Posts: 667
Joined: 7-May 11



Thank you for taking the time to reply. So what this would do is sum Report_ID 7 & 8, 9 & 10, 13 & 14, 15 & 16 etc..


Attached File  Untitled_picture.png ( 309.65K )Number of downloads: 4
Go to the top of the page
 
MadPiet
post Aug 7 2019, 12:58 PM
Post#5



Posts: 3,296
Joined: 27-February 09



GrpCalc: (1+[ReportID])\2

Integer division by two (\2) so will round down. Adding 1 just makes the groups work right.

Then you'd Group on the calculation.
Go to the top of the page
 
kfield7
post Aug 7 2019, 03:13 PM
Post#6



Posts: 992
Joined: 12-November 03
From: Iowa Lot


Is Report_ID autonumber? If so, you can't rely on that for bi-weekly.
A true bi-weekly report (or any date-dependent interval) should be based on the date.
You would then need a function that determines the week of the year, and divides that by 2 to determine the bi-week, then do your grouping on that result.

Pick a starting year, say 2000.

WeekNumSince2000: DateDiff("ww",DateValue("1/1/2000"),[StartDate])

BiWeek: int(DateDiff("ww",DateValue("1/1/2000"),[StartDate])/2)
or
BiWeek:int([WeekNumSince2000]/2)

Add 1 (as per MadPiet) if you need to adjust your week alignment.

or, if your Bi-Week starts over each Jan, you can just do int(DatePart ("ww",[StartDate])/2), but include a grouping on Year([StartDate]).
This post has been edited by kfield7: Aug 7 2019, 03:23 PM
Go to the top of the page
 
RJD
post Aug 7 2019, 03:15 PM
Post#7


UtterAccess VIP
Posts: 10,019
Joined: 25-October 10
From: Gulf South USA


Hi: I agree with Pieter (good approach), IF the Report_ID groups always start with an odd number, and the sequence is always 1. Can you confirm this - or might the IDs sometimes be different than this?

Added: I just saw kfield7's response above and that led to another thought. If you cannot absolutely rely on the conditions I mentioned, you might use, as an alternative, a grouping table with from and to dates along with a grouping number. Just a thought ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Aug 7 2019, 03:25 PM
Post#8



Posts: 3,296
Joined: 27-February 09



I was going to say "use a Calendar table!", but that seemed a bit over the top. The cool thing is that it solves WAY more than just this problem. And even 20 years worth of days isn't that big a table.
Go to the top of the page
 
RJD
post Aug 7 2019, 03:30 PM
Post#9


UtterAccess VIP
Posts: 10,019
Joined: 25-October 10
From: Gulf South USA


Pieter - on the calendar table - as we both mentioned. Building this in Excel should be extremely easy, by making the first couple of records and pulling them down, then importing into Excel. (Makes 62 records from the 8/27/2017 record to the end of 2019.)

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 01:59 PM