Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 43
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815

    Files can be attached to post. Go to the Advanced post editor.
    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.

  2. #17
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Ajax View Post
    You can but I’m pretty busy at the moment so may take a while to reply. One of the significant differences between Access and excel is excel combines data storage and presentation in one view whilst access uses tables for data storage/relationships and queries/forms/reports for presentation. Tables and relationships should be designed for optimum performance, not presentation
    This is the architecture of the project.
    Im not sure how to go about what you have advised me. Create another query?
    Click image for larger version. 

Name:	Screen Shot 2018-05-16 at 1.05.18 PM.jpg 
Views:	22 
Size:	124.3 KB 
ID:	34052

  3. #18
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by June7 View Post
    Files can be attached to post. Go to the Advanced post editor.
    I can't attach the access database file for some reason...

    Thats the query table view (as you can see the balance doesnt update when two records are on the same date.):
    https://ibb.co/fRiSGJ

  4. #19
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've provided the solution - yes you need a different query

    re attaching the file, remove all unrelated tables, queries, forms, reports and modules. Reduce table rows to a number sufficient as an example and change anything which is confidential - particularly names and addresses. Then compact the file and zip. then upload the zipped file

  5. #20
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22

    Talking

    Quote Originally Posted by Ajax View Post
    I've provided the solution - yes you need a different query

    re attaching the file, remove all unrelated tables, queries, forms, reports and modules. Reduce table rows to a number sufficient as an example and change anything which is confidential - particularly names and addresses. Then compact the file and zip. then upload the zipped file
    There you go! Thank you!
    Attached Files Attached Files

  6. #21
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    more complicated because of your split columns and requirement to show net for the day. Personally I think you will have problems down the line with overly complex queries and data management issues if you store your data in this way - you are combining two transactions into one. As said before you manage the data through forms - what the user sees in unrelated to how you store the data. For the same reason, I wouldn't bother with formatting of fields in tables or queries (this is not excel) - they just hide the underlying value.

    Anyway:

    1. Copy and paste this sql into a new query and save as query 1

    Code:
    SELECT CLng(CLng([transaction date]) & [id]) AS tmpID, [Canadian Dollar Acct].*, [payment]+[advance] AS Net
    FROM [Canadian Dollar Acct]
    2. then copy and paste this sql into another new query and save with whatever name you like
    Code:
    SELECT Query1.[Transaction Date], Query1.Payment, Query1.Advance, Query1.Net, Sum(Query1_1.Net) AS SumOfNet
    FROM Query1 AS Query1_1 INNER JOIN Query1 ON Query1_1.tmpID <= Query1.tmpID
    GROUP BY Query1.[Transaction Date], Query1.Payment, Query1.Advance, Query1.Net, Query1.ID
    ORDER BY Query1.[Transaction Date], Query1.ID;
    which produces this result
    Click image for larger version. 

Name:	Capture.JPG 
Views:	23 
Size:	61.9 KB 
ID:	34058

  7. #22
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Ajax View Post
    more complicated because of your split columns and requirement to show net for the day. Personally I think you will have problems down the line with overly complex queries and data management issues if you store your data in this way - you are combining two transactions into one. As said before you manage the data through forms - what the user sees in unrelated to how you store the data. For the same reason, I wouldn't bother with formatting of fields in tables or queries (this is not excel) - they just hide the underlying value.

    Anyway:

    1. Copy and paste this sql into a new query and save as query 1

    Code:
    SELECT CLng(CLng([transaction date]) & [id]) AS tmpID, [Canadian Dollar Acct].*, [payment]+[advance] AS Net
    FROM [Canadian Dollar Acct]
    2. then copy and paste this sql into another new query and save with whatever name you like
    Code:
    SELECT Query1.[Transaction Date], Query1.Payment, Query1.Advance, Query1.Net, Sum(Query1_1.Net) AS SumOfNet
    FROM Query1 AS Query1_1 INNER JOIN Query1 ON Query1_1.tmpID <= Query1.tmpID
    GROUP BY Query1.[Transaction Date], Query1.Payment, Query1.Advance, Query1.Net, Query1.ID
    ORDER BY Query1.[Transaction Date], Query1.ID;
    which produces this result
    Click image for larger version. 

