Getting a SUM from 3 different Text boxes

[Scuda]

Hi all, I have 3 different but similar text boxes. They are txtcontroller,
txtcontroller2, and txtcontroller3

Basically, in my table I need to track different people involved with a
certain task, this is why I have the 3 of them

I have a report called rptTotalByController which gives me a sum of tasks
based on the "txtcontroller" box only. How can I get the total to look for a
name in not only txtcontroller, but the txtcontroller2 and txtcontroller3
boxes?

So, basically anytime someones name shows up in either txtcontroller1, 2 or
3, it will be added to their total.

Hope this makes sense,thanks in advance.

Steph

 

[Jeff Boyce]

If your underlying table also has three possible fields in which a
controller's name can show up, your table could stand a bit more
normalization. If your "tasks" can have one, two, or three controllers (?or
none, to start with?), you have a one-to-many relationship. In a relational
database like Access, you handle this by using another table, not by adding
another column.

"Why?", you ask? What will you do if the number of controllers is changed
(now it could be up to 4; now it will never be more than two; ...)? With
the table design you've described, you'll have to go through all your
related queries, forms, reports, macros, code and table and make the
necessary changes.

Much simpler (and easier for Access to use) to add a table that holds, at
minimum, two fields:

trelTaskController
TaskID
ControllerID

These two ID fields serve as foreign keys, pointing back to the records in
your tblTask and tblController that "own" the valid combination. Notice
that this design only needs one row if there's only one Controller for a
particular Task. Notice also that you could have 10 rows, designating the
10 Controllers for a particular Task. No database/application redesign
required -- just add as many rows as you need!

Also note that this design uses the ControllerID, not the Controller's Name.
Use a table (?tblController) to keep name info, and just put the
ControllerID value in the TaskController table. And by the way, if you have
two John Smiths as Controllers, which one do you mean if you ONLY put their
name (not their ID)?!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

One more thing...

You described up to three Controllers, indicated by their names. How does a
"SUM" come into play here?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Scuda]

Thanks so much Jeff, I will take your advice!

I assume then it will be easier to get the total that I desire probably right?

Thanks again.
Steph

 

[Jeff Boyce]

I don't believe Access understands, and is giving you the prompt to say,
"tell me a value". Using the actual field helps Access see what it needs to
use.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[hongluen via AccessMonster.com]

I think we are facing the same problem.
I posted a message at
http://www.accessmonster.com/Uwe/Fo...es/38758/Merging-text-data-in-cross-tab-query


It is logical that we cannot SUM values of text. I wonder if we could merge
text or "append" them one by one, such that we can accomplish what we want,
without making signigicant change in database structure.

Need advice.

 

[Marshall Barton]

Hi all, I have 3 different but similar text boxes. They are txtcontroller,
txtcontroller2, and txtcontroller3

Basically, in my table I need to track different people involved with a
certain task, this is why I have the 3 of them

I have a report called rptTotalByController which gives me a sum of tasks
based on the "txtcontroller" box only. How can I get the total to look for a
name in not only txtcontroller, but the txtcontroller2 and txtcontroller3
boxes?

So, basically anytime someones name shows up in either txtcontroller1, 2 or
3, it will be added to their total.

Hope this makes sense,thanks in advance.

Well, it makes sense, but only because you're not the first
to make the mistake of not properly normalizing you tables.
You should not have repeating fields in a table (what will
you do when a task needs four or more controlers? Instead
you should have a separate table for task controlers (one
controller per record) that includes a foreign key to the
task table. You might(?) be able to get by your current
question, but there are many more just waiting.

I really don't know how you would calculate what you are
asking because I can't see how you put together a record
source query that allows the report to group on controller
values in three different fields.

 

[Scuda]

First off, thanks to everyone for replying. I appreciate the feedback as I am
always learning.

I am at a Search and Rescue unit, and we are CONTROLLERS. I always had a
text field for CONTROLLER, but when we swtched from 24 to 12 hour watches,
there are many more cases that carry over to another controller. So I added
3 text boxes to my table, then my form,
CONTROLLER2
CONTROLLER3
CONTROLLER4

When I do a query for a specific controller I was only getting numbers for
cases in which they were the first controller (name in the original
CONTROLLER text field) I would like to get a count of all cases they were
involved in, whether thier name appears in ANY of the CONTROLLER text fields.

I realize now (from all of your advice) that I went about it the wrong way.

Steph

 

[Marshall Barton]

Are you asking a new question now, or are you just
rephrasing the original question?

If this is a new question, please explain the current table
structure,

If you haven't changed the table structure, then the answer
is still the same. The table structure needs to be
normalized. You need at least three tables for this data:

tblTasks:
TaskID AutoNumber Primary Key
TaskDecsr Text
. . . (task specific fields)

tblControllers:
CtrlrID AutoNumber Primary Key
CtrlrName Text
CtrlrPhone Text
. . . (controller specific fields)

tblTaskContoller:
TaskID Long
CtrlrID Long
Shift Text(?)
. . . (fields for a specific controller
on a specific task)