calculations in MS Access

  reddwarfcrew 19:45 13 Jul 2006
Locked

Hi,

I have an access database with a table of data (DATA).

I then have another table with a list of names (NAMES).

I then have a query run off the DATA which counts up the number of times each name appears.

What I would like is to able to perform a calculation with the Query results and NAMES table.

The names table holds 5 names (A, B, C, D, E)

The Query then returns the number of times those names appear in the DATA table. What I would like is not the number of times that the name appears but 7 minus the number of times it appears. So if A appears 5 times I want the result to show as 2.

Is this possible?

Thanks

  Main Access 23:58 13 Jul 2006

SELECT 7 - COUNT(Names) as MyResult FROM DATA

GROUP BY Names

I would also rename the tables and fields, Names and Data are very close to reserved words and could get a bit confusing

  reddwarfcrew 19:48 14 Jul 2006

i have achieved what I was after for that query, but now have another problem:

My table of data is called QA
I have 2 columns, 1 called CSRName and 1 called Correct

What I would like is a query the % of 'Yes' that appear in the 'Correct' column per name.

I can do a query to tell me how many 'yes' and also a query for the 'No', but how do I do a calculation in the query. My query for 'Yes' is:

SELECT QAs.CSRName, Count(QAs.[Correct?]) AS Number_Correct
FROM QAs
WHERE (((QAs.[Correct?])="Yes") AND ((Month([Date case worked by CSR]))=7))
GROUP BY QAs.CSRName;


Many thanks

  Main Access 22:15 15 Jul 2006

bit more complexed

Write a union query as follows

SELECT QAs.CSRName, Count(QAs.[Correct?]) AS Number_Correct, 0 as total
FROM QAs
WHERE (((QAs.[Correct?])="Yes") AND ((Month([Date case worked by CSR]))=7))
GROUP BY QAs.CSRName;

UNION SELECT QAs.CSRName, 0 AS Number_Correct,
Count(QAs.[Correct?]) as total
FROM QAs
WHERE Month([Date case worked by CSR])=7
GROUP BY QAs.CSRName;

save the above query

now write another query based on union which groups by CSRName Again.

from this new query you can write another one that will give you your %

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone