Parameterized query question

[Pejo]

I'm going to create a simple report that will give me the percentages of
each mark to occur
in each field.

The Table looks something like.

tbl_Tests

Test_1 Test_2 Test_3

a b a
c b b
a b c
a c a
b c b



I've created the following sql to give me the averages for one column at a
time.

SELECT Test_# AS Mark, (Count(Test_#)/(SELECT count(Test_#) FROM
tbl_TESTS )*100) AS Percentage
FROM tbl_TESTS
GROUP BY Test_#;

I want to be able to replace the Test_# with a paramater in this case.

If I run the query without parameters IE replace the Test_# in all
occurences with Test_1 It works fine...but as soon as I try to add the
parameters it screws up bad.


any thoughts??

 

[Guest]

Post your SQL with parameters.

 

[pottymouthed]

SELECT [@Test_#] AS Mark, (Count( [@Test_#]) / (SELECT count(
[@Test_#]) FROM tbl_tests )*100) AS Percentage
FROM tbl_tests
GROUP BY [@Test_#];

 

[pottymouthed]

If I were using an ASP page I would pass a variable but this must all
be done in access.
I've tried naming each of them something different but then passing the
same test with no luck.

In the end I would like the results to show if I say send Test 1


Mark Percentage

A 30%
B 20%
C 35%
D 15%

 

[John Spencer]

Your problem is that your table is structured incorrectly. You really need
to have
TblGrades
TestNumber
Grade

Then you query would look like the UNTESTED one below.
SELECT TestNumber , Grade
, (Count(Grade)/(SELECT count(Grade)
FROM tblGrades as G
WHERE G.TestNumber = tblGrades.TestNumber)*100) AS Percentage
FROM tblGrades
GROUP BY TestNumber, Grade

The only way you could do what you want with your current structure would be
to build the query on the fly with VBA or some other programming language.

 

[pottymouthed]

Yeah I kind of figured that.... I'm kind of limited in this
situation...might have to create a temp table with the data
reorganized.
At least with T-sql I'd have some options vis a vis cursors or loops.

Still confused as to why it treats the parameters sent to it as such..
No big deal..Probably just use vba.

 

[John Spencer]

You could use a saved UNION ALL query to normalize the data instead of a
temp table and then use that query as the source for your query. The
problem being that you could end up with something that is slow if you have
a lot of records.

SELECT "Test_1" as TestNumber, Test_1 as Grade
FROM Tbl_Tests
UNION ALL
SELECT "Test_2" as TestNumber, Test_2 as Grade
FROM Tbl_Tests
UNION ALL
SELECT ...