Database Sum 1

[liriom]

I have an MS Access Database. It has two tables.

Sample Tables:

tbl_Consulting_$sUsed
Fields:
Consultant Name: xyz consultant
Jan 2003: 0
Feb 2003: 50
Mar 2003: 0
Jan 2004: 0
Feb 2004: 20
Mar 2004: 50
Jan 2005: 0
Feb 2005: 10
Mar 2005: 20

tbl_Consulting_Init_Yrly$
Fields:
Consultant Name: xyz consultant
Init$: 100
InitialMonth: Feb

Basically, there are many consultants, each have a startup month(different for every record)and pay x amount of dollars (different for every record). They have one year from the startup month to expend all the money.

I need a way to sum up all the fields for all the rows and deducted from the initial amount per year.

Please help, I'm new to access.

 

[PHV]

I strongly suggest you take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[liriom]

ok but how is this going to help me code the problem at hand

 

[PHV]

You can't redesign the database ?
You will change the fields names all the time ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[liriom]

no, i cannot change the database, and the fields are not going to change.

 

[dc20]

Would it be sufficient to just have a field in a query that is an expression of ([init] - ([jan]+[feb]+...)) for all your records ?

If the months before initialization are zero, then summing all the months available wouldn't affect the answer. Does it matter if the total is negative or do you have to stop adding when a month when it goes negative ?

 

[liriom]

Every year it renews so:

Jan 2003: 0
Feb 2003: 50
Mar 2003: 0
Jan 2004: 0
Feb 2004: 20
Mar 2004: 50
Jan 2005: 10
Feb 2005: 10
Mar 2005: 20

Init$: 100
InitialMonth: Feb

Total2003 = 100 - 50
Total2004 = 100 - 60
Total 2005 = 100 - 50

Plus every consultant renews depending on the month they join, so some are jan others feb until dec.

I'm new to access but long time programmer.

I have the logic workout, I just don't know how to code it in access

 

[liriom]

Let me modified the question, I need to know how to code in access (module, macro, whatever) so i can access the table. so my real question is how do i access the tables and write the respond based on a loop that goes through all the records based on the month of first sign on and totals.

If initialmonth = jan then
total2003 = sum(jan2003todec2003)
elseif initialmonth = feb then
total2003 = sum(feb2003tojan2003)
elseif initialmonth = mar then
total2003 = sum(mar2003tofeb2003)

and so on till total 2003

and then for 2004 and 2005.

 

[VBslammer]

I'm wondering if your schema only contains 3 months of the year of whether you have Jan - December for each year (you only show Jan - Mar). Basically you have a spreadsheet going there, so each consultant only needs a single record to record all their data. Have you tried making a report yet?

dc20 is on the right track, you just have to work out the business logic in a query or report. Based on the schema you've shown, if a consultant gets [Init$] in Feb 2003, then the year goes from Feb 2003 to Jan 2004, right? Based on that assumption, the logic would be:

<Pseudocode>
If [Init$] = "Jan"
  Total2003 = [Init$]-[Jan 2003]+[Feb 2003]+[Mar 2003]
  Total2004 = [Init$]-[Jan 2004]+[Feb 2004]+[Mar 2004]
  Total2005 = [Init$]-[Jan 2005]+[Feb 2005] [Mar 2005]
Else If [Init$] = "Feb"
  Total2003 = [Init$]-[Feb 2003]+[Mar 2003]+[Jan 2004]
  Total2004 = [Init$]-[Feb 2004]+[Mar 2004]+[Jan 2005]
  Total2005 = [Init$]-[Feb 2005]+[Mar 2005] [red]no 2006 field[/red]
Else If [Init$] = "Mar"
  Total2003 = [Init$]-[Mar 2003]+[Jan 2004]+[Feb 2004]
  Total2004 = [Init$]-[Mar 2004]+[Jan 2005]+[Feb 2005]
  Total2005 = [Init$]-[Mar 2005][red] no 2006 fields[/red]

You could make a query to do this using the Switch function, which would look something like this:

SELECT u.[Consultant Name], 
(y.[Init$]-Switch(y.[InitialMonth]='Jan',CCur(u.[Jan 2003]+u.[Feb 2003]+u.[Mar 2003]),y.[InitialMonth]='Feb',CCur(u.[Feb 2003]+u.[Mar 2003]+u.[Jan 2004]),y.[InitialMonth]='Mar',CCur(u.[Mar 2003]+u.[Jan 2004]+u.[Feb 2004]))) AS Total2003, (y.[Init$]-Switch(y.[InitialMonth]='Jan',CCur(u.[Jan 2004]+u.[Feb 2004]+u.[Mar 2004]),y.[InitialMonth]='Feb',CCur(u.[Feb 2004]+u.[Mar 2004]+u.[Jan 2005]),y.[InitialMonth]='Mar',CCur(u.[Mar 2004]+u.[Jan 2005]+u.[Feb 2005]))) AS Total2004, (y.[Init$]-Switch(y.[InitialMonth]='Jan',CCur(u.[Jan 2005]+u.[Feb 2005]+u.[Mar 2005]),y.[InitialMonth]='Feb',CCur(u.[Feb 2005]+u.[Mar 2005]),y.[InitialMonth]='Mar',CCur(u.[Mar 2005]))) AS Total2005, 
y.[Init$], y.InitialMonth
FROM [tbl_Consulting_Init_Yrly$] AS y 
INNER JOIN [tbl_Consulting_$sUsed] AS u 
ON y.[Consultant Name] = u.[Consultant Name];

