Full Version: Query works but it is HUGE
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
TitinaLorey
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;
blush.gif
DougY
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.
ScottGem
This goes back to what I was telling you in the other thread that we just got working. Repeating groups is not proper design.
Ender
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.
DataPig
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
TitinaLorey
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.
DougY
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.
TitinaLorey
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.
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
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.