none
Monthly Balance Overview RRS feed

  • Question

  • Hello folks,

    I have a query with the following layout:


    My aim is to create a report or pivot table, that uses the AccNr as rows, grouped by the AccGroup field. Next to the AccNr, I want the AccName to be displayed. As columns I want to show the Sums of ContrAM and WthdAM per AccNr per Month. Also I need a column that displays the Balance of each account. So Ech month group should have a column that calculates the the AccBal (Starting Balance for this system) + All previous ContrAM - All previous WthdAM + a certain percentage of the last months Balance as interest.

    In the old  system we had this with excel files looking like that:

    Name of Benf. = AccName, A/C = AccNr, Rcpt = ContrAM(Monthly sum)

    The problem with this system was, that the user had to re-enter lots of data that were already entered in an Excel sheet looking similar to the DailyBills table above.

    What would be my best option to do this? Recreate something like this excel file in Access, as a report or as a pivot table? (I´ve read that a pivot table can´t include subtotals in calculated detail fields)

    Or is there an easy way to just insert the data from the DB into an excel overview file?

    I´m a bit confused with the possibilities have and hope  that someone who has more access experience than me, can help me with finding the best solution.

    Feel free to ask if anything of my request needs clarification.

    Thursday, July 30, 2015 9:56 AM

All replies

  • Hi,

    Pivot tables were removed from later versions of Access, but you might be able to use Crosstab queries instead. Depending on your situation, you may have to create helper queries first before creating the crosstab query.

    Thursday, July 30, 2015 2:39 PM
  • I would suggest changing DailyBills to not have fields for each type transaction but one to identify type and another for amount.

    When summing the type would determine if transaction was positive or negative.


    Build a little, test a little

    Thursday, July 30, 2015 3:19 PM
  • I would suggest changing DailyBills to not have fields for each type transaction but one to identify type and another for amount.

    When summing the type would determine if transaction was positive or negative.


    Build a little, test a little

    That would not be possible, as you can have multiple transactions on one bill. For example on one bill you have a Loan refund as well as a contribution...
    Thursday, July 30, 2015 3:35 PM
  • That would not be possible, as you can have multiple transactions on one bill. For example on one bill you have a Loan refund as well as a contribution...

    Yes, you would have a separate record for each transaction - that is how databases are setup rather than a spreadsheet.

    Build a little, test a little

    Thursday, July 30, 2015 3:47 PM
  • Yes, you would have a separate record for each transaction - that is how databases are setup rather than a spreadsheet.

    Build a little, test a little

    Ah ok ;-)

    I´ll experiment a bit...

    Thursday, July 30, 2015 4:31 PM
  • I would suggest changing DailyBills to not have fields for each type transaction but one to identify type and another for amount.

    When summing the type would determine if transaction was positive or negative.


    Build a little, test a little

    How would this determination be done? I am pretty new to access.

    • Edited by BerthNerd Thursday, July 30, 2015 4:59 PM
    Thursday, July 30, 2015 4:59 PM
  • Using an IIF statement to multiply by -1 or 1.

    Build a little, test a little

    Thursday, July 30, 2015 5:37 PM
  • I would suggest changing DailyBills to not have fields for each type transaction but one to identify type and another for amount.

    When summing the type would determine if transaction was positive or negative.


    Build a little, test a little

    I tried some things with such a design, but I can´t get anything to work as I intend it to. For example I also have a daily report that gives out all transactions for each day. For that I want all transactions of one account in one row. But It always puts out one row for each transaction, and then fills those (several rows per account) With all the amouts. So instead of:

    AccNr = 12, Name, Contr = 50, LoanRef = 0...

    AccNr = 12, Name, Contr = 00, LoanRef = 50...

    I get this:

    AccNr = 12, Name, Contr = 50, LoanRef = 50...

    AccNr = 12, Name, Contr = 50, LoanRef = 50...

    I either want the first option, but even better the second one without access duplication the rows.

    Your suggestion to work wit a type column seams reasonable, but I don´t get it to work and would appreciate some help.

    Friday, July 31, 2015 8:35 AM
  • Post your query SQL by opening in design view, click on VIEW pull-down to select SQL View, highlight all, copy and paste in a post.

    Build a little, test a little

    Friday, July 31, 2015 1:03 PM
  • Hi Berth,

    I see that you chose not to use the corrected database that I provided to you following your last post.  Using the current database that you have, you are going to continuously run into problems trying to accomplish your goals.  The one thing I didn't do in my corrections was to convert your DailyBills table as Karl Dewey suggests, but he is correct.  The table should be modified to show one transaction per record.

    If you want to try this again, I will modify the database that I provided to you to do the job you require here correctly.  If I do, then I suggest that you take the corrections and advice given to heart and abandon the problem system you are currently working with.  If you have questions or comments about the new system, we can iron those out as we go along.  Understand that the last modification I did for you took a significant amount of time to accomplish.  I don't want to waste time if you are going to ignore what is offered.

    Furthermore, it would be in your best interest if you send me your database with the data you have already entered into it so that I can move your data over to the new system as it should be.  If you have some personal data in the system, scrub the particular field(s) and we can update those at a later date.


    Friday, July 31, 2015 1:34 PM
  • Yeah, I did not completely use it. I tried to carry over some of the things you changed to understand them better.

    At the moment I am trying my luck with such a table as Karl Dewey suggested and at least I managed to get some parts to work as I intended.

    Thank you for your offer on correcting it. I will upload the DB again in onedrive. Just give me some moments. I will post the link here then.

    Friday, July 31, 2015 1:56 PM
  • Ok, here is the link to a folder with two DBs, the new one I am currently working on, and an old one to show how the two reports I had finished, should look like.

    BerthNerd Database

    Should we get in contact via skype, teamviewer or something similar, so that you can explain me things and I can explain what I am trying to achieve?

    Friday, July 31, 2015 2:17 PM
  • We can connect using team viewer.  But, let me take a look at what you've got in your two files and I will get back with you later this afternoon.
    Friday, July 31, 2015 2:25 PM
  • ok, what time zone are you in? As I would consider it to be evening at the moment ;-) (I am in India)
    Friday, July 31, 2015 2:31 PM
  • U.S. Eastern Standard Time: 10:36 AM

    We may have to connect tomorrow then.

    Friday, July 31, 2015 2:37 PM
  • probably. I usually stay awake until 2 or 3 AM Indian Time.
    Friday, July 31, 2015 2:49 PM
  • Any News on your progress? I would require a working system tomorrow, I proposed that date, when I still thought I could do this myself. I´ve been experimenting with access the whole weekend but I can´t get it to work as I intend.
    Sunday, August 2, 2015 5:52 AM
  • Hi Berth,

    I'm sorry, I got pulled away yesterday and didn't have any opportunity to look things over.  I'm going to try and get to this today. 

    Sunday, August 2, 2015 1:53 PM
  • Ok, great! I made some progress on my attempt today. Should I upload that file? I would rather like to keep working with that one as I fully understand it so far ;-) I´ll put in in the onedrive folder, same link as above.
    Sunday, August 2, 2015 2:32 PM
  • Berth,

    I have got your database together and ready to share it with you.  If you'd like to do a remote session, let me know.  My Hotmail account is shooter0167.

    Thanks,

    Tuesday, August 4, 2015 12:56 AM