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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|