Name:	Capture.JPG 
Views:	23 
Size:	61.9 KB 
ID:	34058

    Absolutely amazing and impressive. It worked exactly as you advised it...
    Do you suggest that transactions should be entered seperately
    For example: (50,000) Payment as one record and 50,000$ Advance as another record and not on the same line which would subsequently remove the Net column?
    Would that make the whole data management process less complex and mitigate errors in the future?

  8. #23
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Do you suggest that transactions should be entered seperately
    For example: (50,000) Payment as one record and 50,000$ Advance as another record and not on the same line which would subsequently remove the Net column?
    yes

    Would that make the whole data management process less complex and mitigate errors in the future?
    yes - current balance is simply the sum of the amount column.

    store the values as you do in one column - negative for payments, positive for advances. In this case that is all you probably need, but you may have other related data such as cheque number or bacs reference - they can both use the same column named something like 'reference'. You also may have different transaction types such as interest so you may need a field to indicate this as well. And if your records don't go back to the opening of the account (when the balance would be zero) you will need an opening balance 'transaction type'.

    Also, no need to copy the whole post, just the bits you have any questions about

  9. #24
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've attached your db for how a single column could work in terms of a running sum (see query3) and a possible entry form (frmTransactions) to control input, just using formatting properties, no code required. To see how it is done, look at the format properties for the controls and also the conditional formatting. If the user gets the signage wrong, it puts it in the right column - but the can just edit the signage to put in the right column.

    I'm not saying it is the way to go, just demonstrates what can be done. The 'new transaction' for the date is a case in point, and would need some code to tidy it up.
    Attached Files Attached Files

  10. #25
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Ajax View Post
    The 'new transaction' for the date is a case in point, and would need some code to tidy it up.
    Not sure what you mean by this?

    I like your approach better, I'll keep it that way. The SQL code on query 3 is too advanced for me! Great job though. Is there anyway to have a sort order on the form for the transaction? I think I have to use VBA for this....

    I`ve created the report linked to query 3 to visualize the balance better. All I need is copy paste the query and table for other accounts and create their respective forms and reports.

  11. #26
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    tidy up - when you click on the calendar icon, it opens to 1899 (because the default is -1 so the 'enter date' will appear
    reason query3 is more complex is because I was experimenting with a query you just link on transactionID - which if the relevant sql was copied to a query you can instantly find the balance for any transaction - saves having to write it out in different situations.

  12. #27
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    I changed it to Date() so that it doesnt show that specific date anymore.

    Do you know any way to include a box with the formula displaying the most recent balance in the main menu form?

  13. #28
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    use the dsum function

    dsum("Amount","tblTransactions")

  14. #29
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Quote Originally Posted by Ajax View Post
    use the dsum function

    dsum("Amount","tblTransactions")
    beautiful, it worked for all my accounts.

    it seems impossible to add 4 Dsum functions in one text box (i.e: DSum of amount in query 1 + Dsum of balance in query 2 + and so on...)
    i also tried to do textbox3 = textbox1+textbox2 but doesnt work.
    ive tried to use the Sum function with the text box but here again no luck...

    my goal is to add the balance of query 1,2,3,4

  15. #30
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it seems impossible to add 4 Dsum functions in one text box
    no reason why you can't but I have to question why you need to.

    If you have 4 accounts, then they should be in the same transactions table - but with an additional field to show the account name - and you would need to modify the queries provided to filter or group by the account name

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  2. Comparing positive to negative values.
    By rubberducky in forum Access
    Replies: 1
    Last Post: 01-20-2014, 04:44 PM
  3. Replies: 2
    Last Post: 06-05-2013, 09:37 AM
  4. Replies: 7
    Last Post: 09-16-2011, 01:39 PM
  5. Convert Negative Values to Positive
    By creativefusion in forum Queries
    Replies: 1
    Last Post: 10-21-2009, 02:47 AM

Tags for this Thread

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