Query Design Need Help

[orangeo23]

Design a query that displays the band name, the number of events that they have played at, the number of musicians in the band, the total amount earned by the band and the total amount earned by each band member (assuming that the fee is split equally).

Please see the attached screen shot

 

[plog]

You didn't post a question. When you just post instructions it makes us feel like we are doing your homework. While we are happy to help you with your homework, we don't take too kindly to doing it for you.

So, let me help you with the questions you didn't ask:

You're getting more/higher results than you expected because you have a Cartesian Join (http://c2.com/cgi/wiki?CartesianJoin). Your Muscians table needs to be linked to the others. My guess is it should be linked to Band data via another table. That table would have Band Name and unionNo fields in it so that you could tell which muscians are in which band.

You are not going to be able to get all the information you need from one query. You are going to need some sub-queries. Work on one piece of data at a time--once it works, bring in the next and make sure the other fields you have still are correct. If they aren't and you can't figure it out, post back here with what you are trying, any error messages, or what you are actually getting vs. what you expect.

 

[orangeo23]

I am really sorry that i actually didn't ask a Q as mentioned by you.
Anyway next time i be more care full.

I am try yours instruction if any thing comes up will let u know.

Thx for help.

 

[orangeo23]

Im getting big amount of sales figurs

 

[Cronk]

Is it good or not good that you have a big amount?

 

[plog]

I'm going to yank a meaningful post out of you yet:

... and you can't figure it out, post back here with what you are trying, any error messages, or what you are actually getting vs. what you expect.

 

[orangeo23]

Hi, Here is a situation I have

one Musicians table with fields {unionNo PK, Surname, Forenames, Instrument}

2nd Event table with fields {Event Id PK, Price, Group, Venue, Band Name}

3rd Bandmembers table {BandName, UnionNo}

4th Band table {Band Name PK, Price}

5th Group table {Group PK, Practice Night}

Which table do i have to use for this query.

thx in advance

 

[orangeo23]

Im new here so cant post screen shots i need at least 10 post at least

 

[orangeo23]

I cant email or share screen shots

 

[orangeo23]

try to show all my tables

 

[orangeo23]

soon will upload screen shots

 

[orangeo23]

I have attached database file as well. Please let me know wht to do. yhx in advance.

 

[orangeo23]

You didn't post a question. When you just post instructions it makes us feel like we are doing your homework. While we are happy to help you with your homework, we don't take too kindly to doing it for you.

So, let me help you with the questions you didn't ask:

You're getting more/higher results than you expected because you have a Cartesian Join (http://c2.com/cgi/wiki?CartesianJoin). Your Muscians table needs to be linked to the others. My guess is it should be linked to Band data via another table. That table would have Band Name and unionNo fields in it so that you could tell which muscians are in which band.

You are not going to be able to get all the information you need from one query. You are going to need some sub-queries. Work on one piece of data at a time--once it works, bring in the next and make sure the other fields you have still are correct. If they aren't and you can't figure it out, post back here with what you are trying, any error messages, or what you are actually getting vs. what you expect.

*************************************************
Here is my attached database for yours kind consideration.

 

[jdraw]

Here is a free data model that may get you started. It doesn't deal with Price as such, but you could work that in. This will give you a picture of bands and gigs for comparison to your situation. And you can remove the pieces that don't fit into your business.
http://www.databaseanswers.org/data_models/bands_and_gigs/index.htm

 

[orangeo23]

Here is a free data model that may get you started. It doesn't deal with Price as such, but you could work that in. This will give you a picture of bands and gigs for comparison to your situation. And you can remove the pieces that don't fit into your business.
http://www.databaseanswers.org/data_models/bands_and_gigs/index.htm

many thx will update you soon

 

[orangeo23]

I'm going to yank a meaningful post out of you yet:

Any help...

 

[plog]

I don't want the whole thing. I want to know what you have tried and where you are having issues. Walk me through your specific problem.

 

[Cronk]

At last there is some meaningful information about your table structure.

What your original post was missing was the BandMembers table which shows the members of any particular band.

Create a query with the following SQL and advise if that is what you want.

SELECT Bands.[Band Name], Count(Events.[Event Id]) AS [CountOfEvent Id], Sum(Bands.Price) AS SumOfPrice
FROM Musicians INNER JOIN ((Bands INNER JOIN Events ON Bands.[Band Name] = Events.[Band Name]) INNER JOIN [Bandmembers(1)] ON Bands.[Band Name] = [Bandmembers(1)].BandName) ON Musicians.unionNo = [Bandmembers(1)].UnionNo
GROUP BY Bands.[Band Name];

To create the query from SQL, start creating a query but do not add any tables, then go to Home |View | SQLview and paste the text into the window, then go into design view

 

[orangeo23]

At last there is some meaningful information about your table structure.

What your original post was missing was the BandMembers table which shows the members of any particular band.

Create a query with the following SQL and advise if that is what you want.

SELECT Bands.[Band Name], Count(Events.[Event Id]) AS [CountOfEvent Id], Sum(Bands.Price) AS SumOfPrice
FROM Musicians INNER JOIN ((Bands INNER JOIN Events ON Bands.[Band Name] = Events.[Band Name]) INNER JOIN [Bandmembers(1)] ON Bands.[Band Name] = [Bandmembers(1)].BandName) ON Musicians.unionNo = [Bandmembers(1)].UnionNo
GROUP BY Bands.[Band Name];

To create the query from SQL, start creating a query but do not add any tables, then go to Home |View | SQLview and paste the text into the window, then go into design view

****************************************************

Here is the screen shot of output after running SQL query.
Now how do calculate each musician amount. Thanks for yours kind help

 

[orangeo23]

I don't want the whole thing. I want to know what you have tried and where you are having issues. Walk me through your specific problem.

Hi, I manage to run Query, but im stuck how to group bands and price together then how to get each musician amount. thx in advance