Calculating a total of specific letters across columns

[rjscoates]

Hi,



I am two weeks into Access and tutorials and I am stuck on this issue. Looking for advice for best direction.

I have a table with letters, X, A, H, C and NS which represent attendance of a person. Each person has an entry in an attendance table along with their name and month field I am trying to add the totals of those letters across 31 columns, where each column represents a date (1st through 31st). I have enclosed a screenshot from a person's sub-form.

I appreciate ACCESS prefers data in columns rather than rows, but I started this and if I can make it work without restarting from the beginning again, it would be great

Thanks in advance
RJS

[ranman256]

this is the correct data layout:
personID, date, attendCode

which can then be queried to show like your table above, yet still add the values.
but, as you have it, you need to run 31 queries to total the codes. (1 for each column) then put the sums in a single 'report' table. (append qry)
then sum that sum table values.

[June7]

I am not sure what you mean by "add the totals of those letters across 31 columns". Do you want to add columns on each row or add rows under each column? Post a sample of desired output.

Really should normalize data structure. The output you show can easily be produced from normalized data, along with row sum or count you seem to be looking for as well.

[Micron]

I think it's a count of each value (not a total) across 31 fields. For March, count of X is 5
@rjscoates - you know that Access is row based, you've looked at tutorials and what? Did you decide to ignore what you learned or knew? If that's the case it's no wonder you're stuck.

[rjscoates]

I think it's a count of each value (not a total) across 31 fields. For March, count of X is 5
@rjscoates - you know that Access is row based, you've looked at tutorials and what? Did you decide to ignore what you learned or knew? If that's the case it's no wonder you're stuck.

Thank you for your kind and encouraging words. As I said, I’m two weeks in an I am sure learning the intricacies of Access will take more than 14 days and a couple of hours in each day.

I also understood that Access was column based and not row based, as totalling columns is a built in feature but calculating multiple rows is not.

RJS

[rjscoates]

this is the correct data layout:
personID, date, attendCode

which can then be queried to show like your table above, yet still add the values.
but, as you have it, you need to run 31 queries to total the codes. (1 for each column) then put the sums in a single 'report' table. (append qry)
then sum that sum table values.

Thank you for such a prompt response. So I understand correctly, I should have data organized as follows

PersonID DateDay DateMonth DateYear AttndCode

762 10 05 19 1
762 11 05 19 1
762 12 05 19 2
763 10 05 19 1

I then link the columns to a separate table to ensure accurate data is entered without typos, such that PersonID will always be 762 is Joe Bloggs, 763 is Robert Redford etc, DataDay 10 will always be 10th, DateMonth 05 will always be May, DateYear 19 will always be 2019 and AttndCode will return 1 as an 'X' and 2 as a 'A' for example.

Am I understanding correctly?

Thanks again

RJS

[isladogs]

Not quite correct. The dates should be in one field so it should be
PersonID AttendDate AttendMark

You can easily split date fields as needed into the three separate parts

To display your normalised data similar to that in post #1, you will need to use a crosstab query.
However the output will be read only.
If you do need to edit the data you must either use the data from the original table or create a temp table for that purpose

[Micron]

I also understood that Access was column based and not row based, as totalling columns is a built in feature but calculating multiple rows is not.
RJS

Hopefully you now know that is not the case. However, it's not clear if you realize that because of your writing style, which is why I asked that question. I think the sarcasm comes through quite clearly though.

[rjscoates]

Not quite correct. The dates should be in one field so it should be
PersonID AttendDate AttendMark

You can easily split date fields as needed into the three separate parts

To display your normalised data similar to that in post #1, you will need to use a crosstab query.
However the output will be read only.
If you do need to edit the data you must either use the data from the original table or create a temp table for that purpose

Thank you. I split the dates thinking that was the preferred option for data entry and sorting, but I will put them back together again.