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
> How To Assign A Group To A Date As Per A Specific Date Period?, Access 2016    
 
   
thebaul
post Jun 18 2018, 01:01 PM
Post#1



Posts: 183
Joined: 31-May 13



Hello guys.
I want a group number to be assigned to a date based on the following criteria.
If the date is between:
1 - 1st April to 14th June
2 - 15th June to 14th Sept
3 - 15th Sept to 14th Dec
4 - 15th Dec to 14th March
5 - 15th March to 31st March

It would be great if I can use code directly in a query and not call the function from a module. Isn't calling the function from a module slower?
Go to the top of the page
 
theDBguy
post Jun 18 2018, 02:14 PM
Post#2


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


Re: "Isn't calling the function from a module slower?"

No slower than calling any other function in a query, I think. Even if it is slower, it may be unnoticeable because a slower function call could still be too fast for us to notice any difference. If you want absolute speed, avoid calling any function from anywhere. So, if you can store your groups in the table, then it would eliminate the need to call any function.

Just my 2 cents...
Go to the top of the page
 
thebaul
post Jun 18 2018, 02:53 PM
Post#3



Posts: 183
Joined: 31-May 13



Ok Thanks for your reply. I want to calculate the groups when I run the query.
Go to the top of the page
 
theDBguy
post Jun 18 2018, 04:31 PM
Post#4


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


Hi,

Quick question, is this rule for grouping fixed or is it possible to change over time? In any case, using a table might still be a better approach. You can "calculate" the groups by merely joining the tables.

Just a thought...
Go to the top of the page
 
thebaul
post Jun 18 2018, 05:07 PM
Post#5



Posts: 183
Joined: 31-May 13



It will be fixed, it would not change with time. There are 10-15 different tables and the date column in those tables would be used as input for the group function. I need it in a query only.
This post has been edited by thebaul: Jun 18 2018, 05:09 PM
Go to the top of the page
 
theDBguy
post Jun 18 2018, 07:14 PM
Post#6


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


Okay, if you don't want to create a new table for the groups, you could try creating a function to return the group. Doing all the calculations in the query could get complicated. If you have a custom function to assign the group, you can just call it in your query.

Just a thought...
Go to the top of the page
 
projecttoday
post Jun 19 2018, 03:36 AM
Post#7


UtterAccess VIP
Posts: 11,208
Joined: 10-February 04
From: South Charleston, WV


As a general rule ...

