Prev: Left Join query not working in Access 2010
Next: cdo or objmessage??
From: John W. Vinson on 5 May 2010 00:05
On Tue, 4 May 2010 17:27:01 -0700, gambler <gambler(a)discussions.microsoft.com>
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/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.
--

John W. Vinson [MVP]
From: gambler on 5 May 2010 11:57
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 <gambler(a)discussions.microsoft.com>
> 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/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.
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 5 May 2010 12:55
On Wed, 5 May 2010 08:57:01 -0700, gambler <gambler(a)discussions.microsoft.com>
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]
From: gambler on 5 May 2010 14:17
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 <gambler(a)discussions.microsoft.com>
> 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]
> .
>
From: John W. Vinson on 5 May 2010 22:05
On Wed, 5 May 2010 11:17:01 -0700, gambler <gambler(a)discussions.microsoft.com>
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]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Left Join query not working in Access 2010
Next: cdo or objmessage??