Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reports with subtotals by week (97)

    I will receive a database with a table (tblTransactions) consisting of the following fields:

    Date
    TransactionNo
    Region
    Division
    Branch
    Amount

    A Region has many Divisions and a Division has many Branches.
    I need to be able to produce reports based on user criteria.

    Total Transactions or Amount (user choice) by week per Region, Division, or Branch (user choice). It would probably be good to have a start and stop date. The weeks would have to be Sun-Sat.

    Eventually, I would like to graph this for each month. Initially, however, a report will do.

    Can anyone give any hints on how to proceed with this? The table will have over 1,000,000 records.

    Thanks in advance.

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

    Re: reports with subtotals by week (97)

    You could create one report that returns total transactions and amount per week, grouped by Region, Division and Branch.
    Also create a report on which the user can select the type of report, using option buttons or list boxes, plus text boxes to specify the begin and end dates.
    The report would be opened in the On Click event of a command button on the form. You can pass the date period in the WhereCondition argument of DoCmd.OpenReport.
    You'd hide or show sections and controls of the report in the On Open event of the report, based on the selections on the form.

    Alternatively, you could create a separate report for each type, and use the selections on the form to open the appropriate report. The date period would still be passed in DoCmd.OpenReport. This method is probably easier to code, but if you want to modify the report design, you'd have to apply the changes to each report.

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    Thanks, Hans. I have not had any success other than creating some queries that provide summary information. Even then, I am not at all satisfied as I had to make three different queries for Region, Division, and Branch.

    Can you look at the attached db and give me a pointer or hint as to how to do the reports?

    Thanks in advance

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

    Re: reports with subtotals by week (97)

    Study the attached version. I have added a form frmSelection with two group boxes, two text boxes and a command button.
    The On Click code for the command button assembles the where condition for the dates, and opens the report.
    The On Open code for the report hides various elements of the report based on the selection in the group boxes on the form.
    (You cannot open the report by itself any more unless you add more error handling code)

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    Cool. I made some modifications and it works well although i am not yet where I want to be. two things:

    1) How can I make it summarize by month/week such that I can see jan, Week 1, jan, week2, etc... Feb week 1, feb week 2? This is the crucial part of what I need to do - I must be able to present this by week.

    2) How do I use the calendar control to enter dates into the date text fields? Can I arrange it so the calendar control is not visible until (unless) the focus is on that particular date field (text box)?

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

    Re: reports with subtotals by week (97)

    1) If you want tge data to be grouped by month and by week within the lowest geographic level, you won't be able to use the method I proposed to hide Division and/or Branch. You'll have to design separate reports for Region, Division and Branch.

    2) See <post:=441,897>post 441,897</post:>.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    Thanks, Hans. In fact, the very modifications I made were to make three separate reports for those organizational levels and us e the command button to invoke the proper report. Them on the report side, to use the onopen even to appropriate dispaly transactions, amounts, or both.

    My struggle now is being able to separate (group, subtotal?) by week.

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

    Re: reports with subtotals by week (97)

    The attached version shows how to group by month and by week. I reused my previous attachment, so it'll only work correctly if you also group up to the Branch level, and I didn't bother hiding amount or number of transactions.

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

    Re: reports with subtotals by week (97)

    I assumed that you wanted to group on the week number within each month instead of on the week number within the year. The expressions you cite calculate the month and the week, so you should group on each value.

  10. #10
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    <P ID="edit" class=small>(Edited by Don_Sadler on 23-Nov-05 05:37. edited because I answered part of my question)</P>Actually, i thought I was making progress when you posted. In the report, I was using the Date to Gron On "Week" and i thought I was getting good results except that it was givine a new page for each week.

    As I look at what you offer, I am confused by the following:

    =([Date]-DateSerial(Year([Date]),Month([Date]),1))7

    finally, why do you Group on each value instead of week?

  11. #11
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    You're absolutely correct. So the difference, then, is that grouping by week will group by weeks 1-52?

    Can you explain the components of this?

    =([Date]-DateSerial(Year([Date]),Month([Date]),1))7

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

    Re: reports with subtotals by week (97)

    Grouping by week will use weeks 1-52.

    DateSerial(Year([Date]),Month([Date]),1) is the first day of the month in which Date falls (same year, same month, day = 1).
    [Date]-DateSerial(Year([Date]),Month([Date]),1) is the number of days between the first of the month and Date.
    is the integer division operator, e.g. 25 7 = 3 (the remainder is ignored), so ([Date]-DateSerial(Year([Date]),Month([Date]),1)) 7 is 0 for the 1st through the 7th of the month, 1 for the 8th through the 14th of the month etc. In the control source of the text box, 1 is added to this to get the week number within the month.

  13. #13
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    Thank you - very clever!

    I have noticed one thing - the dates seem to be randomly presented. Although the table is sorted by date, the report does not group all the feb 2005 weeks - they are in various places in the report. The grouping box says to sort them ascending but it does not seem to be working. NAy thoughts?

    Now I need to put this back into Access 97. Is there an easier to do it than by completely retyping everything? Access 97 would not even open the first example db you sent.

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

    Re: reports with subtotals by week (97)

    The groupings on month and week are below the ones on Region, Division and Branch in the Sorting and Grouping window, so the data will be grouped on month and date for each Branch separately. If you want to group on month and week first, then on Region etc. within a week, you must move (drag) the rows for month and week to the top of the Sorting and Grouping window.

    You can use Tools | Database Utilities | Convert Database in Access 2000 or higher to convert the database back to Access 97 format. If that is a problem, just holler, I'll attach a converted database.

  15. #15
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reports with subtotals by week (97)

    I found that if I move the date groupings up in the Sorting and Grouping window, the results are not consaistent with my needs. I suspect I am not expressing my objective clearly. Below is what I would like to have as an end reuslt:

    MidWest Region
    January 2005
    Week 1 $XXXXX
    Week 2 $XXXXX
    Week 3 $XXXXX
    Week 4 $XXXXX

    February 2005
    Week 1 $XXXXX
    Week 2 $XXXXX
    Week 3 $XXXXX
    Week 4 $XXXXX

    March 2005
    Week 1 $XXXXX
    Week 2 $XXXXX
    Week 3 $XXXXX
    Week 4 $XXXXX

    Is this a case where I need to create a query? If so, what would be the groupings in the query? I read previously (one of your postings, I believe) that reports handle subtotals far better than queries. Are we working with subtotals here?

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
  •