Crosstab query - adding percentages

[bruegel]

I am new to crosstab queries so please forgive if this is naïve.



I was trying to add percentages together in a crosstab query. Perhaps it’s something they don’t do?

This is the query showing the percentage of each song that I control. Sometimes they add up to 100%, sometimes they don’t.

SongTitle WriterLastName WritersShare

(I Spent Hours Again) Wishing You Well Martin 43.75%
(I Try To) Flow It Out Hinds 50.00%
(I Try To) Flow It Out Hinds 50.00%
3:38* Underwood 20.00%
3:38* Sager 20.00%
3:38* Smith 20.00%
3:38* Stewart 20.00%
3:38* Waddington 20.00%
7th Heaven Walsh 33.33%
A Blacksmith Courted Me Collins 100.00%
A History Of Now Tailor 47.50%
A History Of Now Savale 47.50%

All I want to do is have a row for each song and the total I own. However the crosstab query doesn’t seem to recognise the percentage as a figure which can be summed. Is this correct? I thought this was just formatting and the underlying figure could be added like any number. (I’ve checked that original table format is number).

Help as always most gratefully received

Pieter

[June7]

Why would a crosstab be needed? I am looking at the data in the db posted in your other thread. What is the calculation to determine your share?

[bruegel]

There has been a good deal of restructuring since my original post and I am not working from the data as previously supplied. Briefly we kept running into problems because we were working from imported excel data which meant the move from relational normalised data had been made too early in the process and flexible manipulation of data was being severely limited. We decided to work from access data until we were forced to make the move into single rows as near the final step as possible.

Apart from anything else I find this all a valuable learning process. While the forum can provide incredibly helpful fixes it can also force learning curves. I'm amazed what I've managed to accomplish with VBA since the fiery baptism a couple of weeks ago. However if I find myself trying too many fixes I go back and question the original db design. In this case the owned share had been imported from an an excel sheet as a figure. For all sorts of reasons, not least underlying data can change, I thought it best to look at calculating the share rather than accepting the given figure. It also meant I had to learn something about crosstab queries.

I hope this make sense.

Pieter

[June7]

A little, however I am still not understanding why crosstab is involved in summarizing data. Do you want to provide the latest db version?

[bruegel]

I've uploaded an mdb called publisher share.zip The only linked data on share of song is stored in the junction table. The only way I can see of calculating the publisher share is adding together the shares of the writer published by that publisher.

The structure looks a bit odd as I had to strip out data to keep the size down

Than you

Pieter

[June7]

I am looking at your db and the only query (PublisherShareofSong) is not a crosstab but a simple join of tables.

Do a GROUP BY (aggregate) query that will summarize by song and publisher. Try:

SELECT SongTitles.SongTitleID, Publishers.PublisherID, (1-Sum([WritersShare]))*100 AS PubShare
FROM WritersShares INNER JOIN (Writers INNER JOIN (SongTitles INNER JOIN ([Song/Writer/Share/Publisher] INNER JOIN Publishers ON [Song/Writer/Share/Publisher].PublisherID = Publishers.PublisherID) ON SongTitles.SongTitleID = [Song/Writer/Share/Publisher].SongTitleID) ON Writers.WriterID = [Song/Writer/Share/Publisher].WriterID) ON WritersShares.ShareID = [Song/Writer/Share/Publisher].ShareID
GROUP BY SongTitles.SongTitleID, Publishers.PublisherID;

Now this query can be used in other queries by joining on the Writer and/or Publisher ID fields.

[bruegel]

Yes it was simple join of tables. It was the query on which I tried to base a crosstab query. However as I couldn't get a crosstab query to add percentages (or even find the percentages to add as it didn't seem to recognise them) I didn't have anything to show other than the joined tables to show the writer shares I wished to total.

I ran your query but it gave me incorrect results

7th Heaven has one of my writers at 33.33 but (1-Sum([WritersShare])*100 gives 66.67

A History of Now has two of my writers at 47.5 each but (1-Sum([WritersShare])*100 gives 5

I get the impression that your query was meant as a starting point - I'm sure there's a way but it's now past midnight here and I have to get up early

Thanks for the help thus far

Pieter

[June7]

Okay, try:
SELECT SongTitles.SongTitleID, Publishers.PublisherID, (1-First([WritersShare]))*100 AS PubShare
FROM WritersShares INNER JOIN (Writers INNER JOIN (SongTitles INNER JOIN ([Song/Writer/Share/Publisher] INNER JOIN Publishers ON [Song/Writer/Share/Publisher].PublisherID = Publishers.PublisherID) ON SongTitles.SongTitleID = [Song/Writer/Share/Publisher].SongTitleID) ON Writers.WriterID = [Song/Writer/Share/Publisher].WriterID) ON WritersShares.ShareID = [Song/Writer/Share/Publisher].ShareID
GROUP BY SongTitles.SongTitleID, Publishers.PublisherID;

[bruegel]

SELECT SongTitles.SongTitle, Sum(WritersShares.WritersShare) AS SumOfWritersShare, Publishers.Publisher
FROM WritersShares INNER JOIN (Writers INNER JOIN (SongTitles INNER JOIN ([Song/Writer/Share/Publisher] INNER JOIN Publishers ON [Song/Writer/Share/Publisher].PublisherID = Publishers.PublisherID) ON SongTitles.SongTitleID = [Song/Writer/Share/Publisher].SongTitleID) ON Writers.WriterID = [Song/Writer/Share/Publisher].WriterID) ON WritersShares.ShareID = [Song/Writer/Share/Publisher].ShareID
GROUP BY SongTitles.SongTitle, Publishers.Publisher
HAVING (((Publishers.Publisher) Like "Caco*"))
ORDER BY SongTitles.SongTitle;

but I still don't get the crosstab refusal to add %'s

Pieter

[June7]

That query is your solution? It does not calculate publisher share. It sums the writer share which is what I did in my first suggested query and used to calc publisher share. You said that output was wrong. What is the formula for publisher share? Post the crosstab SQL you attempted.