Looping growth figures

Question

I'm trying to generate some annual growth figures, which is successful; however I'm getting looping data.

SELECT 
  ProvTotalsPeriod.Province, 
  FormatPercent(([12/31/2014]-[12/31/2013])/([12/31/2013])) AS [Growth 14-13], 
  FormatPercent(([12/31/2013]-[12/31/2012])/([12/31/2012])) AS [Growth 13-12], 
  FormatPercent(([12/31/2012]-[12/31/2011])/([12/31/2011])) AS [Growth 12-11]
FROM 
  ProvTotalsPeriod, Membership;

Query should output into a CrossTab query displaying the 4 provinces and their growth as a percent for each period between 2014-2013, 2013-2012, and 2012-2011. But I get a CrossTab query that runs for 4320 rows long (should be 4). I'm also unsure if I'm handling generating growth figures the best way (plan on inserting them into a new table).

Table: Membership (PK)CU_Name(Text), (PK)Period(Date/Time), Membership_#(Number)

CU_Info (PK)CU_Name(Text), CU_#(Text), Province(Text)

Membership.CU_Name One to Many Membership.Cu_Name

Query: ProvTotalsPeriod

TRANSFORM Sum(Membership.[Membership_#]) AS [SumOfMembership_#]
SELECT CU_Info.Province
FROM CU_Info INNER JOIN Membership ON CU_Info.CU_Name = Membership.CU_Name
GROUP BY CU_Info.Province
ORDER BY Membership.Period DESC 
PIVOT Membership.Period;

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

You must show relation between two tables. Using , you are using CROSS JOIN making combination for every row of each of the tables (cartesian product).

So rows in ProvTotalsPeriod * rows in Membership = 4320.

See more about JOINs

EDIT:

After see the relationship and query can you try removing Membership table:

SELECT 
  ProvTotalsPeriod.Province, 
  FormatPercent(([12/31/2014]-[12/31/2013])/([12/31/2013])) AS [Growth 14-13], 
  FormatPercent(([12/31/2013]-[12/31/2012])/([12/31/2012])) AS [Growth 13-12], 
  FormatPercent(([12/31/2012]-[12/31/2011])/([12/31/2011])) AS [Growth 12-11]
FROM 
  ProvTotalsPeriod;

edited May 23 '17 at 12:22

Community

1
1

answered Aug 24 '15 at 12:23

Bogdan Bogdanov

1,707
2
20
31

So my ProvTotalsPeriod is a matrix query based on Membership and CU_Info (my only 2 tables). I'm having problems creating the inner join between the two. Should I perform it on the ProvTotalsPeriod.Period and Membership.Period? It says it does not recognize ProvTotalsPeriod as valid name – Rob Aug 24 '15 at 13:42
Provide structure of the two tables. After that I can give more specific answer. – Bogdan Bogdanov Aug 24 '15 at 13:44
CU_Info: (PK)CU_Name(text), CU_#(Number), Province(Text) Membership: (PK) CU_Name(Text), (PK)Period(Date), Membership_# (Number) ProvTotalsPeriod: TRANSFORM Sum(Membership.[Membership_#]) AS [SumOfMembership_#] SELECT CU_Info.Province FROM CU_Info INNER JOIN Membership ON CU_Info.CU_Name = Membership.CU_Name GROUP BY CU_Info.Province ORDER BY Membership.Period DESC PIVOT Membership.Period; – Rob Aug 24 '15 at 13:47
Sorry, can you update the question? I don't understand how table CU_Info fits inside the query from the question. – Bogdan Bogdanov Aug 24 '15 at 13:48
Just added the relationship and table structure into the original question – Rob Aug 24 '15 at 14:04
Ok, I don't have data in front of me, also I created PIVOTs in access 10 years ago. So I am not sure how to change query. My point is that you have to figure out how to change query to make relation. You can check if I am right if you multiply rows in ProvTotalsPeriod by rows in Membership. You must receive 4320. Also I see that inside `ProvTotalsPeriod` you have `Province` why you just try to remove `Membership` table? – Bogdan Bogdanov Aug 24 '15 at 14:18

Looping growth figures

1 Answers1