Solved

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

Posted on 2008-10-28
7
316 Views
Last Modified: 2012-06-27
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
Comment
Question by:Jon_Wales_UK
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22824169
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
 

Author Comment

by:Jon_Wales_UK
ID: 22824853
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
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22824914
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 11

Accepted Solution

by:
miqrogroove earned 500 total points
ID: 22824974
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
 

Author Comment

by:Jon_Wales_UK
ID: 22826397
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
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22826554
Okay.. that's a pretty crazy table you've got there.  ;)  Have you tried the query I posted?
0
 

Author Comment

by:Jon_Wales_UK
ID: 22826987
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question