Assignment Query using total fees by member

[dgr]

First time here, so I hope I'm in the right place.

I am being asked to create a query as follows:

Create a query named FeesPaidByMembers that displays the total registration fees paid by each club member to participate in sports. Each member from the Registration table should be listed as a record. Each sport should be listed as a field. The query should summarize the total amount of registrations paid.

I thought I knew how, but obviously I don't. Can, and will, someone show me how this is accomplished, without using VBA code?

Many thanks!

The tables used are:
Members

ID	AutoNumber	Primary Key
LastName	Short Text
FirstName	Short Text
Active	Yes/No
Telephone	Short Text

Registration

Receipt #	AutoNumber	Primary Key
Member	Short Text
Sport	Short Text
Registration Paid	Currency

The data for each table is:

DATA:

Members
ID	Lastname	Firstname	Active	Telephone
1	Flint	Brian	Yes
2	Hess	Trudy	No
3	Cook	Kevin	Yes
4	Brown	Richard	No
5	Miller	Susan	Yes
6	Wakefield	Randy	Yes
7	Shea	Mark	Yes
8	Christianson	Thor	Yes
9	Bellows	Sam	Yes
10	Graystone	Thomas	No
11	Smith	Shiela	Yes
12	Adler	Pam	No
13	Sloat	Randy	Yes
14	Gates	Greg	Yes
15	Moorefield	John	Yes
16	Cruz	Stephanie	No
17	Lopez	Cindy	Yes
18	Guerrero	Maria	Yes
19	Worldly	Diane	Yes
20	Grey	John	Yes
21	Bower	Doug	No
22	Long	Rhonda	No
23	Wallace	Rick	No
24	Mason	Wendy	No
25	Piening	Deon	No

Registration
Receipt #	Member	Sport	Registration Paid
1	Cook, Kevin	Football	$50.00
2	Flint, Brian	Baseball	$40.00
3	Miller, Susan	Volleyball	$25.00
4	Miller, Susan	Tennis	$30.00
5	Wakefield, Randy	Basketball	$50.00
6	Cook, Kevin	Volleyball	$25.00
7	Cook, Kevin	Tennis	$30.00
8	Shea, Mark	Football	$50.00
9	Christianson, Thor	Basketball	$50.00
10	Bellows, Sam	Tennis	$30.00
11	Bellows, Sam	Volleyball	$25.00
12	Flint, Brian	Tennis	$30.00
13	Shea, Mark	Volleyball	$25.00
14	Sloat, Randy	Basketball	$50.00
15	Christensen, Thor	Baseball	$40.00
16	Smith, Sheila	Tennis	$30.00
17	Smith, Sheila	Volleyball	$25.00
18	Gates, Greg	Football	$50.00
19	Gates, Greg	Volleyball	$25.00
20	Moorefield, John	Baseball	$40.00
21	Lopez, Cindy	Tennis	$30.00
22	Guerrero, Maria	Tennis	$30.00
23	Sloat, Randy	Football	$50.00
24	Worldly, Diane	Volleyball	$25.00
25	Grey, John	Football	$50.00
26	Grey, John	Baseball	$40.00
27	Flint, Brian	Basketball	$50.00
28	Bower, Doug	Tennis	$30.00
29	Long, Rhonda	Volleyball	$25.00
30	Christensen, Thor	Tennis	$30.00

DATA:

Members
ID	Lastname	Firstname	Active	Telephone
1	Flint	Brian	Yes
2	Hess	Trudy	No
3	Cook	Kevin	Yes
4	Brown	Richard	No
5	Miller	Susan	Yes
6	Wakefield	Randy	Yes
7	Shea	Mark	Yes
8	Christianson	Thor	Yes
9	Bellows	Sam	Yes
10	Graystone	Thomas	No
11	Smith	Shiela	Yes
12	Adler	Pam	No
13	Sloat	Randy	Yes
14	Gates	Greg	Yes
15	Moorefield	John	Yes
16	Cruz	Stephanie	No
17	Lopez	Cindy	Yes
18	Guerrero	Maria	Yes
19	Worldly	Diane	Yes
20	Grey	John	Yes
21	Bower	Doug	No
22	Long	Rhonda	No
23	Wallace	Rick	No
24	Mason	Wendy	No
25	Piening	Deon	No

Registration
Receipt #	Member	Sport	Registration Paid
1	Cook, Kevin	Football	$50.00
2	Flint, Brian	Baseball	$40.00
3	Miller, Susan	Volleyball	$25.00
4	Miller, Susan	Tennis	$30.00
5	Wakefield, Randy	Basketball	$50.00
6	Cook, Kevin	Volleyball	$25.00
7	Cook, Kevin	Tennis	$30.00
8	Shea, Mark	Football	$50.00
9	Christianson, Thor	Basketball	$50.00
10	Bellows, Sam	Tennis	$30.00
11	Bellows, Sam	Volleyball	$25.00
12	Flint, Brian	Tennis	$30.00
13	Shea, Mark	Volleyball	$25.00
14	Sloat, Randy	Basketball	$50.00
15	Christensen, Thor	Baseball	$40.00
16	Smith, Sheila	Tennis	$30.00
17	Smith, Sheila	Volleyball	$25.00
18	Gates, Greg	Football	$50.00
19	Gates, Greg	Volleyball	$25.00
20	Moorefield, John	Baseball	$40.00
21	Lopez, Cindy	Tennis	$30.00
22	Guerrero, Maria	Tennis	$30.00
23	Sloat, Randy	Football	$50.00
24	Worldly, Diane	Volleyball	$25.00
25	Grey, John	Football	$50.00
26	Grey, John	Baseball	$40.00
27	Flint, Brian	Basketball	$50.00
28	Bower, Doug	Tennis	$30.00
29	Long, Rhonda	Volleyball	$25.00
30	Christensen, Thor	Tennis	$30.00

[June7]

Sounds like you need a CROSSTAB query. There is a query wizard for that.

Why did you think you knew how and why is it obvious you don't - what did you try?

Did you intend to post the tables twice?

[dgr]

I didn't know how because I'm not comfortable with crosstab queries. So, obviously, I didn't know how to do this one.

No, I didn't intend on posting the data twice. I'm not sure why that happened. I apologize to everyone that it upset.

I thank you for your help. I did some trial and error, with crosstabs (Thank you), and got it to work, finally.





First time here, so I hope I'm in the right place.

I am being asked to create a query as follows:

Create a query named FeesPaidByMembers that displays the total registration fees paid by each club member to participate in sports. Each member from the Registration table should be listed as a record. Each sport should be listed as a field. The query should summarize the total amount of registrations paid.

I thought I knew how, but obviously I don't. Can, and will, someone show me how this is accomplished, without using VBA code?

Many thanks!