sum fields and then average them

[Guest]

In palin English, I have a table based on club members and several other
fields of numeric data. Some of the fields are numbers, some currency and
some text. This data deals with members betting on horse races.

My question is how to display a summary of each members bets based on horse
names, as opposed to the entire recordset showing all transactions? It is
possible that a member can have more than one bet on the same horse on the
same day.

Thanks.

 

[Brendan Reynolds]

This would give the total and average bet per member per horse. Change the
table and field names to your actual names, obviously ...

SELECT tblTest.MemberName, tblTest.HorseName, Sum(tblTest.ValueOfBet) AS
SumOfValueOfBet, Avg(tblTest.ValueOfBet) AS AvgOfValueOfBet
FROM tblTest
GROUP BY tblTest.MemberName, tblTest.HorseName;

 

[Guest]

Hi Brendan,

I named the tables and fields correctly to:

(SELECT tbl accountMon.account, tbl accountMon.ection, Sum(tbl
accountMon.ValueOfstake) AS
SumOfValueOfstake, Avg(tbl accountMon.ValueOfstake) AS AvgOfValueOfstake
FROM tbl accountMon
GROUP BY tbl accountMon.account, tbl accountMon.ection

But keep getting a syntax error, I tried numerous bracket groupings but just
can't get it to work. For your reference the following might help:

tblTest = tbl accountMon
MemberName = account
HorseName = ection
Bet = stake

Thanks for your time and effort, its very good of you to care.
Regards
Tailwag

 

[Brendan Reynolds]

Is that a space in the table name (between 'tbl' and 'accountMon')? If so,
you'll need square brackets around the table name. You also need to lose the
outer-most parentheses (the one before 'SELECT' and the one after the ';').
Try this ...

SELECT [tbl accountMon].account, [tbl accountMon].ection, Sum([tbl
accountMon].ValueOfstake) AS
SumOfValueOfstake, Avg([tbl accountMon].ValueOfstake) AS AvgOfValueOfstake
FROM [tbl accountMon]
GROUP BY [tbl accountMon].account, [tbl accountMon].ection;

 

[Brendan Reynolds]

Oops - almost missed one - you also need to change 'ValueOfStake' to just
'Stake'.

--
Brendan Reynolds (MVP)

Is that a space in the table name (between 'tbl' and 'accountMon')? If so,
you'll need square brackets around the table name. You also need to lose
the outer-most parentheses (the one before 'SELECT' and the one after the
';'). Try this ...

SELECT [tbl accountMon].account, [tbl accountMon].ection, Sum([tbl
accountMon].ValueOfstake) AS
SumOfValueOfstake, Avg([tbl accountMon].ValueOfstake) AS AvgOfValueOfstake
FROM [tbl accountMon]
GROUP BY [tbl accountMon].account, [tbl accountMon].ection;

--
Brendan Reynolds (MVP)

Hi Brendan,

I named the tables and fields correctly to:

(SELECT tbl accountMon.account, tbl accountMon.ection, Sum(tbl
accountMon.ValueOfstake) AS
SumOfValueOfstake, Avg(tbl accountMon.ValueOfstake) AS AvgOfValueOfstake
FROM tbl accountMon
GROUP BY tbl accountMon.account, tbl accountMon.ection

But keep getting a syntax error, I tried numerous bracket groupings but
just
can't get it to work. For your reference the following might help:

tblTest = tbl accountMon
MemberName = account
HorseName = ection
Bet = stake

Thanks for your time and effort, its very good of you to care.
Regards
Tailwag

 

[Guest]

Hello again Brendan,

I tried what you said to no avail and I think the problem is me. I have been
doing the online training tutorials the last couple of days and have come to
the conclusion that my tables are poorly designed and not very efficent. So I
am going to rebuild the entire thing from scratch and then try your
suggestions to extract the data I require. The names will be different but
the actual data will be the same, so in a couple of days time it would be
good if I could call on you for your help if I still can't get it working by
then. My e-mail address is (e-mail address removed) if you want to e-mail me
outside this forum for more direct communication.

Best wishes.
Tailwag

Oops - almost missed one - you also need to change 'ValueOfStake' to just
'Stake'.

--
Brendan Reynolds (MVP)

Is that a space in the table name (between 'tbl' and 'accountMon')? If so,
you'll need square brackets around the table name. You also need to lose
the outer-most parentheses (the one before 'SELECT' and the one after the
';'). Try this ...

SELECT [tbl accountMon].account, [tbl accountMon].ection, Sum([tbl
accountMon].ValueOfstake) AS
SumOfValueOfstake, Avg([tbl accountMon].ValueOfstake) AS AvgOfValueOfstake
FROM [tbl accountMon]
GROUP BY [tbl accountMon].account, [tbl accountMon].ection;

--
Brendan Reynolds (MVP)

Hi Brendan,

I named the tables and fields correctly to:

(SELECT tbl accountMon.account, tbl accountMon.ection, Sum(tbl
accountMon.ValueOfstake) AS
SumOfValueOfstake, Avg(tbl accountMon.ValueOfstake) AS AvgOfValueOfstake
FROM tbl accountMon
GROUP BY tbl accountMon.account, tbl accountMon.ection

But keep getting a syntax error, I tried numerous bracket groupings but
just
can't get it to work. For your reference the following might help:

tblTest = tbl accountMon
MemberName = account
HorseName = ection
Bet = stake

Thanks for your time and effort, its very good of you to care.
Regards
Tailwag

:

This would give the total and average bet per member per horse. Change
the
table and field names to your actual names, obviously ...

SELECT tblTest.MemberName, tblTest.HorseName, Sum(tblTest.ValueOfBet) AS
SumOfValueOfBet, Avg(tblTest.ValueOfBet) AS AvgOfValueOfBet
FROM tblTest
GROUP BY tblTest.MemberName, tblTest.HorseName;

--
Brendan Reynolds (MVP)

In palin English, I have a table based on club members and several
other
fields of numeric data. Some of the fields are numbers, some currency
and
some text. This data deals with members betting on horse races.

My question is how to display a summary of each members bets based on
horse
names, as opposed to the entire recordset showing all transactions? It
is
possible that a member can have more than one bet on the same horse on
the
same day.

Thanks.

 

[Brendan Reynolds]

When you're ready, just post the question here - if I don't see it, there
are lots of other people here who are able and willing to help you.

--
Brendan Reynolds (MVP)

Hello again Brendan,

I tried what you said to no avail and I think the problem is me. I have
been
doing the online training tutorials the last couple of days and have come
to
the conclusion that my tables are poorly designed and not very efficent.
So I
am going to rebuild the entire thing from scratch and then try your
suggestions to extract the data I require. The names will be different but
the actual data will be the same, so in a couple of days time it would be
good if I could call on you for your help if I still can't get it working
by
then. My e-mail address is (e-mail address removed) if you want to e-mail me
outside this forum for more direct communication.

Best wishes.
Tailwag

Oops - almost missed one - you also need to change 'ValueOfStake' to
just
'Stake'.

--
Brendan Reynolds (MVP)

Is that a space in the table name (between 'tbl' and 'accountMon')? If
so,
you'll need square brackets around the table name. You also need to
lose
the outer-most parentheses (the one before 'SELECT' and the one after
the
';'). Try this ...

SELECT [tbl accountMon].account, [tbl accountMon].ection, Sum([tbl
accountMon].ValueOfstake) AS
SumOfValueOfstake, Avg([tbl accountMon].ValueOfstake) AS
AvgOfValueOfstake
FROM [tbl accountMon]
GROUP BY [tbl accountMon].account, [tbl accountMon].ection;

--
Brendan Reynolds (MVP)

Hi Brendan,

I named the tables and fields correctly to:

(SELECT tbl accountMon.account, tbl accountMon.ection, Sum(tbl
accountMon.ValueOfstake) AS
SumOfValueOfstake, Avg(tbl accountMon.ValueOfstake) AS
AvgOfValueOfstake
FROM tbl accountMon
GROUP BY tbl accountMon.account, tbl accountMon.ection

But keep getting a syntax error, I tried numerous bracket groupings
but
just
can't get it to work. For your reference the following might help:

tblTest = tbl accountMon
MemberName = account
HorseName = ection
Bet = stake

Thanks for your time and effort, its very good of you to care.
Regards
Tailwag

:

This would give the total and average bet per member per horse.
Change
the
table and field names to your actual names, obviously ...

SELECT tblTest.MemberName, tblTest.HorseName, Sum(tblTest.ValueOfBet)
AS
SumOfValueOfBet, Avg(tblTest.ValueOfBet) AS AvgOfValueOfBet
FROM tblTest
GROUP BY tblTest.MemberName, tblTest.HorseName;

--
Brendan Reynolds (MVP)

In palin English, I have a table based on club members and several
other
fields of numeric data. Some of the fields are numbers, some
currency
and
some text. This data deals with members betting on horse races.

My question is how to display a summary of each members bets based
on
horse
names, as opposed to the entire recordset showing all transactions?
It
is
possible that a member can have more than one bet on the same horse
on
the
same day.

Thanks.

 

[Guest]

Thanks Brendan,

I appreciate your time and effort. I am just not ready yet for advanced
queries until I better understand the more simpler ones. I should learn to
walk before I run. I did find an interesting web page that has lots of tips
and tricks, most are too advanced for me at the moment but I'll get to them
eventually. It's at: http://www.aadconsulting.com/aadtips.html perhaps
something there might be good for you. Would it be possible to have your
e-mail address to write to you off list sometimes if I have a curly concept
that I can't figure out? I promise I won't over do the friendship.

Regards
Tailwag

When you're ready, just post the question here - if I don't see it, there
are lots of other people here who are able and willing to help you.

--
Brendan Reynolds (MVP)

Hello again Brendan,

I tried what you said to no avail and I think the problem is me. I have
been
doing the online training tutorials the last couple of days and have come
to
the conclusion that my tables are poorly designed and not very efficent.
So I
am going to rebuild the entire thing from scratch and then try your
suggestions to extract the data I require. The names will be different but
the actual data will be the same, so in a couple of days time it would be
good if I could call on you for your help if I still can't get it working
by
then. My e-mail address is (e-mail address removed) if you want to e-mail me
outside this forum for more direct communication.

Best wishes.
Tailwag

Oops - almost missed one - you also need to change 'ValueOfStake' to
just
'Stake'.

--
Brendan Reynolds (MVP)

Is that a space in the table name (between 'tbl' and 'accountMon')? If
so,
you'll need square brackets around the table name. You also need to
lose
the outer-most parentheses (the one before 'SELECT' and the one after
the
';'). Try this ...

SELECT [tbl accountMon].account, [tbl accountMon].ection, Sum([tbl
accountMon].ValueOfstake) AS
SumOfValueOfstake, Avg([tbl accountMon].ValueOfstake) AS
AvgOfValueOfstake
FROM [tbl accountMon]
GROUP BY [tbl accountMon].account, [tbl accountMon].ection;

--
Brendan Reynolds (MVP)

Hi Brendan,

I named the tables and fields correctly to:

(SELECT tbl accountMon.account, tbl accountMon.ection, Sum(tbl
accountMon.ValueOfstake) AS
SumOfValueOfstake, Avg(tbl accountMon.ValueOfstake) AS
AvgOfValueOfstake
FROM tbl accountMon
GROUP BY tbl accountMon.account, tbl accountMon.ection

But keep getting a syntax error, I tried numerous bracket groupings
but
just
can't get it to work. For your reference the following might help:

tblTest = tbl accountMon
MemberName = account
HorseName = ection
Bet = stake

Thanks for your time and effort, its very good of you to care.
Regards
Tailwag

:

This would give the total and average bet per member per horse.
Change
the
table and field names to your actual names, obviously ...

SELECT tblTest.MemberName, tblTest.HorseName, Sum(tblTest.ValueOfBet)
AS
SumOfValueOfBet, Avg(tblTest.ValueOfBet) AS AvgOfValueOfBet
FROM tblTest
GROUP BY tblTest.MemberName, tblTest.HorseName;

--
Brendan Reynolds (MVP)

In palin English, I have a table based on club members and several
other
fields of numeric data. Some of the fields are numbers, some
currency
and
some text. This data deals with members betting on horse races.

My question is how to display a summary of each members bets based
on
horse
names, as opposed to the entire recordset showing all transactions?
It
is
possible that a member can have more than one bet on the same horse
on
the
same day.

Thanks.