QUERY FIELD SUM

[gambler]

In have the following in my first field.
K:IIF([PL4HX4.RK]=1,"2",IIF([PL4HX4.RK]=2,"1",""))
I have the following in my second field.
H:IIF([PL4HX4.RH]=1,"2",IIF([PL4HX4.RH]=2,"1",""))
What would i write in my third field to get the sum of K + H ?
Thanks
ed

 

[ghetto_banjo]

should be as easy as:

L: [K] + [H]


However, you see that you are returning "1" which is a string, as
opposed 1 the number. You might want to change the values to 2, 1,
and 0 depending on what you are trying to accomplish.

 

[KARL DEWEY]

What would i write in my third field to get the sum of K + H ?
Z: IIF([PL4HX4.RK]=1, 2, IIF([PL4HX4.RK]=2, 1, 0)) + IIF([PL4HX4.RH]=1,
2, IIF([PL4HX4.RH]=2, 1, 0))

Using the quotes around the digits to show for condition results would give
you this -- 21 or 11 or 12 or 22 instead of 3 or 2 or 3 or 4.

If you use the quotes it is text output so that when you use the plus sign
with text it concatenates the information rather than summing.

 

[gambler]

KARL
Do you give any help over the phone? If so what do you charge?
Thanks
ed

Z: IIF([PL4HX4.RK]=1, 2, IIF([PL4HX4.RK]=2, 1, 0)) + IIF([PL4HX4.RH]=1,
2, IIF([PL4HX4.RH]=2, 1, 0))

Using the quotes around the digits to show for condition results would give
you this -- 21 or 11 or 12 or 22 instead of 3 or 2 or 3 or 4.

If you use the quotes it is text output so that when you use the plus sign
with text it concatenates the information rather than summing.

--
Build a little, test a little.

In have the following in my first field.
K:IIF([PL4HX4.RK]=1,"2",IIF([PL4HX4.RK]=2,"1",""))
I have the following in my second field.
H:IIF([PL4HX4.RH]=1,"2",IIF([PL4HX4.RH]=2,"1",""))
What would i write in my third field to get the sum of K + H ?
Thanks
ed

 

[KARL DEWEY]

No phone, no web sites, no viewing your web site.

If the Access problem seems interesting I respond.

--
Build a little, test a little.

KARL
Do you give any help over the phone? If so what do you charge?
Thanks
ed

What would i write in my third field to get the sum of K + H ?

Z: IIF([PL4HX4.RK]=1, 2, IIF([PL4HX4.RK]=2, 1, 0)) + IIF([PL4HX4.RH]=1,
2, IIF([PL4HX4.RH]=2, 1, 0))

Using the quotes around the digits to show for condition results would give
you this -- 21 or 11 or 12 or 22 instead of 3 or 2 or 3 or 4.

If you use the quotes it is text output so that when you use the plus sign
with text it concatenates the information rather than summing.

--
Build a little, test a little.

In have the following in my first field.
K:IIF([PL4HX4.RK]=1,"2",IIF([PL4HX4.RK]=2,"1",""))
I have the following in my second field.
H:IIF([PL4HX4.RH]=1,"2",IIF([PL4HX4.RH]=2,"1",""))
What would i write in my third field to get the sum of K + H ?
Thanks
ed

 

[gambler]

Thanks for responding.

No phone, no web sites, no viewing your web site.

If the Access problem seems interesting I respond.

--
Build a little, test a little.

KARL
Do you give any help over the phone? If so what do you charge?
Thanks
ed

What would i write in my third field to get the sum of K + H ?
Z: IIF([PL4HX4.RK]=1, 2, IIF([PL4HX4.RK]=2, 1, 0)) + IIF([PL4HX4.RH]=1,
2, IIF([PL4HX4.RH]=2, 1, 0))

Using the quotes around the digits to show for condition results would give
you this -- 21 or 11 or 12 or 22 instead of 3 or 2 or 3 or 4.

If you use the quotes it is text output so that when you use the plus sign
with text it concatenates the information rather than summing.

--
Build a little, test a little.


:

In have the following in my first field.
K:IIF([PL4HX4.RK]=1,"2",IIF([PL4HX4.RK]=2,"1",""))
I have the following in my second field.
H:IIF([PL4HX4.RH]=1,"2",IIF([PL4HX4.RH]=2,"1",""))
What would i write in my third field to get the sum of K + H ?
Thanks
ed

 

[gambler]

I did some research on google and i understand you equation. My problem now
is i will have maybe 30 IIf statements to add up. Since they will be going
from left to right across my sheet, how would i add them . I can write one
statement like you did above. I would really be long. How do you suggest i do
it,and i will do some more reading.
Im going from left to right because im doing horse racing and im look at
each horse in the race seperetyl.
Sure appreciate any help
Thanks Karl
ed

Z: IIF([PL4HX4.RK]=1, 2, IIF([PL4HX4.RK]=2, 1, 0)) + IIF([PL4HX4.RH]=1,
2, IIF([PL4HX4.RH]=2, 1, 0))

Using the quotes around the digits to show for condition results would give
you this -- 21 or 11 or 12 or 22 instead of 3 or 2 or 3 or 4.

