Results 1 to 12 of 12
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Lost Beginner, Missing months on reports.

    I Hate Reports, Its why I have put off really learning them up tell now.

    anyway I started with a simple qry of a savings account. the qry_Savings when run shows
    DepYear DepMonth SumOfDeposit
    2019 Nov 25.00
    2019 Dec 25.00
    2020 Jan 25.00
    2020 Feb 25.00
    2020 Mar 25.00



    The qry Design view is pulling the year and month from the actual deposit date if that makes sense



    when I create a simple report (using the wizard)

    DepYear DepMonth SumOfDeposit
    2019
    Nov
    25.00
    25.00
    Jan
    25.00
    25.00
    2020
    25.00
    25.00
    25.00


    Now I am not a complete Idiot, I have completed a 16 hour online course on Access, Designed some complex forms and have even jumped into some basic to intermediate VBA.
    But this is just madness, voodoo magic that I cant wrap my head around. and its not just this from, its always. I am completely lost as to why it shows in this format, Any thoughts or explanations are appreciated.








    thanks for your time

  2. #2
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    First time uploading an image on the forum

    Click image for larger version. 

Name:	Capture2.PNG 
Views:	23 
Size:	29.5 KB 
ID:	42373

    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	20.5 KB 
ID:	42371

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Tuckejam View Post
    ....But this is just madness, voodoo magic that I cant wrap
    Now that's funny!!!



    Not sure what you want the report to look like, but let's start a new report.

    You seem to know about the report Sorting and Grouping.
    When you create a new report, you should add a REPORT HEADER.
    In the detail section, add the fields from the record source (DepYear, DepMonth, SumOfDeposit).
    Select the labels and cut them out, then paste them into the Page Header (like you have) - all 3 text boxes should still be in the details section.
    Align the controls under the labels.
    Shrink the detail section as much as possible.
    Switch the Print Preview.
    Everything good?
    Switch back to design view.
    In the Sorting & Grouping, add a DepYear header.
    Drag the DepYear control and label into the DepYear header.
    Switch the Print Preview.
    How does that look??



    You could post a copy of the dB - would make it easier to advise........

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe something like this?
    Attached Thumbnails Attached Thumbnails Rerport1.png   Report2.png  

  5. #5
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Evans House Account.zip


    Madness: (DesignView ([PageFooter] Over [ReportFooter])), (ReportView([ReportFooter] Over [PageFooter]))

  6. #6
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Doing better with it now.

    Trying to figure out how the language

    =Sum([DepositAmount])

    works in one report and returns that total of all deposits

    but generates an error in another report ???

    Click image for larger version. 

Name:	Capture3.jpg 
Views:	25 
Size:	115.1 KB 
ID:	42381

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi James,

    Please have a look at the updated report. Basically you cannot do calculations in the page footer (here is another thread https://stackoverflow.com/questions/...oter-sum-error),I copied the exact same text box to the report footer and it works. In the first one you had it correctly in a group footer.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I modified your relationships and a few field names (sorry - couldn't resist). And I fixed some of the code.

    Take a look at the report "rpt_SavingsSimpleSumrpt". There is another way to get the total by using a UDF (user defined function). I wrote a function named "fCalcTotal" and put it in the page footer.
    NOTE: at this point the function sums ALL records in tblSavings - no filtering. (but I'm not sure why you have a separate table for deposits)

    To me, it doesn't make sense to have a total in the page footer because you can't know/control the number of records per page.
    I made a 2nd report "rpt_SavingsSimpleSumrpt_2" with a different format.


    .....I added colors just to distinguish the different sections while testing.
    Attached Files Attached Files

  9. #9
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    ssanfu -

    Thank you for all of that,
    To clarify why I have a separate table for deposits into savings, Its a separate account number that money gets automatically transferred to every month from the main account. really tbl_transactions should or could be called tbl_checking account.

    and thank you for the fcalcTotal, but I was also trying to figure out how to get the total of the numbers on the page rather than referencing the numbers in the table, once i get things figured out I will start generating reports from the main account tbl_transactions but they will be filtered by month and category etc.

    and question on the report Calculations. is the syntax

    Also a question on the PK naming convention.

    I know PK is Primary Key and I have thought about using it, but so far (and i am still very new) but so far I have just been going with ID for all of my primary keys.
    Is it mostly preference?, is PK the standard? is there a reason to use both like you did in this situation?
    (sorry, just curious and always looking to learn and understand more)

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    As with most naming conventions, It is a personal preference. Many use PK FK suffixes to differentiate primary and foreign keys. The key (no pun intended) is to be consistant in your naming.

  11. #11
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    ALRIGHT!!!

    Feels like I'm getting a handle on it, at least I understand the Header, Footer, Grouping thing now and how to format/layout what I'm trying to do.


    I did run into on thing I don't understand, dealing with the totals
    in the report below I the savings deposit by year. and at the bottom of the report (Report footer) I had a total for all deposits unsung the the formula "=Sum([SumOfDepositAmount])"

    then I just copied the label and text box with the formula and pasted it int the DepYearFooter, wanting to get the totals for each years depost.

    and to my surprise it just worked. the same formula that sums all deposits also sums the deposits for the year totals ????

    I dont understand what is happening with this


    Click image for larger version. 

Name:	Capture2.PNG 
Views:	14 
Size:	18.5 KB 
ID:	42416Click image for larger version. 

Name:	Capture.PNG 
Views:	14 
Size:	24.4 KB 
ID:	42417

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Both formulas are summing the same field.

    Calc in group header/footer considers only records matching group identifier.

    Calc in report header/footer considers ALL records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-23-2017, 02:55 PM
  2. Replies: 7
    Last Post: 03-23-2015, 07:36 AM
  3. VBA - 13 month rolling data with missing months
    By tbelly82@gmail.com in forum Programming
    Replies: 2
    Last Post: 06-02-2014, 06:27 AM
  4. Help for a beginner with tables and reports
    By Amazingmin in forum Access
    Replies: 11
    Last Post: 02-13-2014, 04:02 AM
  5. Lost characters when exporting reports to pdf
    By QTT in forum Import/Export Data
    Replies: 3
    Last Post: 08-29-2012, 05:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums