How to sum alphanumeric characters

[jay]

I have 31 columns in a table and 1000 records with several x's in
each record.

How can I total the x's in each record?
I know I can use a query but I don't know how or where to put
the code that does it.

 

[John Vinson]

I have 31 columns in a table and 1000 records with several x's in
each record.

How can I total the x's in each record?
I know I can use a query but I don't know how or where to put
the code that does it.

Ugh. I suspect that the 31 columns are days of a month, and each field
has either a single X or a NULL? If so, the table structure is VERY
badly denormalized. If not, please accept my apology and perhaps
explain!

That said, you can count the number of fields containing an X either
by writing custom VBA code or using the rather ungainly expression:

CountOfX: Abs(([Field1]="x") + ([Field2]="x") + ([Field3]="x") + <etc.
etc. for 31 fields))

This works because a logical expression returns -1 for TRUE, 0 for
FALSE, so if ten fields are equal to X the summed expression will be
-10. The Abs() function converts this to +10.

John W. Vinson[MVP]

 

[jay]

I have 31 columns in a table and 1000 records with several x's in
each record.

How can I total the x's in each record?
I know I can use a query but I don't know how or where to put
the code that does it.

Ugh. I suspect that the 31 columns are days of a month, and each field
has either a single X or a NULL? If so, the table structure is VERY
badly denormalized. If not, please accept my apology and perhaps
explain!

That said, you can count the number of fields containing an X either
by writing custom VBA code or using the rather ungainly expression:

CountOfX: Abs(([Field1]="x") + ([Field2]="x") + ([Field3]="x") + <etc.
etc. for 31 fields))

This works because a logical expression returns -1 for TRUE, 0 for
FALSE, so if ten fields are equal to X the summed expression will be
-10. The Abs() function converts this to +10.

John W. Vinson[MVP]

You are right. I also print out a complicated form that uses
these numbers. I have that working. However I need to count
all of these x's to get a running total.

Until I can do it better.
Thank you and later.

 

[jay]

I have 31 columns in a table and 1000 records with several x's in
each record.

How can I total the x's in each record?
I know I can use a query but I don't know how or where to put
the code that does it.

Ugh. I suspect that the 31 columns are days of a month, and each field
has either a single X or a NULL? If so, the table structure is VERY
badly denormalized. If not, please accept my apology and perhaps
explain!

That said, you can count the number of fields containing an X either
by writing custom VBA code or using the rather ungainly expression:

CountOfX: Abs(([Field1]="x") + ([Field2]="x") + ([Field3]="x") + <etc.
etc. for 31 fields))

This works because a logical expression returns -1 for TRUE, 0 for
FALSE, so if ten fields are equal to X the summed expression will be
-10. The Abs() function converts this to +10.

John W. Vinson[MVP]

You are right. I also print out a complicated form that uses
these numbers. I have that working. However I need to count
all of these x's to get a running total.

Until I can do it better.
Thank you and later.

It is working great. Thank You.


Now I have a Field with X's down the Column.
How can I total down a Column. I tried with the Abs method above, but
I was unable to get it to work.

 

[Duane Hookom]

Did you change the table or the query or what? What do you mean by "unable
to get it to work"? Are the field types "Text" and actually contain the
value "X" or is the field Yes/No with checks?
--
Duane Hookom
MS Access MVP

I have 31 columns in a table and 1000 records with several x's in
each record.

How can I total the x's in each record?
I know I can use a query but I don't know how or where to put
the code that does it.

Ugh. I suspect that the 31 columns are days of a month, and each field
has either a single X or a NULL? If so, the table structure is VERY
badly denormalized. If not, please accept my apology and perhaps
explain!

That said, you can count the number of fields containing an X either
by writing custom VBA code or using the rather ungainly expression:

CountOfX: Abs(([Field1]="x") + ([Field2]="x") + ([Field3]="x") + <etc.
etc. for 31 fields))

This works because a logical expression returns -1 for TRUE, 0 for
FALSE, so if ten fields are equal to X the summed expression will be
-10. The Abs() function converts this to +10.

John W. Vinson[MVP]

You are right. I also print out a complicated form that uses
these numbers. I have that working. However I need to count
all of these x's to get a running total.

Until I can do it better.
Thank you and later.

It is working great. Thank You.


Now I have a Field with X's down the Column.
How can I total down a Column. I tried with the Abs method above, but
I was unable to get it to work.

 

[jay]

Did you change the table or the query or what? What do you mean by "unable
to get it to work"? Are the field types "Text" and actually contain the
value "X" or is the field Yes/No with checks?

No. The Above is working fine. I just have another total I need to do.


I have a field with X's in the field upto 100.
What expression can I use in a form to total all the X's in one field.
I know this is Demoralize but it is needed for a particular form.

D1 this is Day1

X
X
X

X

X
X
X
X

Now total them and display the total in a form which is 8 in this case.

 

[Duane Hookom]

In a control source, you can use:
=Sum(Abs(D1="x"))

I think you mean "unnormalized", not "demoralize". I guess it can me
demoralizing when you find out your tables aren't in the correct format.

 

[jay]

In a control source, you can use:
=Sum(Abs(D1="x"))

I think you mean "unnormalized", not "demoralize". I guess it can me
demoralizing when you find out your tables aren't in the correct format.

Everything is working.
Now this is the icing on the cake.

I have 31 fields that are days of the month in a Program table Query.
I have 100 records. I have totaled the X's in each record.
Now when a customer enters the training program I enter an entry date in
an EntryDate Field in a Table. When he leaves I enter an exit date in an
ExitDate field in the same Table called ProgramTable.



ProgramTable

CustomerName EntryDate ExitDate
Joe 4/5/05 4/25/05




ProgramTableQuery

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10...... D25 D26 D27 D28 D29 D30 D31
.. . . + X X X X X . ...... - . . . . . .




Now for the X totals to work I had to put a . for the saturdays and sundays
and the days he was not participating in the program. The days the customer
is not present.

I need to have a . for each day upto a + on the day before the EntryDate
lets say 4/5/05 and a negative or - on the ExitDate lets say on 4/25/05.

All of the plus and negatives are totaled and working at this time.

This example shows a customer or student present for 5 days this month.
I then have a merged document that prints out the days of the month for
each day with it's . dots " plus X's and - negative charactors.

It would be so nice to just enter the two dates for each student instead of
having to enter for each one all of these characters.
The Code would make this form automatic.

I am new to Access and am learning what I can. I know this code would be
a complex one and I am not sure if it is possible. I hope I will get it
working.

Any help would be appreciated.

 

[Duane Hookom]

To be totally honest, I wouldn't create any more solutions with the current
table structure. I would take the time to normalize the table and create
solution from the normalized tables.

 

[John Vinson]

It would be so nice to just enter the two dates for each student instead of
having to enter for each one all of these characters.
The Code would make this form automatic.

I am new to Access and am learning what I can. I know this code would be
a complex one and I am not sure if it is possible. I hope I will get it
working.

It is possible, but you're quite right - it IS complicated.

The reason that it is complicated is that

*** Your Table Structure Is Wrong ***.

"Safe upon the solid rock
The ugly houses stand -
Come and see my shining castle
Built upon the sand."

Your castle makes *initial data entry* a bit easier. But it makes
everything else MUCH MUCH HARDER!

Please read back over the thread, and consider storing your data in a
practical, ugly, pedestrian tall-thin normalized table with StudentID,
ClassID, StartDate and EndDate fields. It will be a bit more work at
first... but it won't come crashing down.

John W. Vinson[MVP]

 

[jay]

It is possible, but you're quite right - it IS complicated.

The reason that it is complicated is that

*** Your Table Structure Is Wrong ***.

"Safe upon the solid rock
The ugly houses stand -
Come and see my shining castle
Built upon the sand."

Your castle makes *initial data entry* a bit easier. But it makes
everything else MUCH MUCH HARDER!

Please read back over the thread, and consider storing your data in a
practical, ugly, pedestrian tall-thin normalized table with StudentID,
ClassID, StartDate and EndDate fields. It will be a bit more work at
first... but it won't come crashing down.

John W. Vinson[MVP]

So I am doomed.
You say I must make a table for each day of the Month? 31 tables? Just
to make it normal? Why go that rout?

 

[John Vinson]

So I am doomed.

Well, doomed to do some extra work.

You say I must make a table for each day of the Month? 31 tables? Just
to make it normal? Why go that rout?

No. I'm not saying anything of the sort!

ONE table.

Four fields.

CustomerID, ProgramID, StartDate, EndDate.

If the customer enrols in two programs, or the same program twice, add
two records to this table.

You can use a Crosstab query to regenerate your spreadsheet
appearance, if that's useful.

John W. Vinson[MVP]

 

[jay]

Well, doomed to do some extra work.


No. I'm not saying anything of the sort!

ONE table.

Four fields.

CustomerID, ProgramID, StartDate, EndDate.

If the customer enrols in two programs, or the same program twice, add
two records to this table.

You can use a Crosstab query to regenerate your spreadsheet
appearance, if that's useful.

John W. Vinson[MVP]

Ok. I have normalized my data from 4 tables to 11 tables.
Id with ProgramID and 1 column of information. Most tables if not all
are 3 columns.

This is the first time I did this and I am checking it out.
Is this right?

 

[jay]

Do you mind sharing your table structure? I'm not sure what your 11 tables
are.

SchoolEntryExitTable
Id Position EntryDate Exit Date
1 2.1 4/1/2005 4/25/2005
2 2.2 3/2/2005 3/21/2005

and so on


SchoolNameTable
1d Position Name
1 2.1 Jones
2 2.2 Frederick

and so on


SchoolAddressStreetTable
Id Street
1 123 3rd Street
2 321 4th Street

SchoolCityTable
Id City
1 San Diego

And so on with the basic information

Then


Then the big one.

SchoolDatesTable
Id Positon D1 D2 D3 ...... D31
1 2.1 X X X X
2 2.2 . . x x


Upto no more than 100 records

The Period means not in school on that
day.

I don't think this will line the columns
up straight. They was off on my previous post.


What I am looking for is the code that when an Entry Date
is entered lets say for D12 it will insert a "+" to the left of
D12 and then "." in D1 to D10. This would make it fill in automatically
just by entering the Entry Date.

Next when an Exit Date is entered a "-" is placed in that Day and
all of the Days after it have "." inserted.

So it would look like this:

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14
.. . + X X X X X X X - . . .

I have all my calculation working. I calculate the total adds "+",
total drops "-" and Total "X"s.

However, we have to enter all of this. If we had the code to
give us a simple entering of an entry date and exit date, we would
have it automatically done.

There are others with me who would like to have this code. It would
save a lot of time. I have it calculating perfect. You know how
many mistakes happen with people entering data like this.

I don't know how hard it would be to do. I hope it turns out to be easy.
I know I will get it but it will take a while for me to do this code.

 

[Duane Hookom]

Do you mind sharing your table structure? I'm not sure what your 11 tables
are.

 

[John Vinson]

I'm sorry, Jay, but we're talking past one another. You're normalizing
THE WRONG TABLE. I'm guessing your School table was perfectly fine;
what needed changing was the wide-flat SchoolDatesTable (or, more
properly, SchoolDatesSpreadsheet). I'm now concluding that your
SchoolDatesTable *SHOULD NOT EXIST* - see below.

SchoolEntryExitTable
Id Position EntryDate Exit Date
1 2.1 4/1/2005 4/25/2005
2 2.2 3/2/2005 3/21/2005

and so on


SchoolNameTable
1d Position Name
1 2.1 Jones
2 2.2 Frederick

Don't use the reserved word Name as a fieldname; call it SchoolName
perhaps (though I'm not sure in this context what this is the name
OF).

and so on


SchoolAddressStreetTable
Id Street
1 123 3rd Street
2 321 4th Street

This is just a field in the SchoolNameTable.

SchoolCityTable
Id City
1 San Diego

This is just a field in the SchoolNameTable.

And so on with the basic information

These are almost surely also just fields in the SchoolNameTable.

Then


Then the big one.

SchoolDatesTable
Id Positon D1 D2 D3 ...... D31
1 2.1 X X X X
2 2.2 . . x x

This contains the information in your EntryExit table, just recast,
correct? Do you empty the table and regenerate it every month? How do
you know whether Id 3, D4 is present on the 4th of April or the 4th of
March?

Upto no more than 100 records

up to as many records as are needed to record which ID's are present
on which Date.

The Period means not in school on that
day.

The absence of a record means not in school on that day.

I don't think this will line the columns
up straight. They was off on my previous post.


What I am looking for is the code that when an Entry Date
is entered lets say for D12 it will insert a "+" to the left of
D12 and then "." in D1 to D10. This would make it fill in automatically
just by entering the Entry Date.

This appears to be a *REPORT*, a tool for *displsying* data. If you
are assuming that you must have a Table set up in the exact format of
your desired report, I see why we're miscommunicating! A Table *is for
data storage*, not for reporting. You will instead want to create a
Crosstab query based on your EntryExitTable, and base a Report (or
Form) ON THAT QUERY.

Next when an Exit Date is entered a "-" is placed in that Day and
all of the Days after it have "." inserted.

So it would look like this:

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14
. . + X X X X X X X - . . .

I have all my calculation working. I calculate the total adds "+",
total drops "-" and Total "X"s.

This is a COMPLETELY SEPARATE ISSUE from the table with X'x, periods
and hyphens. You're counting *DAYS* - not X's - or at least you should
be. For example, a query:

SELECT ID, Position,
Sum(DateDiff("d", [Entry Date], [Exit Date])) AS DaysPresent,
Count([Entry Date]) AS TotalAdds,
Count([Exit Date]) AS TotalDrops
FROM SchoolEntryExitTable
GROUP BY ID, Position
WHERE <criteria to select the date range>

will give the number of days present, count of adds, and count of
drops for each ID.

However, we have to enter all of this. If we had the code to
give us a simple entering of an entry date and exit date, we would
have it automatically done.

There are others with me who would like to have this code. It would
save a lot of time. I have it calculating perfect. You know how
many mistakes happen with people entering data like this.

I don't know how hard it would be to do. I hope it turns out to be easy.
I know I will get it but it will take a while for me to do this code.

It's doable - but I really think you're *on the wrong track* trying to
do it in a wide-flat table like this. Doing so will make your job
*HARDER*, not simpler.

John W. Vinson[MVP]

 

[jay]

I'm sorry, Jay, but we're talking past one another. You're normalizing
THE WRONG TABLE. I'm guessing your School table was perfectly fine;
what needed changing was the wide-flat SchoolDatesTable (or, more
properly, SchoolDatesSpreadsheet). I'm now concluding that your
SchoolDatesTable *SHOULD NOT EXIST* - see below.

That is good news. I liked the School Table with its basic information of
each customer. It is simple and works very good.
How I am using the SchoolDatesTable is in a switchboard form where I can
see the Days from D1 to D31. I Then can enter the "."s, "X"s, "+", "-", and
I forgot to put these two in..... "A"s and no counting of Saturday and
Sunday and Holidays. No school on those days.

This is just a field in the SchoolNameTable.


This is just a field in the SchoolNameTable.


These are almost surely also just fields in the SchoolNameTable.

We have to count the Absent days too. The A days are not many. That is no
problem. We can just enter those. The Xs and . are many.

Now the reason for the ShoolDatesTable.
We have a complicated form that I had scanned, placed into a word document
and lined up a table on top of the image of the document. I merged the
SchoolDatesQuery made from the SchoolDatesTable to the Word Document. It
prints all information the X,A,.,+,-, and all totals. I have to do it this
way for my Boss who looks at these characters and verifies all totals. The
form must be done by hand or by computer.

This is just a field in the SchoolNameTable.


This is just a field in the SchoolNameTable.


These are almost surely also just fields in the SchoolNameTable.

We have to count the Absent days too. The A days are not many. That is no
problem. We can just enter those. The Xs and . are many.

Now the reason for the ShoolDatesTable.
We have a complicated form that I had scanned, placed into a word document
and lined up a table on top of the image of the document. I merged the
SchoolDatesQuery made from the SchoolDatesTable to the Word Document. It
prints all information the X,A,.,+,-, and all totals. I have to do it this
way for my Boss who looks at these characters and verifies all totals. The
form must be done by hand or by computer.

Don't use the reserved word Name as a fieldname; call it SchoolName
perhaps (though I'm not sure in this context what this is the name
OF).

Ok I will take note of that.

This contains the information in your EntryExit table, just recast,
correct? Do you empty the table and regenerate it every month? How do
you know whether Id 3, D4 is present on the 4th of April or the 4th of
March?

Yes. I will Append all records with exitdates to a drop table for storage.
Then run an update on the Tables and remove all dropped customers with exit
dates. The Tables grow large then reduced considerably at the end of the
month. Works smoothly.

up to as many records as are needed to record which ID's are present
on which Date.

No. All records stay and have A's in place of the X's for being not
in attendance on those days.

This appears to be a *REPORT*, a tool for *displsying* data. If you
are assuming that you must have a Table set up in the exact format of
your desired report, I see why we're miscommunicating! A Table *is for
data storage*, not for reporting. You will instead want to create a
Crosstab query based on your EntryExitTable, and base a Report (or
Form) ON THAT QUERY.

Yes. I am just using a query with two tables in it. SchoolMainTable with
all of the basic info and the ExitDatesTable with it's fields.
Everything works smoothly.

Next when an Exit Date is entered a "-" is placed in that Day and
all of the Days after it have "." inserted.

So it would look like this:

D1 D2 D3 D4 D5 D6 D7 D8 D9 D10 D11 D12 D13 D14
. . + X X X X X X X - . . .

I have all my calculation working. I calculate the total adds "+",
total drops "-" and Total "X"s.

This is a COMPLETELY SEPARATE ISSUE from the table with X'x, periods
and hyphens. You're counting *DAYS* - not X's - or at least you should
be. For example, a query:




SELECT ID, Position,
Sum(DateDiff("d", [Entry Date], [Exit Date])) AS DaysPresent,
Count([Entry Date]) AS TotalAdds,
Count([Exit Date]) AS TotalDrops
FROM SchoolEntryExitTable
GROUP BY ID, Position
WHERE <criteria to select the date range>

will give the number of days present, count of adds, and count of
drops for each ID.

I have the totals already.
I will book mark this and study it.

It's doable - but I really think you're *on the wrong track* trying to
do it in a wide-flat table like this. Doing so will make your job
*HARDER*, not simpler.

John W. Vinson[MVP]

Yes. I will Append all records with exitdates to a drop table for storage.
Then run an update on the Tables and remove all dropped customers with exit
dates. The Tables grow large then reduced considerably at the end of the
month. Works smoothly.

This appears to be a *REPORT*, a tool for *displsying* data. If you
are assuming that you must have a Table set up in the exact format of
your desired report, I see why we're miscommunicating! A Table *is for
data storage*, not for reporting. You will instead want to create a
Crosstab query based on your EntryExitTable, and base a Report (or
Form) ON THAT QUERY.

Yes. I am just using a query with two tables in it. SchoolMainTable with
all of the basic info and the ExitDatesTable with it's fields.
Everything works smoothly.












If we focus on 31 fields in the SchoolDatesTable with 2 other fields
EntryDate and ExitDate in the same table, and only 1 record, it may
just be easy to see.

A code is needed. Maybe an If Else statement.

If the EntryExit field has a date, then take the day in the middle of the
date like 04/25/05 and insert a plus to the left of D25 which is D24 and
followed by the "."s down to the last day "D1.

If the EntryDate Field does not have a date that is within the Current Month
then exit.

If the EntryDate Field is Null or empty then insert "." from Q1-D31

Then if the ExitDate Field has a date then take the middle two numbers which
is the day and insert a "-" in that D field followed by the "." up to the
last day which is "D31".


That would be cool. It would make this whole process automatic. No human
errors. Perfect. Not just mine but everyone. All 35 teachers.


One more point. The month has Sunday, Saturday and Holidays. There are no
X's in those days just insert the ".".
Months have 19 or 21 present days.

 

[jay]

A code is needed.  Maybe an If Else statement.

If the EntryExit field has a date, then take the day in the middle of the
date  like  04/25/05 and insert a plus to the left of D25 which is D24 and
followed by the "."s down to the last day "D1.

If the EntryDate Field does not have a date that is within the Current
Month then exit.

If the EntryDate Field is Null or empty then insert "." from Q1-D31

Then if the ExitDate Field has a date then take the middle two numbers
which is the day and insert a "-" in that D field followed by the "." up
to the last day which is "D31".


That would be cool. It would make this whole process automatic.  No human
errors.  Perfect.  Not just mine but everyone.  All 35 teachers.


One more point.  The month has Sunday, Saturday and Holidays.  There are
no X's in those days just insert the ".".
Months have 19 or 21 present days.

I had errors in that last post.
We can not use the + and - method. We can total the entry dates and the
exit dates. EntryDates are adds and ExitDates are Drops for the current
month and can be totaled. The + and - are not needed.


A code is needed.  Maybe an If Else statement.

If the EntryDate field has a date that is within the current month, then
take the day in the middle of the date  like  04/25/05 and insert "." to
the left of D25 down to the last day "D1.

Then insert "X" in the D25 up to the D31 field With no "x" in the weekends
and holidays fields for that month. That is insert "." in the weekends and
If the EntryDate Field has a date not with in the current month then
enter "X" in D1 to D3 with "." in the weekends and Holidays fields.
holiday fields.

If the EntryDate Field has a date not with in the current month then
enter "X" in D1 to D31 with "." in the weekends and Holidays fields.



If the EntryDate Field is Null or empty then insert "." from D1-D31


Then if the ExitDate Field has a date with in the current month then take
the middle two numbers which is the day and insert a "." in that Day field
followed by the "." up to the last day which is "D31".


That would be cool. It would make this whole process automatic.  No human
errors.  Perfect.  Not just mine but everyone.  All 35 teachers.


One more point.  The month has Sunday, Saturday and Holidays.  There are no
X's in those days just insert the ".".
Months have 19 or 21 or so present days.

 

[John Vinson]

How I am using the SchoolDatesTable is in a switchboard form where I can
see the Days from D1 to D31. I Then can enter the "."s, "X"s, "+", "-", and
I forgot to put these two in..... "A"s and no counting of Saturday and
Sunday and Holidays. No school on those days.

Your error is in assuming that you can or should edit data in a Table.

A Table is for data storage, and should be normalized.

A Table IS NOT A DATA ENTRY OR DATA PRESENTATION TOOL.

It sounds like you need an unbound Form with quite a bit of fairly
complicated VBA code to allow users to enter dots, x's, plusses and
minuses and have that data translated into table field values, or vice
versa. It's more code than I'd be comfortable to committing to write
for free as a newsgroup volunteer... but that's the tack I'd take.
Just get away from the idea that this should be a stored Table in your
database - because it quite certainly should NOT.

John W. Vinson[MVP]

 

[jay]

Your error is in assuming that you can or should edit data in a Table.

A Table is for data storage, and should be normalized.

A Table IS NOT A DATA ENTRY OR DATA PRESENTATION TOOL.

It sounds like you need an unbound Form with quite a bit of fairly
complicated VBA code to allow users to enter dots, x's, plusses and
minuses and have that data translated into table field values, or vice
versa. It's more code than I'd be comfortable to committing to write
for free as a newsgroup volunteer... but that's the tack I'd take.
Just get away from the idea that this should be a stored Table in your
database - because it quite certainly should NOT.

John W. Vinson[MVP]

Nope. We already are entering these characters manually without VBA Code.
I am asking for the VBA code that would enter the characters for us based
on what date was entered in the EntryDate and ExitDate Fields.
I know now that that would be too much code to ask for. I will have to
do it my self.

Can I ask for help from time to time for little bits of information like
other people on this list or am I denied any further help? Am I kicked to
the curb? Am I booted out from the Access Society for deviating from the
norm?