Solved

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

Posted on 2008-10-28
7
315 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

861 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

26 Experts available now in Live!

Get 1:1 Help Now