gigifarrow
asked on
Reduced Query that was over 1000 characters and still over What is another alternative?
first I like to thank all the experts that have got me this far. I reduced the characters and I am still over!
Below are three IIf statements. Based on what the Production number is these fields that are check marks equal certain amounts once they have been checked.
All this code is calculated in a field called Percentages.
If I am not able to use it in a query how would I do the other alternative?
,IIf([Production] Between 1 And 132 Or [Production] Between 134 And 136 Or [Production] Between 140 And 146 Or [Production]= 151 Or 152 Or [Production] Between 158 And 164 Or [Production] Between 167 And 237,Format(Abs((Induction*
FROM tblFortCarsonFullup AS F;
Below are three IIf statements. Based on what the Production number is these fields that are check marks equal certain amounts once they have been checked.
All this code is calculated in a field called Percentages.
If I am not able to use it in a query how would I do the other alternative?
,IIf([Production] Between 1 And 132 Or [Production] Between 134 And 136 Or [Production] Between 140 And 146 Or [Production]= 151 Or 152 Or [Production] Between 158 And 164 Or [Production] Between 167 And 237,Format(Abs((Induction*
FROM tblFortCarsonFullup AS F;
Or, create a table of [Production] and a code for whatever percentage will be used, and join that to your original table. Then, you are not checking for a [Production] value range, but just a single value in the percentage code field.
I created tblPctCode, with fields Production (number, long) and pctCode(number, long)
Using your number ranges, I created 300 records, and in PctCode put 1, 2, or 3, for the 3 formulas you had for the iif conditions.
I then created tblFormula, with fields pctCode(Number, long) and Formula (memo). I created 3 records, one for each PctCode, with the corresponding formula for each iif condition in your query.
I joined the two new tables to root table (~FullUp), Production on Production and PctCode on PctCode, and used EVAL on the formula.
SELECT F.Production, F.InShopDate, F.SerialNumber, F.Unit, F.Induction, F.TearDown, F.Provision, F.E1GroundInsert, F.ReliefHoleExtinguisher, F.AssemblyA, F.FuelShotOffSleeve, F.AsemblyB, F.CEPUpgrade, F.AFESCEPSwitch, F.AssemblyC, F.ERRHandle, F.DriverSeat, F.AssemblyD, F.HotBoxEnhanceMent, F.AssemblyE, F.LegacyBattery, F.[LED/ERR], F.T161Track, F.AFES, F.HotBox, F.BASSM2, F.GunnersSeatStopRework, F.AOA, F.BRATP, F.RoadTest, F.QC, F.OutDuction, F.Location, F.DateCompleted, F.DateReturned, F.Remarks, F.Percentage, F.HotBox57K6608, F.RollerHousingMOD, F.FeriteBead,
Eval([c].[formula]) AS Percentages
FROM (tblTempleFacilityFullup AS F INNER JOIN tblPctCode AS P ON F.Production = P.Production) INNER JOIN tblFormula AS c ON P.PctCode = c.PctCode;
I'll post the 2 tables shortly
Using your number ranges, I created 300 records, and in PctCode put 1, 2, or 3, for the 3 formulas you had for the iif conditions.
I then created tblFormula, with fields pctCode(Number, long) and Formula (memo). I created 3 records, one for each PctCode, with the corresponding formula for each iif condition in your query.
I joined the two new tables to root table (~FullUp), Production on Production and PctCode on PctCode, and used EVAL on the formula.
SELECT F.Production, F.InShopDate, F.SerialNumber, F.Unit, F.Induction, F.TearDown, F.Provision, F.E1GroundInsert, F.ReliefHoleExtinguisher, F.AssemblyA, F.FuelShotOffSleeve, F.AsemblyB, F.CEPUpgrade, F.AFESCEPSwitch, F.AssemblyC, F.ERRHandle, F.DriverSeat, F.AssemblyD, F.HotBoxEnhanceMent, F.AssemblyE, F.LegacyBattery, F.[LED/ERR], F.T161Track, F.AFES, F.HotBox, F.BASSM2, F.GunnersSeatStopRework, F.AOA, F.BRATP, F.RoadTest, F.QC, F.OutDuction, F.Location, F.DateCompleted, F.DateReturned, F.Remarks, F.Percentage, F.HotBox57K6608, F.RollerHousingMOD, F.FeriteBead,
Eval([c].[formula]) AS Percentages
FROM (tblTempleFacilityFullup AS F INNER JOIN tblPctCode AS P ON F.Production = P.Production) INNER JOIN tblFormula AS c ON P.PctCode = c.PctCode;
I'll post the 2 tables shortly
Here they are. If you keep up with the PctCodes and the formulas, your SQL should be stable through additions, deletions and changes of Production values and associated formulas.
HelpWithQuery---NewTables.accdb
HelpWithQuery---NewTables.accdb
ASKER
Wow thanks again experts for taking the time to answer my question. I will try this and see if it works. and get back to you. At work now will try when I get off. I was always told not to use calculations in access tables. But I will try everything that was advised.
It is not a calculation. It is a string, which becomes a calculation within the query.
ASKER
Okay I have joined it like you said but Im getting a error. In the Percentage formula. Here is a copy of what i did.
HelpWithQuery---NewTables.accdb
HelpWithQuery---NewTables.accdb
I missed something when I checked this on my PC previously. May have to go back to drawing board.
ASKER
Hello, again is there anybody out there that can elaborate on my question? I have tried what the other export told me to do but I am getting a error message. Which I dont understand why. Above is my database example.
OK, I created a function which will use the formula. Do you expect the results to be either 100% or 0 %?
I am going to check this out a bit before I post this time.
I am going to check this out a bit before I post this time.
OK, I see now. I am guessing you want the percentage to change as you toggle the Y/N fields on the form for each step. I need to adjust the query and code to do this if that is true.
ASKER
Exactly!!
Everytime the user checks a field it shows what percentage that they are at.
If you add all the sums of each of them It comes out to 100.
I put the "%" so that the user will see it as 100% or 40% what ever it comes out to be.
Everytime the user checks a field it shows what percentage that they are at.
If you add all the sums of each of them It comes out to 100.
I put the "%" so that the user will see it as 100% or 40% what ever it comes out to be.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you !!
You have done a excellent job! Just curious, did you go to school to learn this or did you learn this on your own?
You have done a excellent job! Just curious, did you go to school to learn this or did you learn this on your own?
On my own. Here at EE, and at previous job at OTC.
You need to move this into VBA code and call the procedures from the query.
Anything you need for the calculation will need to be passed to the function. If you have more then one value to pass back to the query, you'll need a seperate procedure for each.
Jim.