1. Code such as you would need, i. e. a Case statement or some Ifs, would likely run faster than reading a table stored on a disk (doesn't matter where the code is called from).
2. Tables are more "dynamic" than code (meaning easier to change)

But remember, a lookup on a very small table is likely to be very fast anyway. Using a function won't necessarily make your queries run noticeably faster.
Go to the top of the page
 
thebaul
post Jun 19 2018, 03:52 AM
Post#8



Posts: 183
Joined: 31-May 13



Yes I figured the code out using a bunch of if statements and put the code in a module. There is no perceptible speed decrease if any. Thanks.
Go to the top of the page
 
projecttoday
post Jun 19 2018, 10:12 AM
Post#9


UtterAccess VIP
Posts: 11,208
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

You tried it both ways?

I made a couple of changes to post #7, adding the word "likely" to point 1, since I can't be sure the code will run faster, and putting "noticeably" in italics in the last statement.
Go to the top of the page
 
projecttoday
post Jun 19 2018, 10:14 AM
Post#10


UtterAccess VIP
Posts: 11,208
Joined: 10-February 04
From: South Charleston, WV


Also, fyi: Case Statement
Go to the top of the page
 
JonSmith
post Jun 19 2018, 10:38 AM
Post#11


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



Late to the party but years ago I messed around with the partition function.
It doesnt natively work with dates so I made a wrapper around it to do so. I was focusing more on time slots, partitioning values times into 15 minute groupings (or 30 min or whatever).

Perhaps it can be updated to group by dates by months?
Go to the top of the page
 
projecttoday
post Jun 19 2018, 10:57 AM
Post#12


UtterAccess VIP
Posts: 11,208
Joined: 10-February 04
From: South Charleston, WV


What is the partition function?
Go to the top of the page
 
JonSmith
post Jun 19 2018, 04:46 PM
Post#13


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



See here Robert. Let me know if they explain it poorly and it doesnt make sense.

https://msdn.microsoft.com/en-us/VBA/langua...tition-function
Go to the top of the page
 
projecttoday
post Jun 19 2018, 09:29 PM
Post#14


UtterAccess VIP
Posts: 11,208
Joined: 10-February 04
From: South Charleston, WV


I see. Yes, why don't they show the result of their example? And a simple query would be better to demonstrate the usage than SELECT DISTINCTROW.

So how did you make Partition date-ready? Maybe you can post the code.

Of course, with a function, all thebaul has to do is put functionname(fieldname) in any query where those values are needed. And, contrary to what he/she initially thought, the performance will be fantastic. Of course, we like things table-driven, but it's hard to argue against a function like that if no future changes are likely. Or is it?
Go to the top of the page
 
JonSmith
post Jun 20 2018, 02:37 AM
Post#15


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



Yep, just needed access to a computer that has it on. Its been a while. I'd like to preface that this code is approximately 6 years old and early on to me learning to code, I think it holds up however.
So the output is for reporting purposes so its formatted as such.

If you use the code below you are for example asking the code to create 30 minute timeslots between 6am and 1pm and assign a slot to the value of 6:46.
In this example the result is '06:30 to 06:59'.
You can adjust it, so if you used 5 minute timeslots you'd get '06:45 to 06:49'

CODE
PartitionTime(#6:46:00 AM#, #6:00:00 AM#, #1:00:00 PM#, 30)



I haven't thought too deeply about it and figure that the issue of months would become a challenge since they aren't standard but since the 14th is a consistent partition value then I figure something is possible. You could assign a month based off of this, partition that and then go from there. The function would just need a the start of the year and then the end. It could then easily be tweaked around and used each year without any coding or table maintenance.



CODE
Public Function PartitionTime(dtInitialTime As Date, dtStartTime As Date, dtEndTime As Date, intMinInterval As Integer) As String
   'Author: Jon Smith
   'Purpose: Partition a time value by converting to an integer and then using the built in partition function.
   'See the Partition function for more details on how it works.
   '
   'Copywrite: Use and distribute freely, but please give credit where credit is due.
   '
   'Parameters
   ''''''''''''''
   'dtInitialTime: Time to be evaluated against the time ranges
   '
   'dtStartTime: Time that is the start of the time ranges
   '
   'dtEndTime: Time that is the end of the time ranges
   '
   'intMinInterval: The length of time in minutes between each time slot within the time ranges specified
   '
   'Notes:
   '''''''''
   'Currently doesn't partition into seconds. Just hours and minutes. This is easily possible however would need to
   'change the 1440 value (number of mins in a day) to 86400 (number of seconds in a day) aswell as the formatting changed
   'from short time to another.
   'Should work across multiple days but haven't experimented properly.
   'Seconds don't work at the moment due to an overflow error.

   Dim dblIntialTime As Double
   Dim dblStartTime As Double
   Dim dblEndTime As Double
   Dim strLeftPartition As String
   Dim strRightPartition As String
   Dim strDateFormat As String

   'Convert the date values into number of minutes.
   dblIntialTime = dtInitialTime * 1440
   dblStartTime = dtStartTime * 1440
   dblEndTime = dtEndTime * 1440

   'Use the partition function to get the basic numeric split up, this results in a text value
   PartitionTime = Partition(dblIntialTime, dblStartTime, dblEndTime, intMinInterval)
    
   strLeftPartition = Split(PartitionTime, ":")(0)
   strRightPartition = Split(PartitionTime, ":")(1)
    
   'Check to see if we format as a datetime or just a time value.
   If Int(dtInitialTime) + Int(dtStartTime) + Int(dtEndTime) = 0 Then
      strDateFormat = "Short Time"
   Else
      strDateFormat = "dd/mm/yy hh:nn"
   End If
    
   'Convert the Partitioned values back to a date value, sometimes the left or right will be empty.
   If IsNumeric(strLeftPartition) Then strLeftPartition = Format((strLeftPartition / 1440), strDateFormat) Else strLeftPartition = vbNullString
   If IsNumeric(strRightPartition) Then strRightPartition = Format((strRightPartition / 1440), strDateFormat) Else strRightPartition = vbNullString
    
   'Built the final string, if the left or right partition is empty then format differently.
   If (strLeftPartition <> vbNullString) And (strRightPartition <> vbNullString) Then PartitionTime = strLeftPartition & " to " & strRightPartition
   If (strLeftPartition = vbNullString) And (strRightPartition <> vbNullString) Then PartitionTime = "Before " & Format(DateAdd("n", 1, strRightPartition), strDateFormat)
   If (strLeftPartition <> vbNullString) And (strRightPartition = vbNullString) Then PartitionTime = strLeftPartition & " or later"


   'With a time only element
   'Print PartitionTime(#6:46:00 AM#, #6:00:00 AM#, #1:00:00 PM#, 20)
   '06:40 to 06:59


   'With a date element
   'Print PartitionTime(#5/14/2014 6:46:00 AM#, #4/14/2014 6:00:00 AM#, #5/14/2014 1:00:00 PM#, 20)
   '14/05/14 06:40 to 14/05/14 06:59



End Function
Go to the top of the page
 
projecttoday
post Jun 20 2018, 04:59 AM
Post#16


UtterAccess VIP
Posts: 11,208
Joined: 10-February 04
From: South Charleston, WV


Thanks.

I guess that's why Partition isn't better-known. The intervals have to be uniform. It's kind of like Choose. Most situations require more. As with Switch.
Go to the top of the page
 
JonSmith
post Jun 20 2018, 05:54 AM
Post#17


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



Yup and since it really only works with integer values its also very limited. The explanations are also rather poor so it can be a pain to understand it properly initially.
I think it has some really good potential in some very narrow specific scenarios but to do much with it you have to do something like I did.

I really like my partition time thing since I think its so flexible and when making time slots I think you would always want it uniform. Dates are trickier because as per their nature they are less uniform.
Lets all petition MS to convert to some sort of standard calendar where weeks all fit into a year perfectly neatly and all months are the same. tongue.gif
Go to the top of the page
 
JonSmith
post Jun 21 2018, 06:34 AM
Post#18


UtterAccess VIP
Posts: 4,053
Joined: 19-October 10



It was easier than I thought. I did it today just as a thought exercise to take a break from something else.
That being said, this cannot work with the example given because the data periods are inconsistent. They sometimes are 3 months, sometimes 4.

JS


CODE
Function PartitionDate(dtInitialDate As Date, dtStartDate As Date, dtEndDate As Date, intMonthInterval As Integer) As String

   Dim dblInitialDate As Double
   Dim dblStartDate As Double
   Dim dblEndDate As Double
   Dim strLeftPartition As String
   Dim strRightPartition As String


   dblInitialDate = Abs(DateDiff("m", dtInitialDate, dtStartDate))
   dblStartDate = 0
   dblEndDate = Abs(DateDiff("m", dtStartDate, dtEndDate))


   PartitionDate = Partition(dblInitialDate, dblStartDate, dblEndDate, intMonthInterval)

   strLeftPartition = Split(PartitionDate, ":")(0)
   strRightPartition = Split(PartitionDate, ":")(1)

   If IsNumeric(strLeftPartition) Then strLeftPartition = DateAdd("m", CDbl(strLeftPartition), dtStartDate)
   If IsNumeric(strRightPartition) Then strRightPartition = (DateAdd("m", CDbl(strRightPartition) + 1, dtStartDate) - 1)

   If (strLeftPartition <> vbNullString) And (strRightPartition <> vbNullString) Then PartitionDate = strLeftPartition & " to " & strRightPartition
   If (strLeftPartition = vbNullString) And (strRightPartition <> vbNullString) Then PartitionDate = "Before " & strRightPartition
   If (strLeftPartition <> vbNullString) And (strRightPartition = vbNullString) Then PartitionDate = strLeftPartition & " or later"

End Function
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 12:39 PM