Query Design Need Help

[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

Many thx it work fine but the 2nd part of Q is remaining.

My question is

"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)"

 

[Cronk]

You could think about a second query based on the first where you divide the total amount.

 

[orangeo23]

You could think about a second query based on the first where you divide the total amount.

Hi, I have been trying to solve from last 8 hours for part 2
but with out any luck, any further help will be appreciated.
thx in advance

 

[CazB]

try this....

it could probably be done in a more streamlined way as this needs all 3 queries.... just import them all into your database and then run qryBandsEarningsPerMember

 

[orangeo23]

try this....

it could probably be done in a more streamlined way as this needs all 3 queries.... just import them all into your database and then run qryBandsEarningsPerMember

Great works like a dream. You are a Star......

Many thx for yours help