Assistance Needed: Auto-Populating Weekday & Updating Actual Spent and Balance Column in Access

[Papie]

Hello everyone,

I'm working on a Microsoft Access database to track transactions and monthly allocations, but I've run into a few challenges. I’d appreciate any guidance, modification, addition or subtraction on the following issues:

The purpose of this database is to track my daily, weekly, and monthly transactions, analyze my spending patterns, and make informed decisions to improve my savings.


1. Auto-Populating the Weekday Based on Transaction Date

• I have a tblTransactions table where I enter transaction records.
• I want the Week_Day column to be automatically populated based on the Trans_Date field.
• I am using a form called frmTransactionEntry to enter records into the database.

2. Updating Allocated Amount & Actual Spent Automatically

• Every time I enter a transaction using frmTransactionEntry, I want the Amount to be deducted from the Allocated_Amount for the respective category in tblMonthlyAllocation.
• The Actual_Spent column should be updated accordingly, and the Balance should reflect the new amount left for that category in the given month.

3. Handling Negative Balances & Zero Transactions

• If the Actual_Spent exceeds the Allocated_Amount, the Balance should display a negative amount.
• If no transactions have been recorded for a category, both Actual_Spent and Balance should show zero instead of being empty.

I've tried using queries and VBA, but I keep running into errors such as type mismatches and non-updatable queries. Any help or alternative approaches would be greatly appreciated.

TABLE DETAILS

1. tblCategories

This table contains three columns:

• Categories_ID (Short Text) – An abbreviation of the category name (e.g., Grocery = GROC, Transportation = TRANS, Medical/Health = MED/H).
• Categories_Name (Short Text) – The full name of the category, such as Grocery, Medical/Health, Transportation, Bills, etc.
• Type (Short Text) – Specifies whether the category is for income or expense.

2. tblMonthlyAllocation

This table tracks allocated amounts and spending for each category per month. It consists of seven columns:

• Allocation_ID (AutoNumber) – Unique identifier for each record.
• Month_Year (Date/Time) – Represents the month and year of the allocation.
• Allocated_Amount (Currency) – The budgeted amount for a specific category in a given month (e.g., Grocery = 200, Medical/Health = 100, Transportation = 50).
• Actual_Spent (Currency) – The total amount spent in a category for the month.
• Balance (Currency) – The remaining amount after spending.
• Categories_ID (Short Text) – Links the record to a category in tblCategories.
• Note (Long Text) – Provides additional details about the allocation.

3. tblTransactions

This table records individual transactions and contains eight columns:

• Trans_ID (AutoNumber) – Unique identifier for each transaction.
• Week_Day (Short Text) – Stores the day of the week (e.g., Sunday to Saturday) based on the transaction date.
• Trans_Date (Date/Time) – The date of the transaction.
• Amount (Currency) – The amount spent on the transaction.
• Payment_Method (Short Text) – Indicates how the transaction was paid (e.g., cash, credit card, visa card).
• Categories_ID (Short Text) – Links the transaction to a category in tblCategories.
• Shop_ID (Number) – Identifies the shopping center where the transaction took place.
• Description (Long Text) – Provides additional details about the transaction.

4. tblShoppingCenter

This table stores details about shopping locations and consists of three columns:

• Shop_ID (AutoNumber) – Unique identifier for each shop.
• Shop_Name (Short Text) – The name of the shop where the transaction occurred.
• Address (Short Text) – The location or address of the shop.

Thanks in advance for your time and expertise!

[jojowhite]

I don't think Actual_Spent and Balance field is needed, since you can Calculate
them through Query.

Category_ID should also be Autonumber then add Category as your Abbreviation.
It is more faster to index/lookup on a Numeric field, you only compare their value.
while Text, you need to compare character by character.

[madpiet]

Oh, where to start. One, I'd do it in Excel. LOL. Really, I would.

You don't really need Weekday if you have transdate. you can just write a query to get that. (and a bunch of other date attributes... month, year, quarter, etc).

2. Updating Allocated Amount etc. you don't store this. you calculate it on the fly with a query.
3. Balance stuff. That should be calculated too. The rest is formatting (the control on your form/report, and not in the table).

Just for fun, lookup Chandoo on YouTube. He does pretty much this, but in Excel using DAX. (although, I can see why you might want to stay away from it... it's super weird if you come from a database background).

[mike60smart]

Hi
Can you upload a copy of your database?

[Papie]

Thank you so kindly I think this method look better. I will try it.

[Papie]

thank you so kindly I will try these methods I was doing this in excel for a while just decided to step it up with MS access and thank for for the YouTube reference as well.

[Papie]

Thank you for the link I will PM you just in case.

[Micron]

If you stick with Access, it is considered poor design practice to store calculations. These calculations should not be stored:
- week day (which you don't need as a table field)
- allocated amount
- balance
- and perhaps actual spent

[Papie]

Hey Micron, thanks for the insight! I have a question—if I don’t store the allocated amount, how can I determine the total amount allocated to a category for the month?


For example, how would I know how much was allocated for groceries, medical expenses, bills, etc.?

[jojowhite]

you create a Query for the Allocation for each Category and Month/Year

1. qryCategoryAllocMnYr

select Category_ID, Month(Month_Year) As Mn, Year(Month_Year) As Yr, Allocated_Amount from  tblMonthlyAllocation;

from your transaction table, create an aggregate (sum) query all your expenditure and group them by Category_Id, Month and Year:

2. qryTransCatMnYr

select Category_ID, Month(Trans_Date) As Mn, Year(Trans_Date) As Yr, Sum(Actual_Spent) As MonthExpense
from tblTransactions 
group by Category_ID, Month(Trans_Date), Year(Trans_Date);

to get the Balance( or overspent) from each category by Month and Year,
join the two queries:


3.qryCatAllocBalance

Select A.Category_ID, 
A.Mn As [Month], 
A.Yr As [Year], 
A.Allocated_Amount, 
Val(B.MonthExpense & "") As Spending, 
(A.Allocated_Amount - Val(B.MonthExpense & "")) As Allocation_Balance 
from qryCategoryAllocMnYr As A 
Left Join qryTransCatMnYr As B 
On A.Category_ID = B.Category_ID And A.Mn = B.Mn And A.Yr = B.Yr;

[Micron]

Depending on how many records you will have, DSum with criteria is another way - a form with 3 calculated controls. One Dsum for each plus and minus sums, the 3rd to subtract them from each other. Aggregate functions over domains (tables) are not efficient so not a query with DSum.

Depending on your data and/or preferences, you could have 1 field for transactions, both plus and minus. Then it can be one DSum with criteria and the result will be the sum of all negative and positive values. You'd need two when your negative and positive values are in separate fields.