Left Join - Check for value and count. (1 Viewer)

[aaronkm]

Hello,

I'm using Access 03 and have a query that checks for the existence of a value from another table. This works well but what I'm not able to figure out is how to then get a count of the times that entry is found in the table.

In other words...

I have a table called 'jcaScore'
in this table are unique text values in a column named 'Tier3'

My query needs to check the 'analyze' table, 'Tier3' column and COUNT the number of times each of those unique Tier3 values from the jcaScore table are present and place that count in the 'jcaScore'.gapscore field.

I've almost got it but I can't figure out the last argument.

UPDATE scoreJCA LEFT JOIN analyze ON scoreJCA.[Tier3] = analyze.Tier3 SET scoreJCA.gapcount = ???

Much adulation for any assist!

TGIF too!

All the best,
Aaron

 

[neileg]

Why do you want to store a value that you can calculate? If you do an aggregate query set to COUNT your tier3, you can use that. Or use DCount() instead of a query.

 

[aaronkm]

Yes i should not be storing calculated fields and I really do apologize, I'm just trying to reach an end result using any means I can, the steps to the objective are quite complex. So until I can get solid understanding of the entire process I'm trying to break it down into the smallest pieces I can. Once that's complete then I'll try and make it all one statement.

In short because I'm not a guru and trying to digest the request all at once just makes my head swim...

This thread/question is just a small piece of the entire function I'm trying to develop.

The entire function goes something like this...

I have a list of data that contains a tier3 selection, these selections are compared against another table and if they are present then a score is placed in one column of my scores table. I then need to count how many times each of those entries were present, this score goes into a different score column. (that's what I'm trying to get answered here).
Then I'll use another query to count a separate attribute associated with these source rows and place that value into the scores table in yet another scores column.

Do that two more times and I'll be left with a table of scores that will have five columns of scores. These scores are then summed and squared and then divided into one another, the end result being a value in which I can plot on a 2d chart between 1,1 (optimal solution) and 0,0 (least optimal solution) The value that is closest to the optimal solution AND farthest from the least optimal solution would be the best choice.

In a nutshell, this is called the TOPSIS analytical method and that's what I'm trying to achieve one little baby step at a time.

Any suggestions on getting the rows counted after the join?