If you use the quotes it is text output so that when you use the plus sign
with text it concatenates the information rather than summing.

--
Build a little, test a little.

In have the following in my first field.
K:IIF([PL4HX4.RK]=1,"2",IIF([PL4HX4.RK]=2,"1",""))
I have the following in my second field.
H:IIF([PL4HX4.RH]=1,"2",IIF([PL4HX4.RH]=2,"1",""))
What would i write in my third field to get the sum of K + H ?
Thanks
ed

 

[PieterLinden via AccessMonster.com]

I did some research on google and i understand you equation. My problem now
is i will have maybe 30 IIf statements to add up. Since they will be going
from left to right across my sheet, how would i add them . I can write one
statement like you did above. I would really be long. How do you suggest i do
it,and i will do some more reading.
Im going from left to right because im doing horse racing and im look at
each horse in the race seperetyl.
Sure appreciate any help
Thanks Karl
ed

Usually that's a sign that you need to normalize. Generally, tables should
be tall and slim, not short and fat. Short and fat is almost always an
indication of design problems. Then you can use totals queries. Much easier
and faster.

 

[gambler]

Usually that's a sign that you need to normalize. Generally, tables should
be tall and slim, not short and fat. Short and fat is almost always an
indication of design problems. Then you can use totals queries. Much easier
and faster.

--



.

 

[gambler]

Ill have to read some more.
Thanks
ed

Usually that's a sign that you need to normalize. Generally, tables should
be tall and slim, not short and fat. Short and fat is almost always an
indication of design problems. Then you can use totals queries. Much easier
and faster.

--



.

 

[John W. Vinson]

I did some research on google and i understand you equation. My problem now
is i will have maybe 30 IIf statements to add up. Since they will be going
from left to right across my sheet, how would i add them . I can write one
statement like you did above. I would really be long. How do you suggest i do
it,and i will do some more reading.

Here's some more places to read:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

Crystal's "Normalization 101" would be appropriate.

The proper structure for your race data would involve three tables:

Horses
HorseID <primary key>
HorseName
<owner ID, other information about the horse itself>

Races
RaceID <primary key>
Venue
RaceDate
RaceNumber <e.g. 3rd race of the day>
<other info about the race as an event>

Placement
RaceID <which race did the horse run in>
HorseID <which horse ran>
RaceTime <how fast did it run; I'd use a Double count of seconds, e.g.
3:15.25 would be stored as 195.25>
Place <1 for win, 2 for place, 3 for show, 11 for... oh well, better luck
next time>
<other info about this horse's run in this race, e.g. fouled, disqualified,
???>

If the 3rd race at Pimlico on Friday had 11 horses running, there'd be 11
records for that race in the Placement table, and a very simple Totals query
would let you sum <whatever it is you're summing>; NULL values would either
not be in the table at all or would be ignored by the sum.

 

[gambler]

WOW!!!!!!!!!!!!!!!
I never heard of all those place you gave me. Im going to have to do a lot
of reading. Im old but not dead, just slow.
I receive all my horse racing data from HDW. I have a program that lets me
export the data into Access. I have all the data i need in my tables. I can
generally write a query that will do what i want, but putting that query in a
report that will show me my query results is the problem.
I know your probably extremely busy, but is there anyway i could talk to you
and explain exactly what i want to? If so what would you charge?
If not im just very thankful for the help you already gave .
Thanks for helping me.
ed

 

[John W. Vinson]

WOW!!!!!!!!!!!!!!!
I never heard of all those place you gave me. Im going to have to do a lot
of reading. Im old but not dead, just slow.
I receive all my horse racing data from HDW. I have a program that lets me
export the data into Access. I have all the data i need in my tables. I can
generally write a query that will do what i want, but putting that query in a
report that will show me my query results is the problem.
I know your probably extremely busy, but is there anyway i could talk to you
and explain exactly what i want to? If so what would you charge?
If not im just very thankful for the help you already gave .
Thanks for helping me.

