Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report by Date Problem (Access 97)

    Okay! I've had it up to here! I was trying my hardest to get this to work on my own, but it looks like my efforts are failing.

    This is my very detailed and complicated problem:

    I have a table called tbl_pending with the following fields:

    Resolved_Date
    Mail_Type
    Mail_ID

    I also have a table called RunData with the following fields:

    Start_Date
    End_Date

    Basically, I need a report based from these two tables that has a format similar to this:

    Mail Type Jan 03 | Feb 03 | Mar 03
    Address Change 100 | 105 | 110
    Doc Only 225 | 230 | 190
    Totals 325 | 335 | 300


    The date at the top is coming from the Resolved_Date field as long as it is between Start_Date and End_Date. The number is a count of the Mail_ID. I have the following query that gives me kind of what I need, but I can't figure out how to take this data and place it in a report with the above format.

    SELECT (Format([Resolved_Date],"mmm"" '""yy")) AS ResolvedMonth, tbl_pending.Mail_Type, Count(tbl_pending.Mail_ID) AS CountOfMail_ID
    FROM tbl_rundata, tbl_pending
    WHERE (((tbl_pending.Resolved_Date) Is Not Null And (tbl_pending.Resolved_Date) Between [Start_Date] And [End_Date]))
    GROUP BY (Format([Resolved_Date],"mmm"" '""yy")), tbl_pending.Mail_Type, (Year([Resolved_Date])*12+Month([Resolved_Date])-1)
    ORDER BY (Year([Resolved_Date])*12+Month([Resolved_Date])-1);

    Does anyone out there know how to make this work? I was thinking a crosstab query, but since the date is completely choosable by the user, I don't think that would work.

    Any help with this would be greatly appreciated.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Date Problem (Access 97)

    Try the following crosstab query:

    TRANSFORM Count(tbl_pending.Mail_ID) AS CountOfMail_ID
    SELECT tbl_pending.Mail_Type
    FROM tbl_rundata, tbl_pending
    WHERE (((tbl_pending.Resolved_Date) Between [Start_Date] And [End_Date]))
    GROUP BY tbl_pending.Mail_Type
    PIVOT Format([Resolved_Date],"mmm"" '""yy")

    This will list the months across the top in alphabetic order; you can reorder them in the report (manually). If you prefer the query to list the months in chronological order, you can set the column headings property to "Jan '01","Feb '01", "Mar '01". The SQL becomes:

    TRANSFORM Count(tbl_pending.Mail_ID) AS CountOfMail_ID
    SELECT tbl_pending.Mail_Type
    FROM tbl_rundata, tbl_pending
    WHERE (((tbl_pending.Resolved_Date) Between [Start_Date] And [End_Date]))
    GROUP BY tbl_pending.Mail_Type
    PIVOT Format([Resolved_Date],"mmm"" '""yy") In ("Jan '03","Feb '03","Mar '03")

  3. #3
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Thank you very much for your help with this.

    The crosstab query works wonderfully when creating the report. The problem I see now is when the user selects different dates to lookup between. WHen this happens, it produces errors on the report. How can I make the report reformat itself with these new dates?

    Thanks again

    Vassago

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Date Problem (Access 97)

    Welcome to the wonderful world of dynamic crosstab reports. Unfortunately, I can't refer you to one of the many threads about this subject - they have all been removed <img src=/S/sad.gif border=0 alt=sad width=15 height=15>.

    I have attached a zipped Access 97 database that demonstrates how to fill a report based on a dynamic crosstab query. It uses a form to enter the start and end dates, and code in the On Open event of the report to set up the report. It is not trivial, but something like this (there are many variants) is necessary to make a report reflect the changes in column headings in a dynamic crosstab query.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Thanks for the warm welcome! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    You have opened up a whole new page for me. I'll play with this code for a while to see what I can come up with. I appreciate the time you have taken and hopefully will not need to extend this thread into new branches. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Why have the threads been removed?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Date Problem (Access 97)

    >> Why have the threads been removed?

    See the newsflash <!post=Massive culling of posts.,269652>Massive culling of posts.<!/post>

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    Check out http://www.rogersaccesslibrary.com/downloa...abReport2k.mdb'

  8. #8
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Okay, this is where I'm at. I looked in the Solutions Sample Database and followed the instructions completely (I think). When I open the report, it is empty. I can't figure out where I'm going wrong here. I've tried everything I can think of for the past few hours to no avail. I have attached a sample of my database the way I have it now. Please take a look and let me know what you think! Thanks for your help with all of this!

    Vassago
    Attached Files Attached Files

  9. #9
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Okay, so now I have data showing up, but it's still not showing up in chronological order. Is your method you posted the only way to make it show up that way? Are there any other suggestions to make it show up in chronological order other than listing each individual month. When I list a month not chosen, it shows up as 0, not to mention I want the user to be able to define the months they are looking up (ranging from 05/2002 - 07/2003). Thanks!

    Vassago
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Date Problem (Access 97)

    This is a personal issue, but I thoroughly dislike the code of this particular example in the Solutions database. It is extremely inefficient and convoluted. (Many other examples in that database are excellent and worthwhile studying)

    So I haven't even tried to find what was wrong. I replaced the code by that from the demo I posted in this thread, adapted for your situation. The code is sprinkled with comments to explain what it does.
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Date Problem (Access 97)

    I would use dates instead of formatted text in the query, and do the formatting in the report.

    TRANSFORM Count(tbl_pendingmail.Mail_ID) AS CountOfMail_ID
    SELECT tbl_pendingmail.Mail_Type
    FROM tbl_rundata, tbl_pendingmail
    WHERE (((tbl_pendingmail.Date_Entered) Between [Start_Date] And [End_Date]))
    GROUP BY tbl_pendingmail.Mail_Type
    PIVOT DateSerial(Year([Date_Entered]),Month([Date_Entered]),1);

    As you see, the column headings are not specified explicitly this time.

    In the code behind the report as I posted it, change the line

    Me("Head" & intX).Caption = rst(intX - 1).Name

    to

    Me("Head" & intX).Caption = Format(rst(intX - 1).Name, "mmm 'yy")

  12. #12
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Wow! This appears to be perfect. I appreciate the help. You're also correct, your sample is much better than the one Access gives.

    Thanks!

    Vassago

  13. #13
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Okay, so I've ran into one more problem. I have been able to successfully recreate the report a few more times for different reasons which I am eternally grateful for. I just have a quick question on a new situation.

    How can I make the last column calculate an average instead of a total? I've tried everything I can think of. Thanks!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report by Date Problem (Access 97)

    Do you want to calculate a monthly average? The simplest way is to divide the sum by the number of months. In the database I attached a few replies up in this thread, these are the modified lines to use:

    ' Set caption of next available label in page header to "Average".
    Me("Head" & (intColumnCount + 1)).Caption = "Average"
    ' Set control source of next available text box in detail section to row sum.
    Me("Col" & (intColumnCount + 1)).ControlSource = "=Nz(" & Mid(strRowTotal, 4) & ",0)/" & (intColumnCount - 1)
    ' Set control source of next available text box in report footer to grand total.
    Me("Tol" & (intColumnCount + 1)).ControlSource = "=Nz(" & Mid(strGrandTotal, 4) & ",0)/" & (intColumnCount - 1)

  15. #15
    Lounger
    Join Date
    May 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report by Date Problem (Access 97)

    Thanks for the quick reply. It's going to be a little harder than that I think. There are some months where the number could be 0. In this case, I would like it to skip that month and not count it. Meaning, if you got "0" for Jan 03, "2" for Feb 03, and "1" for Mar 03, it would calculate the average of Feb and March only, since there is no value for Jan, and come up with an average of "1.5" instead of "1". What do you think? Thank you!

    Lestat

Page 1 of 2 12 LastLast

Posting Permissions

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