Create a query

[Mandoz]

Hello,

I have created a list of bowlers on a table, also I created a query that
provides me the total scores. now I need to create a query that will add the
score of bowler # 1 with the rest of the bowlers and the score of bowler # 2
with the rest of the bowlers and so on. if this is posible in access can you
please help.

 

[Ken Snell [MVP]]

Please show us an example of the data that you want this query to return,
and the starting data that the query will use. It's not clear to me from
your description what you want to see?

 

[Mandoz]

I have a query that adds handicap plus what the bowler actually bowled in 3
games for exapmple bowler # 1 has 15 handicap + game 1 (200) game 2 (200)
game 3 (200) that query will mulitiply the handicap 15x3=45 + 600 = 645.
Bowler # 2 has 10 handicap + game 1 (200) game 2 (200) game 3 (200) total
630, I have 45 bowlers, now what I really need is to create one query that
will add the total for bowler #1 individually with the rest of the 44
bowlers.

The report will look like this Bowler # 1 645 Bowler # 2 630 Total 1275

Sorry if I made if more confusing.

Thanks,

 

[Ken Snell [MVP]]

Please show us the SQL statement of your query. Open the query in design
view, click on the Query View icon on toolbar and select SQL View, and then
copy the text that you see in that window. Paste that text here in your
reply.

Also, tell us about your table's data fields (the table or tables that your
query is using).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Mandoz]

SELECT Registration.FirstName, Registration.[Jr/Sr], Registration.Sex,
Registration.Average, Registration.LaneNumber, [Enter Scores].[Game 1],
[Enter Scores].[Game 2], [Enter Scores].[Game 3],
IIf([Registration]![Average]>=[League Sep Up]![Base on an Average
of],0,([League Sep Up]![Base on an Average
of]-[Registration]![Average])*[League Sep Up]![Handicap is a % of]/100) AS
Expr1, [Expr1]*[League Sep Up]![Number of Games] AS Expr2, [Game 1]+[Game
2]+[Game 3] AS Expr3, [Expr2]+[Expr3] AS Expr4, [Game 1]+[Expr1] AS Expr5,
[Game 2]+[Expr1] AS Expr6, [Game 3]+[Expr1] AS Expr7
FROM [League Sep Up] INNER JOIN (Registration LEFT JOIN [Enter Scores] ON
Registration.FirstName = [Enter Scores].[First Name]) ON [League Sep
Up].[League ID] = Registration.LeagueID;

These are the fields that I have for 3 tables.
Table #1, First Name, Jr/Sr, Sex, Average and League ID
Table #2 League ID, Handicap is a % of, Based on an Average of and No. of
games.
Table #3 First Name, Game 1, Game 2, Game 3

 

[Ken Snell [MVP]]

You say you want to add bowlers' scores together? How do you identify the
specific bowlers whose scores are to be added? Are they all the bowlers in a
specific league? or some other criterion?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT Registration.FirstName, Registration.[Jr/Sr], Registration.Sex,
Registration.Average, Registration.LaneNumber, [Enter Scores].[Game 1],
[Enter Scores].[Game 2], [Enter Scores].[Game 3],
IIf([Registration]![Average]>=[League Sep Up]![Base on an Average
of],0,([League Sep Up]![Base on an Average
of]-[Registration]![Average])*[League Sep Up]![Handicap is a % of]/100) AS
Expr1, [Expr1]*[League Sep Up]![Number of Games] AS Expr2, [Game 1]+[Game
2]+[Game 3] AS Expr3, [Expr2]+[Expr3] AS Expr4, [Game 1]+[Expr1] AS Expr5,
[Game 2]+[Expr1] AS Expr6, [Game 3]+[Expr1] AS Expr7
FROM [League Sep Up] INNER JOIN (Registration LEFT JOIN [Enter Scores] ON
Registration.FirstName = [Enter Scores].[First Name]) ON [League Sep
Up].[League ID] = Registration.LeagueID;

These are the fields that I have for 3 tables.
Table #1, First Name, Jr/Sr, Sex, Average and League ID
Table #2 League ID, Handicap is a % of, Based on an Average of and No. of
games.
Table #3 First Name, Game 1, Game 2, Game 3

--
Bowlin is fun, Bowl in LA

Please show us the SQL statement of your query. Open the query in design
view, click on the Query View icon on toolbar and select SQL View, and
then
copy the text that you see in that window. Paste that text here in your
reply.

Also, tell us about your table's data fields (the table or tables that
your
query is using).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Mandoz]

The table where I have the list of bowlers gives me the option for many
leagues, for right now I only have one league, I have a field for LeagueID.
Thanks,

Armando
--
Bowlin is fun, Bowl in LA

You say you want to add bowlers' scores together? How do you identify the
specific bowlers whose scores are to be added? Are they all the bowlers in a
specific league? or some other criterion?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT Registration.FirstName, Registration.[Jr/Sr], Registration.Sex,
Registration.Average, Registration.LaneNumber, [Enter Scores].[Game 1],
[Enter Scores].[Game 2], [Enter Scores].[Game 3],
IIf([Registration]![Average]>=[League Sep Up]![Base on an Average
of],0,([League Sep Up]![Base on an Average
of]-[Registration]![Average])*[League Sep Up]![Handicap is a % of]/100) AS
Expr1, [Expr1]*[League Sep Up]![Number of Games] AS Expr2, [Game 1]+[Game
2]+[Game 3] AS Expr3, [Expr2]+[Expr3] AS Expr4, [Game 1]+[Expr1] AS Expr5,
[Game 2]+[Expr1] AS Expr6, [Game 3]+[Expr1] AS Expr7
FROM [League Sep Up] INNER JOIN (Registration LEFT JOIN [Enter Scores] ON
Registration.FirstName = [Enter Scores].[First Name]) ON [League Sep
Up].[League ID] = Registration.LeagueID;

