Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

Display results from SQL within an Access Database form....

I need to apply the following weighting to the results, on a per row ID basis in to a new field BIM_KPI
The weighting that need to be applied are as follows:-

L1 = 1; L2 *2, L3*3...  having  69 in total (as aresult from input from form)= 414(i.e.69+138+207)   so, 100/414= 0.21545~

For example if we take the first line of the attached  image being ID 29 the weighting should be as follows:
ID = 29
Total_L1 = 17  *1 (applied weighting) = 17
Total_L2 = 27  *2 (applied weighting) = 54
Total_L2 = 25  *3 (applied weighting) = 75

Total       17+54+75= 146
Total BIM_KP1  = 146*0.21545 = 31.45%  two decimal places)  

The result is intended to be displayed using  a dashboard builder... see image - swing RAG meter..

something is wrong with this BIM_KP1: CountL([id],"L1"+[id],"L2"*2+[id]*3*0.4831)AS Percentage_KPI

The following SQL was used to give the query results (see attcahed image)
SELECT [BIM Software].ID, CountL([id],"L1") AS Total_L1, CountL([id],"L2") AS Total_L2, CountL([id],"L3") AS Total_L3
FROM [BIM Software]
GROUP BY [BIM Software].ID;

Function CountL(recID, L)
Dim rs As DAO.Recordset, db As DAO.Database, cntL As Integer
Dim j As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from [BIM Software] where iD=" & recID)
Do Until rs.EOF
    For j = 1 To rs.Fields.Count - 1
        If rs.Fields(j) = L Then
            cntL = cntL + 1
        End If
   
    Next
rs.MoveNext
Loop
rs.Close
CountL = cntL
End Function



SELECT [BIM Software].ID, CountL([id],"L1") AS Total_L1, CountL([id],"L2") AS Total_L2, CountL([id],"L3") AS Total_L3
FROM [BIM Software]
GROUP BY [BIM Software].ID;
 
Function CountL(recID, L)
Dim rs As DAO.Recordset, db As DAO.Database, cntL As Integer
Dim j As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from [BIM Software] where iD=" & recID)
Do Until rs.EOF
    For j = 1 To rs.Fields.Count - 1
        If rs.Fields(j) = L Then
            cntL = cntL + 1
        End If
    
    Next
rs.MoveNext
Loop
rs.Close
CountL = cntL
End Function

Open in new window

99.jpg
0
Jon_Wales_UK
Asked:
Jon_Wales_UK
  • 4
  • 3
1 Solution
 
miqrogrooveCommented:
Could you paste a sample of the underlying data?  And please be specific about which part of all this isn't working.. which part you need help with first.
0
 
Jon_Wales_UKAuthor Commented:
I'm new to Access... Firstly I would like I believe an SQL query / function solution
that can be applied to the original SQL but having 'weighting' appended to [BIMQuery1),
on a per row ID basis - a new field to be created called [BIM_KPI] at the end of [BIMQuery1]  

What I would like is for a user to select by combo boxan  [ID] or [ProjectTitle] ... the  [BIM_KPI] to be displayed in  text box, 0-100% on a dashboard Form, as the user selects the total tthe Red,Amber,Green graphic swing meter displays  - see attached image

The weighting that need to be applied are as follows:-
L1 = 1; L2 *2, L3*3...  having  69 in total (as aresult from input from form)= 414(i.e.69+138+207)   so, 100/414= 0.21545~

For example if we take the first line of the attached  image being ID 29 the weighting should be as follows:
ID = 29
Total_L1 = 17  *1 (applied weighting) = 17
Total_L2 = 27  *2 (applied weighting) = 54
Total_L2 = 25  *3 (applied weighting) = 75

Total       17+54+75= 146
Total BIM_KP1  = 146*0.21545 = 31.45%  two decimal places)  

The result is intended to be displayed using  a dashboard builder... see image - swing RAG meter..

something is wrong with this BIM_KP1: CountL([id],"L1"+[id],"L2"*2+[id]*3*0.4831)AS Percentage_KPI

Image-2.jpg
0
 
miqrogrooveCommented:
What I need to see is a screen shot of the [BIM Software] table data so that I can see how you have the L-values arranged into rows and columns.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
miqrogrooveCommented:
In the meantime, let me take a stab at this to see if we are thinking the same thing:

BIM_KP1: CountL([id],"L1"+[id],"L2"*2+[id]*3*0.4831)AS Percentage_KPI

SELECT ID, ((Total_L1 + Total_L2 * 2 + Total_L3 * 3) * 0.4831) AS Percentage_KPI
FROM (
  SELECT [BIM Software].ID, CountL([id],"L1") AS Total_L1, CountL([id],"L2") AS Total_L2, CountL([id],"L3") AS Total_L3
  FROM [BIM Software]
  GROUP BY [BIM Software].ID) AS Query1
0
 
Jon_Wales_UKAuthor Commented:
miqrogroove:  What I need to see is a screen shot of the [BIM Software] table data so that I can see how you have the L-values arranged into rows and columns.....

See attached screen shot...
112.jpg
0
 
miqrogrooveCommented:
Okay.. that's a pretty crazy table you've got there.  ;)  Have you tried the query I posted?
0
 
Jon_Wales_UKAuthor Commented:
Yes it works...although ...decimal point needs correcting...minor... many thanks... next part is to display the result in a combo on a form...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now