SELECT TestProjtCostMonth.ProjMthCostID, TestProjtCostMonth.CostReducProjtID, TestProjtCostMonth.ImplementDate, IIf([ImplementMon]="",1,[ImplementMon]) AS ImplementMonth, Format([ImplementDate],"yyyy") AS ImplementYr, Format([ImplementDate],"m") AS ImplementMon, TestProjtCostMonth.DollarValueFullYr, TestProjtCostMonth.StartMonth, TestProjtCostMonth.StartDate, TestProjtCostMonth.ProjCostA, TestProjtCostMonth.ProjMonthB, TestProjtCostMonth.ProjCostDateB, TestProjtCostMonth.ProjCostB, TestProjtCostMonth.ProjMonthC, TestProjtCostMonth.ProjCostDateC, TestProjtCostMonth.ProjCostC, TestProjtCostMonth.ProjMonthD, TestProjtCostMonth.ProjCostDateD, TestProjtCostMonth.ProjCostD, TestProjtCostMonth.ProjMonthE, TestProjtCostMonth.ProjCostDateE, TestProjtCostMonth.ProjCostE, TestProjtCostMonth.ProjMonthF, TestProjtCostMonth.ProjCostDateF, TestProjtCostMonth.ProjCostF, TestProjtCostMonth.ProjMonthG, TestProjtCostMonth.ProjCostDateG, TestProjtCostMonth.ProjCostG, TestProjtCostMonth.ProjMonthH, TestProjtCostMonth.ProjCostDateH, TestProjtCostMonth.ProjCostH, TestProjtCostMonth.ProjMonthI, TestProjtCostMonth.ProjCostDateI, TestProjtCostMonth.ProjCostI, TestProjtCostMonth.ProjMonthJ, TestProjtCostMonth.ProjCostDateJ, TestProjtCostMonth.ProjCostJ, TestProjtCostMonth.ProjMonthK, TestProjtCostMonth.ProjCostDateK, TestProjtCostMonth.ProjCostK, TestProjtCostMonth.EndMonth, TestProjtCostMonth.EndDate, TestProjtCostMonth.ProjCostL, IIf([ImplementMonth]=1,[ProjCostA]+[ProjCostB]+[ProjCostC]+[ProjCostD]+[ProjCost
E]+[ProjCostF]+[ProjCostG]+[ProjCostH]+[ProjCostI]+[ProjCostJ]+[ProjCostK]+[Proj
C
ostL],IIf([ImplementMonth]=2,[ProjCostA]+[ProjCostB]+[ProjCostC]+[ProjCostD]+[Pr
o
jCostE]+[ProjCostF]+[ProjCostG]+[ProjCostH]+[ProjCostI]+[ProjCostJ]+[ProjCostK],
I
If([ImplementMonth]=3,[ProjCostA]+[ProjCostB]+[ProjCostC]+[ProjCostD]+[ProjCostE
]
+[ProjCostF]+[ProjCostG]+[ProjCostH]+[ProjCostI]+[ProjCostJ],IIf([ImplementMonth
]
=4,[ProjCostA]+[ProjCostB]+[ProjCostC]+[ProjCostD]+[ProjCostE]+[ProjCostF]+[Proj
C
ostG]+[ProjCostH]+[ProjCostI],IIf([ImplementMonth]=5,[ProjCostA]+[ProjCostB]+[Pr
o
jCostC]+[ProjCostD]+[ProjCostE]+[ProjCostF]+[ProjCostG]+[ProjCostH],IIf([Impleme
n
tMonth]=6,[ProjCostA]+[ProjCostB]+[ProjCostC]+[ProjCostD]+[ProjCostE]+[ProjCostF
]
+[ProjCostG],IIf([ImplementMonth]=7,[ProjCostA]+[ProjCostB]+[ProjCostC]+[ProjCos
t
D]+[ProjCostE]+[ProjCostF],IIf([ImplementMonth]=8,[ProjCostA]+[ProjCostB]+[ProjC
o
stC]+[ProjCostD]+[ProjCostE],IIf([ImplementMonth]=9,[ProjCostA]+[ProjCostB]+[Pro
j
CostC]+[ProjCostD],IIf([ImplementMonth]=10,[ProjCostA]+[ProjCostB]+[ProjCostC],I
I
f([ImplementMonth]=11,[ProjCostA]+[ProjCostB],IIf([ImplementMonth]=12,[ProjCostA
]
,"")))))))))))) AS FullYrBF
FROM TestProjtCostMonth;

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/blush.gif)