These are the fields that I have for 3 tables.
Table #1, First Name, Jr/Sr, Sex, Average and League ID
Table #2 League ID, Handicap is a % of, Based on an Average of and No. of
games.
Table #3 First Name, Game 1, Game 2, Game 3

--
Bowlin is fun, Bowl in LA

Please show us the SQL statement of your query. Open the query in design
view, click on the Query View icon on toolbar and select SQL View, and
then
copy the text that you see in that window. Paste that text here in your
reply.

Also, tell us about your table's data fields (the table or tables that
your
query is using).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a query that adds handicap plus what the bowler actually bowled
in 3
games for exapmple bowler # 1 has 15 handicap + game 1 (200) game 2
(200)
game 3 (200) that query will mulitiply the handicap 15x3=45 + 600 =
645.
Bowler # 2 has 10 handicap + game 1 (200) game 2 (200) game 3 (200)
total
630, I have 45 bowlers, now what I really need is to create one query
that
will add the total for bowler #1 individually with the rest of the 44
bowlers.

The report will look like this Bowler # 1 645 Bowler # 2 630 Total
1275

Sorry if I made if more confusing.

Thanks,
--
Thankyou all good blesse you


:

Please show us an example of the data that you want this query to
return,
and the starting data that the query will use. It's not clear to me
from
your description what you want to see?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hello,

I have created a list of bowlers on a table, also I created a query
that
provides me the total scores. now I need to create a query that will
add
the
score of bowler # 1 with the rest of the bowlers and the score of
bowler #
2
with the rest of the bowlers and so on. if this is posible in
access
can
you
please help.

 

[Ken Snell [MVP]]

But how do you identify "Bowler #1" and "Bowler #2"? Are those the only two
bowlers whose scores are to be added together? I do not understand what you
are wanting to achieve in your final result.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

The table where I have the list of bowlers gives me the option for many
leagues, for right now I only have one league, I have a field for
LeagueID.
Thanks,

Armando
--
Bowlin is fun, Bowl in LA

You say you want to add bowlers' scores together? How do you identify the
specific bowlers whose scores are to be added? Are they all the bowlers
in a
specific league? or some other criterion?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

SELECT Registration.FirstName, Registration.[Jr/Sr], Registration.Sex,
Registration.Average, Registration.LaneNumber, [Enter Scores].[Game 1],
[Enter Scores].[Game 2], [Enter Scores].[Game 3],
IIf([Registration]![Average]>=[League Sep Up]![Base on an Average
of],0,([League Sep Up]![Base on an Average
of]-[Registration]![Average])*[League Sep Up]![Handicap is a % of]/100)
AS
Expr1, [Expr1]*[League Sep Up]![Number of Games] AS Expr2, [Game
1]+[Game
2]+[Game 3] AS Expr3, [Expr2]+[Expr3] AS Expr4, [Game 1]+[Expr1] AS
Expr5,
[Game 2]+[Expr1] AS Expr6, [Game 3]+[Expr1] AS Expr7
FROM [League Sep Up] INNER JOIN (Registration LEFT JOIN [Enter Scores]
ON
Registration.FirstName = [Enter Scores].[First Name]) ON [League Sep
Up].[League ID] = Registration.LeagueID;

These are the fields that I have for 3 tables.
Table #1, First Name, Jr/Sr, Sex, Average and League ID
Table #2 League ID, Handicap is a % of, Based on an Average of and No.
of
games.
Table #3 First Name, Game 1, Game 2, Game 3

--
Bowlin is fun, Bowl in LA


:

Please show us the SQL statement of your query. Open the query in
design
view, click on the Query View icon on toolbar and select SQL View, and
then
copy the text that you see in that window. Paste that text here in
your
reply.

Also, tell us about your table's data fields (the table or tables that
your
query is using).

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have a query that adds handicap plus what the bowler actually
bowled
in 3
games for exapmple bowler # 1 has 15 handicap + game 1 (200) game 2
(200)
game 3 (200) that query will mulitiply the handicap 15x3=45 + 600 =
645.
Bowler # 2 has 10 handicap + game 1 (200) game 2 (200) game 3 (200)
total
630, I have 45 bowlers, now what I really need is to create one
query
that
will add the total for bowler #1 individually with the rest of the
44
bowlers.

The report will look like this Bowler # 1 645 Bowler # 2 630
Total
1275

Sorry if I made if more confusing.

Thanks,
--
Thankyou all good blesse you


:

Please show us an example of the data that you want this query to
return,
and the starting data that the query will use. It's not clear to me
from
your description what you want to see?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hello,

I have created a list of bowlers on a table, also I created a
query
that
provides me the total scores. now I need to create a query that
will
add
the
score of bowler # 1 with the rest of the bowlers and the score of
bowler #
2
with the rest of the bowlers and so on. if this is posible in
access
can
you
please help.