calculations in MS Access

  reddwarfcrew 19:45 13 Jul 06


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?


  Main Access 23:58 13 Jul 06

SELECT 7 - COUNT(Names) as MyResult FROM DATA


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 06

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
WHERE (((QAs.[Correct?])="Yes") AND ((Month([Date case worked by CSR]))=7))

Many thanks

  Main Access 22:15 15 Jul 06

bit more complexed

Write a union query as follows

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

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

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.

LG G6 review: Hands-on with LG’s bold, big-screen shot at perfection

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…