I'm not currently accepting new clients, I'm afraid. This could be a pretty
straightforward application, depending on just what your reports entail; you
might want to see if there's a nearby college with an Access course. Perhaps
you could take it, or hire a student (get the prof's recommendation!!) to put
it together.

If your data from HDW (whatever that is <g>) comes in the wide-flat, one field
per horse format, you'll need a query to migrate the data into the properly
normalized table. Tedious but not very difficult, post back with a description
of your current table if you need help.

 

[gambler]

I understand why you cant accept me as a client. There are only so many hours
in a day. As one gets older he tries to enjoy more of them. I just appreciate
what time you give. Ill do my best when i try and explain what im doing.
Please dont get to flustrated with me as im not that good with access. Most
of my success is trial and error. Many times i dont know what ive done, but
it works.
I have a 6 tables like you described above. I joint whatever tables im going
to use in a query by connecting lines between the following fields. [ track,
date, race , program] This allows the table data to be applied to each horse.
I then make up a report to read this data.
In the query im writing now, Im giving 20-30 data fields a 1 or 2. I hope to
apply the 1 or 2 to the data by using IIF statements. My data starts out with
trk, date, race, program #, horse name and the 20-30 fields of IFF
statements. These will go from left to right across my sheet. At the end of
my last field i hope to be able to sum all the 20-30 fields. This last field
i will put in my report. Have i made myself clear? If not ill try again.
Sure appreciate help
ed

 

[John W. Vinson]

I understand why you cant accept me as a client. There are only so many hours
in a day. As one gets older he tries to enjoy more of them.

I'm 64 myself so I know just what you mean... that's one reason I'm declining
the proposal!

I just appreciate
what time you give. Ill do my best when i try and explain what im doing.
Please dont get to flustrated with me as im not that good with access. Most
of my success is trial and error. Many times i dont know what ive done, but
it works.
I have a 6 tables like you described above. I joint whatever tables im going
to use in a query by connecting lines between the following fields. [ track,
date, race , program] This allows the table data to be applied to each horse.
I then make up a report to read this data.
In the query im writing now, Im giving 20-30 data fields a 1 or 2.

What is this table? What are these fields?

I hope to
apply the 1 or 2 to the data by using IIF statements. My data starts out with
trk, date, race, program #, horse name and the 20-30 fields of IFF
statements.

That's where I think you're going wrong. If you have 20 or 30 different
values, all of which pertain to a given (track, date, race, program, horse)
then you should have - I think, not knowing what the data is!!! - 20 or 30
RECORDS (not fields), in a table with fields for the identifying information
(track, date, race, program, horse), a field to identify the kind of value
(perhaps what you're now using as your fieldname), and the value. You could
then do a totals query *summing down the list* rather than across. That's how
relational tables work best.

These will go from left to right across my sheet. At the end of
my last field i hope to be able to sum all the 20-30 fields.

Don't confuse data PRESENTATION with data STORAGE. You may visualize the
values going across, or even present them that way (say with a crosstab
query), but based on my (incomplete!) understanding of what you're trying to
do, you should not store them that way.

This last field
i will put in my report. Have i made myself clear? If not ill try again.
Sure appreciate help
ed

If you could clarify what these fields mean it might help come up with a more
properly normalized solution.

 

[gambler]

Im in a handicapping contest tommorrow and have to use most of my time
preparing for it. If i can ill try to post. So dont think i gave up. Without
you im done. Im not familiar with using access vertically. The only way i
know how to use it is horizontally. What you said make a lot of sense, I just
dont know how to do it that way. If i use an access query vertically all i
would get is 20-30 OR STATEMENTS. Im going to try and come up with a better
description of what Im doing. When I have trouble with my program i usually
send the WEB MASTER a copy of what Im doing snd he tries to figure it out.
His strong suit isnt access, so he cant help. I know you dont want to give
out your email address or any phone number or even call me. I UNDERSTAND WAY.
Is there a way i could send my program to this location for you to see what i
have? I know once you see it or i can explain it more clearly you will have a
solution. ALSO MY POST IS BECOMING SEVERAL DAYS OLD. iF FOR SOME REASON ITS
GET TAKEN OFF THE BOARD FOR BEING OLD WHAT IS THE BEST WAY TO REPOST SO THAT
I WILL BE ABLE TO STAY IN TOUGH WITH YOU? I know you dont read every post.
I'll try harder in my next post to explain myself better. Please dont give up
on me Im doing my best to explain myself.
Once again thants for all the help
ed

I understand why you cant accept me as a client. There are only so many hours
in a day. As one gets older he tries to enjoy more of them.

I'm 64 myself so I know just what you mean... that's one reason I'm declining
the proposal!

I just appreciate
what time you give. Ill do my best when i try and explain what im doing.
Please dont get to flustrated with me as im not that good with access. Most
of my success is trial and error. Many times i dont know what ive done, but
it works.
I have a 6 tables like you described above. I joint whatever tables im going
to use in a query by connecting lines between the following fields. [ track,
date, race , program] This allows the table data to be applied to each horse.
I then make up a report to read this data.
In the query im writing now, Im giving 20-30 data fields a 1 or 2.

What is this table? What are these fields?

I hope to
apply the 1 or 2 to the data by using IIF statements. My data starts out with
trk, date, race, program #, horse name and the 20-30 fields of IFF
statements.

That's where I think you're going wrong. If you have 20 or 30 different
values, all of which pertain to a given (track, date, race, program, horse)
then you should have - I think, not knowing what the data is!!! - 20 or 30
RECORDS (not fields), in a table with fields for the identifying information
(track, date, race, program, horse), a field to identify the kind of value
(perhaps what you're now using as your fieldname), and the value. You could
then do a totals query *summing down the list* rather than across. That's how
relational tables work best.

These will go from left to right across my sheet. At the end of
my last field i hope to be able to sum all the 20-30 fields.

Don't confuse data PRESENTATION with data STORAGE. You may visualize the
values going across, or even present them that way (say with a crosstab
query), but based on my (incomplete!) understanding of what you're trying to
do, you should not store them that way.

This last field
i will put in my report. Have i made myself clear? If not ill try again.
Sure appreciate help
ed

If you could clarify what these fields mean it might help come up with a more
properly normalized solution.