Although I am not sure what is your question... you table is not normalized... you have repeating groups (ProjCostA, ProjCostB, ProjCostC, etc.)...

You should fix you structure before continuing... search the forums for Normalization, there are good posts about the topic.

This goes back to what I was telling you in the other thread that we just got working. Repeating groups is not proper design.

When you get the nomalization issue handled, you can also tighten up your query, by not qualifing your fields with the table name. This is only necessary for multiple table queries. Even then, I alias the table name with something short.

Your problem is that your table has spreadsheet disease. That is you set up your table the way you would in Excel. Your column names are pulling double duty as criteria on some level.

You might want to consider restructuring your table to something like thisso

Project | Cost | Project Month|
_________________________
A 545 January
A 545 February
A 545 March
B 545 January
B 545 February
C 545 January
C 545 February

I did try normalizing but I could not figure out how to get the fields to show horizontally instead of virtically on the form, plus when the user has to enter the infomation manually they wanted the months to show from Jan to Dec automatically when there is no ImplementDate set yet and when one does get assigned to have the months changed order accordingly but not affect the maunally entered data.

It was the only solution I could come up with and yes I am aware it breaks all the normalization rules.

The form I am attaching show how the user wants it to look by default until an Implement date is assigned.

If you insist on breaking normalization in order to accommodate form layout.... how do you want us to help you?? The difficulties you encounter now and will encounter as you move forward will not be solved by 'band-aid' solutions, at least not here, at UA.

Thought some more about how to work the information the way the user wants it. Unfortunatly alot of people where I work cling to excel and it is hard convincing them that access does not work the same way.

I did renormalize the database the way I had it originally see attached Relaionship. I need two different tables to hold information. One for when the user clicks the button to do the calcualtions automatically, the other to hold the information entered manually. The

The code that runs the form is shown below.

Private Sub BtnUpdateAutoData_Click()
On Error GoTo Err_BtnUpdateAutoData_Click
Dim CurMonthCost As Currency

This code first deletes the information before rerunning the code to repopulate the information.

DoCmd.OpenQuery "DeleteQry"

If Not IsNull([ImplementDate]) Then

If Day(Me![ImplementDate]) = 1 Then
CurMonthCost = Me![DollarValueFullYr] / 12
ElseIf Day(Me![ImplementDate]) = 15 Then
CurMonthCost = Me![DollarValueFullYr] / 24
End If

For i = 0 To 11
strSQL = "INSERT INTO UpdateTest(ProjCostMonth, Amount, CostReducProjtID) "
strSQL = strSQL & "VALUES(#" & DateAdd("m", i, Me!ImplementDate) & "#, " & CurMonthCost
strSQL = strSQL & ", " & Me.CostReducProjtID & ");"
CurrentDb.Execute strSQL
Next i

End If

Me.Refresh
Me.Repaint

Exit_BtnUpdateAutoData_Click:
Exit Sub

Err_BtnUpdateAutoData_Click:
MsgBox Err.Description
Resume Exit_BtnUpdateAutoData_Click
End Sub
-----------------
Private Sub CalcTestBtn_Click()
On Error GoTo Err_CalcTestBtn_Click
Dim CurMonthCost As Currency

If Not IsNull([ImplementDate]) Then

If Day(Me![ImplementDate]) = 1 Then
CurMonthCost = Me![DollarValueFullYr] / 12
ElseIf Day(Me![ImplementDate]) = 15 Then
CurMonthCost = Me![DollarValueFullYr] / 24
End If

For i = 0 To 11
strSQL = "INSERT INTO UpdateTest(ProjCostMonth, Amount, CostReducProjtID) "
strSQL = strSQL & "VALUES(#" & DateAdd("m", i, Me!ImplementDate) & "#, " & CurMonthCost
strSQL = strSQL & ", " & Me.CostReducProjtID & ");"
CurrentDb.Execute strSQL
Next i

Else

For i = 1 To 12
strSQL = "INSERT INTO TestMonthCost(ProjMonth, CostReducProjtID) "
strSQL = strSQL & "VALUES( " & i & "," & Me.CostReducProjtID & ");"
CurrentDb.Execute strSQL
Next i

End If

Me.Refresh
Me.Repaint

Exit_CalcTestBtn_Click:
Exit Sub

Err_CalcTestBtn_Click:
MsgBox Err.Description
Resume Exit_CalcTestBtn_Click

End Sub

The query I showed above is gone completely