A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

QUERY FIELD SUM



 
 
Thread Tools Display Modes
  #11  
Old May 5th, 2010, 05:05 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default QUERY FIELD SUM

On Tue, 4 May 2010 17:27:01 -0700, gambler
wrote:

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/acc...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.
--

John W. Vinson [MVP]
  #12  
Old May 5th, 2010, 04:57 PM posted to microsoft.public.access.queries
gambler
external usenet poster
 
Posts: 13
Default QUERY FIELD SUM

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" wrote:

On Tue, 4 May 2010 17:27:01 -0700, gambler
wrote:

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/acc...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.
--

John W. Vinson [MVP]
.

  #13  
Old May 5th, 2010, 05:55 PM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default QUERY FIELD SUM

On Wed, 5 May 2010 08:57:01 -0700, gambler
wrote:

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.
--

John W. Vinson [MVP]
  #14  
Old May 5th, 2010, 07:17 PM posted to microsoft.public.access.queries
gambler
external usenet poster
 
Posts: 13
Default QUERY FIELD SUM

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" wrote:

On Wed, 5 May 2010 08:57:01 -0700, gambler
wrote:

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.
--

John W. Vinson [MVP]
.

  #15  
Old May 6th, 2010, 03:05 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default QUERY FIELD SUM

On Wed, 5 May 2010 11:17:01 -0700, gambler
wrote:

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.
--

John W. Vinson [MVP]
  #16  
Old May 6th, 2010, 08:00 PM posted to microsoft.public.access.queries
gambler
external usenet poster
 
Posts: 13
Default QUERY FIELD SUM

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

"John W. Vinson" wrote:

On Wed, 5 May 2010 11:17:01 -0700, gambler
wrote:

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.
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.