DataBase - Querying Dates
Asked By Martin
18-Mar-10 12:09 PM
Hello,
I am looking for some advice on how to tackle querying some data.
I have two tables, table one with around 10,000 records per month and
columns that give me (for each record) a start date, start time, end date and
end time.
Table two details a percentage for each hour and day of the week (so 24
hours * 7 days = 168 records). This has three columns, the day of the week
(Mon to Sun), the hour (00:00 to 23:00) and a percentage.
What I want to do is sum the total of the percentage column in table 2 where
the day of the week and the hour of the week fall within the start date /
start time and end date / end time.
Any help would be greatly appreciated.
Martin
VBA
(1)
OpenRecordset
(1)
ValuesTable
(1)
HourNumber
(1)
WeekTotal
(1)
DayNumber
(1)
DateDiff
(1)
FCalcSum
(1)
KARL DEWEY replied to Martin
If table two only has day of the week, there can be 4 - 5 Mondays in a month,
how will you know which Monday to use?
--
Build a little, test a little.
John Spencer replied to Martin
Sounds as if you need a non-equi join and at the same time you need to do some
data manipulation on the fields That are being used in the join.
The first thing I would do would be to add another column and store the Day of
week number Sun=1 to Sat=7 for each record. Then I would add another column
that stores just the NUMBER of the hour.
How do you handle partial hours? Ignore them, round them to the nearest whole
hour, or use partial hours to get partial credit.
Do you have records that overlap days? I assume so since you are recording a
start date and end date
Do you have records that encompass more than one week - event starts on Friday
and runs until Tuesday of the following week? If not the problem becomes a bit
easier to solve.
Do you have records that encompass multiple weeks? Jan 1 to Jan 31 for instance.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Martin replied to John Spencer
Thanks for the reply John.
Yes I do have records that could start on a Friday and end on the Tuesday of
the next week. Table one with the data of start date/time and end date/time
is something I am sent and cannot change however the second table with the
percentages is something I have created to try to help overcome the problem
but this can be changed if that helps?
John Spencer replied to Martin
I wish you had answered all the questions, but that should be enough for me to
start thinking about a solution. I would like to do this using a query, but
it may be necessary to create a VBA function. It would certainly be easier to
use VBA to solve this, but the speed might not be acceptable. I will try to
get back to you by Sunday. I am a little stretched for time at this point.
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John Spencer replied to John Spencer
I have not come up with a good way to do this with just a query. I have put
together a function and tested it minimally. This assumes that your lookup
table has a weekday number (1 to 7) and an hour number (0 to 23).
Also this gives full credit for partial hours.
'================= Minimally tested code follows ================
Function fCalcSum(dStart, tStart, dEnd, tEnd)
Dim dblResult As Double
Dim StrSQL As String
Static WeekTotal As Double
If WeekTotal = 0 Then
WeekTotal = dSum("TheValue", "ValuesTable")
End If
If IsDate(dStart) And IsDate(tStart) _
And IsDate(dEnd) And IsDate(tEnd) Then
If Weekday(dStart) <= Weekday(dEnd) Then
StrSQL = " SELECT SUM(TheValue)" & _
Weekday(dStart) * 100 + Hour(tStart) & _
Weekday(dEnd) * 100 + Hour(tEnd)
ElseIf Weekday(dStart) > Weekday(dEnd) Then
StrSQL = " SELECT SUM(TheValue)" & _
Weekday(dEnd) * 100 + Hour(tEnd) & _
Weekday(dStart) * 100 + Hour(tStart)
End If
'Get the hours that are not entire weeks
dblResult = Nz(CurrentDb().OpenRecordset(StrSQL).Fields(0), 0)
'Get hours for entire week(s)
dblResult = dblResult + (DateDiff("d", dStart, dEnd) \ 7) * WeekTotal
'Adjust the total for counting the same hour twice in the above SQL
dblResult = dblResult - 1
'Return the resulting calculation
fCalcSum = dblResult
Else
fCalcSum = Null
End If
End Function
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Outlook (1) I should note that I am using the data macro to call a VBA function (by setting a local macro variable to the result of the function call). So this is not for the web, I did not see a problem with calling a VBA function (and various places I have seen online mentioned workarounds for calling VBA from a data macro). But, again, while it works when I insert a record in note? In it I added that I was using the AfterInsert macro to run some VBA code (by setting a local variable to the result of a function call). Maybe that maybe that is the problem Yes, in this case, you need a copy of the VBA in the front end. (for testing you can place it in both - since if you happen to open the back end table directly, then the VBA code is ALSO required since the front end might not even be open and the talk to that may be sitting on your computer. So when the trigger fires, that VBA code will be required. So, the VBA needs to exist in the front end if that all you opened. However, if you
VBA vs Macros DataBase Let me preface this by saying it is a sincere question. I Using Access 2010 I note that help tells us to use macros in preference to VBA for web apps and for greater security. Also a VBA to macro converter is provided but not the other way around. Why so down on VBA? Once upon a time it was considered that for any serious development VBA was preferred to macros because it had a) error trapping (where untrapped errors would crash runtime installation of Access) and b) greater power and flexibility. Are these two advantages of VBA over macros still true with 2010? Other than for web apps (which I am not doing) should I change to macros or stick with VBA? When I took up Access I learned VBA and only did macros when it was the only way for certain operations. Other than
big of post do you want? Perhaps the big news is the new version of VBA, or what we call VBA 7. This new version of VBA now has a true pointer data type called longPtr. We also get a 64 bit the term JET, but I really talking about ACE). Note that this new version of VBA of course is also runs on the 32 bit version of windows. -> Database triggers and features are are more oriented towards developers (such as a new data Pointer type in VBA - we never had a data pointer type in VBA. Too bad we did not, as the switch to 64 bits would have been seamless all well, when we jump to 128 bit computers, Access and VBA will be ready because we now have a true pointer data type). Other features will on the surface it looks like a giant leap forward for Access, and also for VBA. I was not aware of the table triggers - very cool. I like using triggers and
correct term. I am talking about Dbs to which you set a reference in the VBA references section. Secondly apologies for sort-of reposting this question, but I drew blanks last the Tools-> References section. As far as I know it is not possible to use VBA to compact and repair. I cannot find any way of removing the reference from the I have a reference set to "Menu.Accde" in the Tools-> References section of the VBA editor, then I uncheck the reference, Menu.AccDe still shows up in the project explorer Only those functions = 93can not be found = 94. But you can refresh the reference through VBA, by looping through the References. Especially when you have more than one reference that must the ambiguities from the programmer = 92s side, but that Access could refresh the reference for VBA, but failed to do that for (I assume) Jet Expression Service. After an Access.References except the one it was originally compiled with. The entire process can be automated using VBA and automation, so it is not as tedious as it sounds. Steve What old "Switchboard code." I also use late binding for everything but the basie three Access references (Access, VBA, DAO). Extra code? Not so much, but it is harder to code when you do is useful if there is a conflict with other libraries. . . just like you can call VBA.MsgBox() or simply MsgBox(). Of course, you know all that. The Help file DOES state
SQL help DataBase I am trying to access a table in a mdb using VBA though a brain dead program. The problem is it is modifying the the text inside line in it is editor. strSql = "SELECT tblParts.[PART#] FROM tblParts WHERE (((tblParts.[PART#]) = '" & FileName & "'));" VBA sees the following strSql = "SELECT tblParts. FROM tblParts WHERE (((tblParts.) = '" & FileName & "'));" Is there isomeway I SQL group? Access Discussions SQL Server (1) Office (1) Module (1) Macro (1) Date (1) VBA (1) VB (1) Environment (1) Huh? I think you are actually saying that you are trying to write VBA code in a brain dead text editor. You need to identify the editor. Why not use the VBA IDE in Access? Ughhh! Get rid of those unnecessary parentheses! And the semicolon as well are using, it is impossible to advise you how to escape them. it is not VBA doing this so I suspect the VBA escape method (doubling the characters that you want to be treated literally) would work. It SQL" question, anyways - it is a question about the text editor you are using. a VBA l - . ets k. t on I know that the #, useful for date fields, can make