Solved

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

Posted on 2008-10-28
7
317 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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