How do you run formulas in SQL to update a table based on new information?

Hello Experts.

I have a database (or am designing a database) for SQL compact 3.5, and as I haven't done this before, I know that the planning is everything.

In my attachment, you will see my tables and the primary key and foreign key relationships in the first page.  And in the second page you will see an example of the tables in use.  I didn't upload or print the row and column headings as I need to understand the SQL table code.

Question:
How can I calculate the "Current Balances" in the "Expense Accounts" and "Payment Types" tables?  How should I setup a table to show the deposits/transactions for an account/payment type by month?  I am not sure if I should have a separate month table to log that data or not.  In the program, I will generate a code that automatically creates a new month feild/column that will store that months date.

This is my first database and I am trying to understand how they work.

Thank You.

Mike
Database-Tables.pdf
missionarymikeAsked:
Who is Participating?
 
Daniel WilsonCommented:
The DDL command would be (if I recall your field names correctly)
Create Unique Index unq_Allowance on Allowance (ExpenseAccount, [Year], [Month])
0
 
Daniel WilsonCommented:
>> In the program, I will generate a code that automatically creates a new month feild/column that will store that months date.

No, no.  More rows, not more columns.

Your transactions table looks good.  You have the date.  Reporting on it by month will not be difficult.

>>How can I calculate the "Current Balances" in the "Expense Accounts" and "Payment Types" tables?

You could set up a trigger on your Transactions table to update those ... but it is probably better not to even have those fields in the Expense Accounts table.  Calculate them with a SUM() function when you report on the data.

0
 
missionarymikeAuthor Commented:
This main issue is complex.

First, when a deposit is entered (unless it is for a specific account) will be split up by percentages that are setup in the allowances table.  So the database will need to take the deposits from that month only and divide them up by a preset % and dumped into the expense accounts.  Therefor to get the accurate balance of the expense account, the database will need to sum all of the "monthly" columns that are created.  If an error is found in a transaction 3 months back, the user can change it and the deposits will automatically update all the %'s of that month and then recalculate the totals.

Does that make sense?

Mike
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Walter RitzelSenior Software EngineerCommented:
Agreed with DanielWilson. Does not seems to be necessary to have those fields in the tables.

To report on data, you should create SELECT statements, like this one:
SELECT datepart('yyyy',date), datepart('m',date), expense_code, sum(payment)
from transaction
group by datepart('yyyy',date), datepart('m',date), expense_code

The syntax could be wrong, but this is the principle is that.
0
 
missionarymikeAuthor Commented:
Thanks guys.  I did not provide an example for the allowances table (my mistake).  I need to be able to let the user select the monthly allowance for each individual month (% of total income for the month),  The allowances may be different month-to-month.  So somehow I need a table that will allow the user to enter how much of their income they want allocated to each expense code for the month.  I agree that to calculate, this could all be dynamic, but I need the database to calculate the deposits for a month multiplied by the % for each month and then add the total.

Look at the attached file for the allowances.  I need a table/field/column/row/?? for each month.

Mike

Database-Tables-2.pdf
0
 
Daniel WilsonCommented:
A row for each month/expense code combination.
0
 
missionarymikeAuthor Commented:
Do you mean, have the expense codes as the column headings and have the monthly info as the rows/records?  How would that work if the user adds/deletes/modifies the expense codes?

Thanks.

Mike
0
 
missionarymikeAuthor Commented:
Is this a better use of the allowances table.  I have adjusted the table in the examples attachment.

Will this cause a problem with the redundancy of the use of expense codes?

Mike

Database-Tables-3.pdf
0
 
Daniel WilsonCommented:
Yeah, that one looks better.

You won't be able to set the expense codes as a key ... but they could and probably should be part of a key/unique index that would include the month & year to which they apply.
0
 
missionarymikeAuthor Commented:
Can you elaborate.  I don't quite understand.  What prevents them from being a key?

Thanks.

Mike
0
 
Daniel WilsonCommented:
They won't be unique.

Auto Expenses     2009   May
Groceries               2009 May
Auto Expenses     2009  June
0
 
missionarymikeAuthor Commented:
Okay, so they won't be unique.  But can they still be a foriegn key to the Expense Code table so that they would be updated/changed if the expene code is renamed?

Mike
0
 
missionarymikeAuthor Commented:
That is also why i had the ID field.  That might be the only unique feild.  I thought the primary/foreign key relationship would allow you to connect fields togehter so they could be update more easily.

Mike
0
 
Daniel WilsonCommented:
Using the ID field as your key is certainly an option ... probably your best in this case.  But I would probably also create a unique index on that set of 3 fields to prevent duplication -- e.g. 2 Auto Expense entries for the same month.
0
 
missionarymikeAuthor Commented:
How do you create this "index" for the 3 fields together and not individually?

Mike
0
 
missionarymikeAuthor Commented:
Great.  1 last question.  This table/tables will be able to take:

1) all transaction deposits to a type code (ARVEST) and
2) multiply by the percentage for each allowance, each month and
3) add all the months together so that a grand total can be acheived

Correct?

Mike
0
 
missionarymikeAuthor Commented:
I will be a little clearer, and repost the tables for a final review.  Thanks again.

Mike
0
 
Daniel WilsonCommented:
correct
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.