I have a table with names and adresses on and another table wich is linked over 'link' which is a number. the second table has a series of fields which are dates. (Name1, Name2, Address1, address2, Town, Postcode, link. and link, 01012010, 02012010, 03012010... etc) one fieled for every date over the last 6 months the value of the field can be a letter from A to F. I need to count the number of times each letter occrs for each person. In excel I would list the names down the side and the dates along the top and use the first six columns e.g. =COUNTIF(K2:HN2, "C"). I need to produce a query that returns the same. can anyone help please.

Hi Graham

First off let me just say I am not an access expert, this was posted in the Excel forum so you may have better luck if you ask a mod to move this to a more relevant forum.


First you need to restructure your data to be properly normalised. Your second table would be better with a format like so:

LinkID, DateYear, DateMonth, DateDay, CodeLetter

How you get there, you have 2 options.

1. Write some code to loop through your existing table and create the normalised data
2. Write a 'Union' query to do the same

Once you have your data in the correct format then it would be very simple piece of SQL to get the results you want

OR - And this is not recommended. You could concatenate all of your 180 (Might need to break it into months) date fields into one long string. Then to calculate the number of "A's" it is the length of this string minus the length of the string with all the "A's" replaced with nothing.

Here is an example...


HTH
Stu