Files can be attached to post. Go to the Advanced post editor.
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.
This is the architecture of the project.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
Im not sure how to go about what you have advised me. Create another query?
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
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!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
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
2. then copy and paste this sql into another new query and save with whatever name you likeCode:SELECT CLng(CLng([transaction date]) & [id]) AS tmpID, [Canadian Dollar Acct].*, [payment]+[advance] AS Net FROM [Canadian Dollar Acct]
which produces this resultCode: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;
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
2. then copy and paste this sql into another new query and save with whatever name you likeCode:SELECT CLng(CLng([transaction date]) & [id]) AS tmpID, [Canadian Dollar Acct].*, [payment]+[advance] AS Net FROM [Canadian Dollar Acct]
which produces this resultCode: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;
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?
yesDo 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 - current balance is simply the sum of the amount column.Would that make the whole data management process less complex and mitigate errors in the future?
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
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.
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.
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.
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?
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
no reason why you can't but I have to question why you need to.it seems impossible to add 4 Dsum functions in one text box
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