Solved

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

Posted on 2009-05-06
18
172 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:missionarymike
  • 10
  • 7
18 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24321418
>> 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
 

Author Comment

by:missionarymike
ID: 24321474
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
 
LVL 15

Expert Comment

by:Walter Ritzel
ID: 24321480
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
 

Author Comment

by:missionarymike
ID: 24321527
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24321584
A row for each month/expense code combination.
0
 

Author Comment

by:missionarymike
ID: 24321605
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
 

Author Comment

by:missionarymike
ID: 24327122
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24329676
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
 

Author Comment

by:missionarymike
ID: 24329719
Can you elaborate.  I don't quite understand.  What prevents them from being a key?

Thanks.

Mike
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24329949
They won't be unique.

Auto Expenses     2009   May
Groceries               2009 May
Auto Expenses     2009  June
0
 

Author Comment

by:missionarymike
ID: 24330048
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
 

Author Comment

by:missionarymike
ID: 24330066
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
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24330145
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
 

Author Comment

by:missionarymike
ID: 24330276
How do you create this "index" for the 3 fields together and not individually?

Mike
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 24332030
The DDL command would be (if I recall your field names correctly)
Create Unique Index unq_Allowance on Allowance (ExpenseAccount, [Year], [Month])
0
 

Author Comment

by:missionarymike
ID: 24332214
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
 

Author Closing Comment

by:missionarymike
ID: 31578807
I will be a little clearer, and repost the tables for a final review.  Thanks again.

Mike
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24334975
correct
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Group by and order by clause 28 36
SQL Server creating a temp table 7 39
Azure SQL DB? 3 19
Slow SQL query 12 26
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now