The output format would be like this:

Consultant Name   Total2003   Total2004    Total2005       Init$     InitialMonth
X Consultant       $90.00        $90.00        $80.00        100         Mar
Y Consultant       $80.00        $85.00        $60.00        100         Jan
Z Consultant       $70.00        $100.00       $100.00       100         Feb

Forgive me, but I couldn't help but laugh at this statement:

no, i cannot change the database, and the fields are not going to change.

I love that. But all kidding aside, that's a hideous schema and I'd be at the next staff meeting with an arsenal of reasons why it needs to change and it needs to change NOW!

VBSlammer

Unemployed in Houston, Texas

 

[dc20]

I must be missing something, since I can't see your equations fitting the data. Seems like it should be...

Total2003 = 100 - 50
Total2004 = 100 - 70
Total 2005 = 100 - 40

Do you want to pick up Jan03 or Jan04 for Feb03 ?
Otherwise it seems like you still just sum up the whole 12 months of the year in all cases.

total_2003a = sum(feb2003tojan2003)
total_2003b = sum(mar2003tofeb2003)

Aren't total_2003a and total_2003b the same amount ?

 

[liriom]

I know, is pretty frustating to not format this database to something better, but i'm stuck with it.

Thanks for the switch command, I think this is what i was looking for, will implement and see what happens.

To answer your question, it goes Jan-Dec, I used Jan-Mar as an example, I didn't want to write all the months out.

Right now I get some aggregate function error thingy, but I'm pretty tire, so I'll tackle it in the morning.

 

[willir]

liriom

PHV and vbSlammer are correct. Your design is problematic...
- you have to hard code each year and month
- you may run into the 255 field limitation maximum for a table
- with a poor design, it is like pulling teet to extract the information.

Basically, the design you have presented is more like a spreadsheet, and does take advantage of the power of a relational database. Chosing appropriate db software

Please note that it is much easier to "fix" a design problem early on rather than later when you have a lot of data to migrate. I can understand if the design is controlled by another or you have invested a lot of time into creating the forms. But "pay me now, or pay me lots and lots later".

For example, look at the query work provided by vbSlammer - the months and years have to be hard coded, and this is just for one consultant.

Consider something like...

tblConsultant
ConsultantID - primary key
ConsultantLN - last name
ConsultantFN - first name
...etc (contact info)

tblBank
BankID - primary key
ConsultantID - foreign key to tblConsultant
BankedDate - date field
BankedAmount - currency

And then either...
tblConsultFees
ConsultantID - foreign key to tblConsultant
ConsultPeriod - date field
ConsultAmount - currency

Primary key - ConsultantID + ConsultPeriod

Discussion:
ConsultPeriod - Althought this is a date field, it is formulated to use the month, ie., 2005-01-01 (Jan), 2005-02-01 (Feb)... As with your design, you have to add up the amount for each month manually and enter it.

Or forget the month and just track the transaction date and let Access calculate the monthly sums for you...

tblConsultFees
ConsultFeesID - primary key
ConsultantID - foreign key to tblConsultant
ConsultDate - date field
ConsultAmount - currency

Now you just enter the consulting fees when incurred.

So why is this type of design better:
- You don't have to change / add the field names for each consultant and for each year.
- Your queries become so, so, so much easier. Do you see the query vbSlammer created for you? Next year, you will have to edit it for 2006 because the query is hard-coded for the month and year.
- You will have historical records for past years all in one table -- makes a great comparison tool on performance, spending, budgeting...

Hey vbSlammer kudos on using the switch function to man-handle that awful select statement. When can you change the "Unemployed in Houston"? It must be tough.

Richard

 

[liriom]

Thank you all for your comments, the Switch worked like a charm! Long Long switch, but it works.

 

[dc20]

I'm still curious about this

Do you want to pick up Jan03 or Jan04 for Feb03 ?
Otherwise it seems like you still just sum up the whole 12 months of the year in all cases.

total_2003a = sum(feb2003tojan2003)
total_2003b = sum(mar2003tofeb2003)

Aren't total_2003a and total_2003b the same amount ?

 

[liriom]

I have to sum every year, a year is 12 months from the initial month.

SO, if you signup on feb, then your year goes from feb.2003 to jan2004.

and there is a total needed for every year.

 

[dc20]

ok, that's different than what you showed, being all contained in the 2003. That's what I figured.

You had...

If initialmonth = jan then
total2003 = sum(jan2003todec2003)
elseif initialmonth = feb then
total2003 = sum(feb2003tojan2003)
elseif initialmonth = mar then
total2003 = sum(mar2003tofeb2003)