End of month balance carry forward

[access2010]

We use Access 2003 at our shelter

The Trustees of the shelter which I volunteer at want a report to match each month's statement from the Broker or Bank for their investments

How can we get the values for the "Bought Total Cost $" and "Sold Total Sell $" in our report

How do we control a opening cash balance from the end of the previous month and at the end of the current month so that these values continue on for each month

Thank you
Linda

 

[arnelgp]

that is accounting principle you are talking there. on my opinion you must save beg bal and end bal in another table. others may prefer to just use query: begbal for the month+debit for the month-credit for the month = end bal for the month = beg bal for next month. but as your data grow, say for 2 years and still using the later proc, your query will become slow, especially when theres a lot of transactions for each day.

 

[plog]

I'm looking at your table and its not set up properly. You don't store the value of debits and credits in different columns. You shouldn't have a [Shares Sold] and [Shares Bought] field, you should simply have a [SharesTransacted] field (positive numbers mean bought, negative numbers mean sold). Then to get the total shares owned you run a simple total query and add them up (using SUM function). You've made the same mistake with your [Income] and [Deposits] fields.

Additionally, you shouldn't store data that can be calculated. That goes for the balance you mentioned in your posting as well as the 2 Total fields you currently have. You don't need those, when you do need the totals, you run a query to calculate them.

In that same vein, you don't need to store the FiscalYear and FiscalMonth data. That too is calculable. You have the Transaction date, you can simply determine the fiscal year/month that date falls into via a query.

I really suggest you read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) before going forward. Right now you essentially are using Access as Excel. Might as well move back to Excel if that's how you are going to work.

 

[gemma-the-husky]

I generally have a transaction table to store all transactions, and then add carryforward and broughtforward items with a designated type, as part of a period end/reconciliation process.

eg type 98 for a c/f and type 99 for a b/f

you can then do the next reconciliation starting with the latest b/f value.

{similar to what arnelgp suggested, but all in a single table}
{plogs points are also germane. it's much easier to use a single column for all the transaction values}

 

[access2010]

Thank you all for your suggestions for our Access 2003 database

Could I please have this database corrected to enable our office to have a opening cash balance from the end of the previous month and at the end of the current month

Could I please receive the query code adjustment to our formula for the profit or loss amount as there should only have a value if a sale has been made

Your suggestions are appreciated

Thank you
Barbara