Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    40

    Unanswered: How to tweak my calculating Query?

    I am a complete novice with little or no code writing experience. It is only with considerable help and guidance from members of this forum that I have reached the stage I am at now. Put simply, I now have a working query that calculates the days elapsed between RaceDate values returned in the recordset but it needs some minor tweaking. I have attached the qryResult3 notepad file for all to see. My problem now is only one of alignment. I need the RaceDate field to remain the way it is in decending order please!. I would like 09 Sept 02 to answer to read 0 and then 11 Nov 02 answer to read 63 then each calculation needs to be moved up one place ending with 24 Aug 04 answer to read 30 and that would be a perfect result. I have used Ineuw's following instructions to get me this far:

    'This requires a function call from the query which passes the 1st value to a
    'public function, stores that value in a holding variable, then passes the 2nd
    'value and evaluates it with the holding variable, then replaces the new value
    'in the holding variable and returns the result and so on . . .
    'There also must be a mechanism which clears the holding variable before the
    'first record of the recordset.

    'To do this:
    '1. Create a module and declare a public variable at the module level:
    Public varHoldDate as Variant

    '2. Create a Public Function to clear the variable:
    Public Function fnClearHoldDate()' I name all my functions starting with fn.
    varHoldDate = Null ' Clear the variable before running the query.
    End Function

    'Create a macro:
    '3. the 1st macro line calls fnClearHoldVar():
    RunCode =fnClearHoldVar()

    '4. 2nd macro line calls the query:
    OpenQuery . . . QueryName (I've used myqryname)

    '5. In the query add a field:
    DateDiffCalc: fnCalculateDiff([RaceDate])

    '6. The date difference function call.
    Public Function fnCalculateDiff(ByVal varNewDate As Variant) As Variant
    ' If variable is Null, place the 1st passed date in the holding variable
    If IsNull(varHoldDate) Then
    varHoldDate = varNewDate
    fnCalculateDiff=0 ' 1st date difference is 0
    Else
    fnCalculateDiff = DateDiff("d", varNewDate, varHoldDate) ' assign function's return value.
    varHoldDate = varNewDate ' place new date into the holding variable for the next record.
    End If

    End Function

    Many thanks to all again. Kind Regards, Ronald A. Dixon
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    Don't fret, I tested everything before sending it to you, so here is what you should do next:

    1. Click on the database menu bar\tools and Repair and compact the application. You should do this regularly and often, during development.

    2. After compacting the database, run the macro to open the query and click on the Racedate column and sort it descending, using the sort button of the query toolbar. Then click on save.

    I don't know how you're pasting data into a notepad, but I use the database Office links\Output to notepad and that works fine.

    It may be best if you zip a copy of your database in Access 2000 format and post it or email it to me directly at ineiw@aei.ca and I'll have a look at it.

  3. #3
    Join Date
    Aug 2004
    Posts
    40
    I have tried to send an email directly to you via the address you gave me but it keeps bouncing back and the mail delivery message reads some failure or other. My email message did read: Good Morning,
    ...my database may be far too big for me to email to you, it is 540mb in size. I am happy to do that if you think it is possible? Failing that, I have compacted and repaired it as you describe, I do have a policy of compact and repair regularly in any case. I tried opening the query by highlighting the macro and clicking on run. The macro fails and I get an Microsoft Access window which says: The expression you entered has a function that Microsoft Access can't find. I can run the query by double clicking the query itself and it works well. However, the RaceDate column is perfect and decending but the calculated values returned all just need moving up one place in the datasheet view.
    Regards,
    Ronald A. Dixon
    my email is: ronaldadixon@btopenworld.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do this problem with straight sql

    if your table looks like this (i loaded this from your text file):
    Code:
    Racedate	somenumber
    2002-09-09	63
    2002-11-11	15
    2002-11-26	30
    2002-12-26	22
    2003-01-17	7
    2003-01-24	36
    2003-03-01	3
    2003-03-04	27
    2003-03-31	23
    2003-04-23	8
    2003-05-01	13
    2003-05-14	19
    2003-06-02	11
    2003-06-13	12
    2003-06-25	19
    2003-07-14	19
    2003-08-02	45
    2003-09-16	2
    2003-09-18	249
    2004-05-24	11
    2004-06-04	17
    2004-06-21	34
    2004-07-25	30
    2004-08-24	0
    then you can run this query:
    Code:
    select t1.racedate
         , t2.racedate
         , t1.racedate - t2.racedate as days
         , t2.somenumber as t2number
      from racedates t1
         , racedates t2
     where t2.racedate 
         = (select max(racedate)
              from racedates
             where racedate < t1.racedate)
    order by 1;
    which produces these results:
    Code:
    t1.Racedate	t2.Racedate	days	t2number
    2002-11-11	2002-09-09	63	63
    2002-11-26	2002-11-11	15	15
    2002-12-26	2002-11-26	30	30
    2003-01-17	2002-12-26	22	22
    2003-01-24	2003-01-17	7	7
    2003-03-01	2003-01-24	36	36
    2003-03-04	2003-03-01	3	3
    2003-03-31	2003-03-04	27	27
    2003-04-23	2003-03-31	23	23
    2003-05-01	2003-04-23	8	8
    2003-05-14	2003-05-01	13	13
    2003-06-02	2003-05-14	19	19
    2003-06-13	2003-06-02	11	11
    2003-06-25	2003-06-13	12	12
    2003-07-14	2003-06-25	19	19
    2003-08-02	2003-07-14	19	19
    2003-09-16	2003-08-02	45	45
    2003-09-18	2003-09-16	2	2
    2004-05-24	2003-09-18	249	249
    2004-06-04	2004-05-24	11	11
    2004-06-21	2004-06-04	17	17
    2004-07-25	2004-06-21	34	34
    2004-08-24	2004-07-25	30	30
    now, granted, a join like that isn't going to be terribly efficient, but it runs pretty fast (less than a second) when i tested it, and it doesn't look like you have all that many dates (certainly not half a gig worth)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2004
    Posts
    40
    R937 Thank you for your contribution, but the table that my query is working on contains 47 different fields and a growing number of records. The total number of records to date is 460731. You will see that the present solution so kindly supplied by INEUW is achieving my aims almost entirely, it shows the RaceDate values in decending order which is what I want and it just needs tweaking a little to align the days elapsed values with the RaceDates. In an ideal world the most recent RaceDate will be at the top of my query in datasheet view (in this case 24 Aug 04) and the value aligned with that should be 30. The earliest date shown at the bottom of my query in datasheet view (in this case 09 Sep 02) would have a value aligned to it of 0 or Blank or whatever because there is no RaceDate before it. Hope this helps clarify things a little. Regards, Ronald A. Dixon

  6. #6
    Join Date
    Aug 2004
    Posts
    40
    Eureka! we have it....congratulations to all who have helped out in the last couple of days. Can I give a special mention to Ineuw who took the trouble to contact me directly and has ensured I have a much improved database that is working absolutely perfectly (touch wood) I could not wish for better help and advice than I have received on this forum and I would like to take this oportunity to thank you all. Ineuw I cannot thank you enough. If there is anything I can do for you, please feel free to ask it. Kind Regards, Ronald A. Dixon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •