Solved

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

Posted on 2008-10-28
7
314 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now