Making Crosstab Query - "Number of Services" Matrix

Milin · May 15, 2000

Hello, I can't quite figure out how to do this - do I need SQL? ...I am a newbie at it I have households of customers(all grouped by a unique household group id number) and the magazine (represented by a magazine code) that they responded to.  What I want to create is a matrix that tells me "In households that have a response to magazine X, within those same households there were also 20 responses to magazine y, 10 responses to magzine z, etc...".  I don't want to see the actual households/group id numbers, just the cumulative results of my "product mix" of magazines in households. I can already make a matrix with magazine code horizonal and household group id number verticle, but what I want is to see the magazine code on both axis. SO a household could be counted in multiple rows, since they would show up in the product mix of every magazine they had a response to. Do I make sense? hard to explain in words. I previously used a banking database that gave me the "cross-sell" of products in households...ie..I could see that if someone had an IRA in their household they were more likely to also own a CD. Thanks for any help or adviseQ Milin

DougP · May 15, 2000

you Might need to make a graph. DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a> Ask me how Bar-codes can help you be more productive.

gol4 · May 16, 2000

in your crosstab query design view where you have the households as a row heading and magazines as a column heading , drag in magazines again and set it as a row heading also this will give               mag1   mag2   mag3 res1 mag1 res1 mag2 res2 mag1 res2 mag2 hope thats what you had in mind

elizabeth · May 16, 2000

Interesting, I've never done one like this before. go14, I tried your method but don't quite understand. Did you skip the crosstab query and build this directly? This is how I did it, getting only count, not percentages: Using a structure of Table2: Household, Mag, Owned (Yes/No) Query1: SELECT Table2.Mag, Table2_1.Mag, Count(Table2_1.Owned) AS CountOfOwned FROM Table2 INNER JOIN Table2 AS Table2_1 ON Table2.PKHousehold = Table2_1.PKHousehold GROUP BY Table2.Mag, Table2_1.Mag; Query1_Crosstab: TRANSFORM Sum(Query1.CountOfOwned) AS SumOfCountOfOwned SELECT Query1.Table2.Mag FROM Query1 GROUP BY Query1.Table2.Mag PIVOT Query1.Table2_1.Mag;

Milin · May 16, 2000

Thanks for your replies! I think Elizabeth is going towards what I'm trying to do - but I'm not sure because I don't quite understand what you wrote.  What is Tablel2_1?  Here's some more info - I have a query that gives me householdID, MagCode, and other address info blah blah. My query and table records look like: 10  Joe Smith  123 Elm   mag1 10  Betty Smith 123 Elm  Mag2 10  John Smith 123 Elm   mag3 20  John Doe 1313 E main  mag4 20  John Doe 1313 E main  mag2 20  John Doe 1313 E main  mag2 with 10 & 20 being the household numbers    I want to make something like                 mag1 mag2 mag3 mag4 mag1 total(1)   1     1    1        0 mag2 total(2)   1     2    1        1 mag3 total(1)   1     1    1        0        mag4 total(1)   1     1    0        1 where Total would be the total number of households that contain mag (but not the household numbers themselves - I already have a cross tab to do that. So, there are a total of two households that have mag2. Within those two mag2 households, one of them also has a mag1 and one of them also has a mag3.  And really, I should create a second calculation for each cell that totals the number of mag codes - so I have the count of households and the count of magcodes. I'm way above myself - can this be done in Access? Would Crystal Reports be better at this?  or should I be paying good money and hiring one of you programmer-types?

Thanks for your thoughts Milin

elizabeth · May 16, 2000

Good, your table structure looks like mine. In the first query, I added Table2 to the query grid twice. The first time it calls itself Tabel2, the second time it defaults to the name Table2_1. This is how Access handles the naming automatically. BTW, I guess the name I used for the Household was actually PKHousehold. If you just sub your table and fieldnames for mine you should be able to paste the SQL into the query window and run it. My crosstab didn't have totals, but here's how it looks if you add them: TRANSFORM Sum([CountOfOwned]) SELECT [Table2_1].[Mag], Sum([CountOfOwned]) AS [Total Of CountOfOwned] FROM Query1 GROUP BY [Table2_1].[Mag] PIVOT [Table2].[Mag];

Milin · May 16, 2000

elizabeth.. This is cool!!! It almost works - what I notice is that in the joining of the table twice in query1, it is counting my magcode totals twice, so the intersection cell of mag1 and mag1 on the crosstab query is very inflated.  I noticed that when I deleted the table2 copy that query1 totalled correctly, but then the crosstab wouldn't work. Otherwise I've followed it exactly as you've written it. Now what

elizabeth · May 17, 2000

If you just want the total of each magazine, leave out the line that creates totals. Look at the intersection of rowA with columnA for the total of A magazines. The totals in this report represent a row total.

Milin · May 17, 2000

Sorry elizabeth - I didn't explain it well... the formulas I'm using are your Query1 and Query1_crosstab.  I'm assuming, like you said, that the intersection of rowA with columnA is the total for A magazine. At first this looked right, but I went back and did testing against my master table (table2) - I queried for all magazine A records and came out with a much lower number.  What I noticed was that query1 was totalling this higher number, but if I removed the Table2_1 references, the query would total the correct number of Magazine A. -- but then the crosstab won't work.  So I guess some how query1 is overcounting the total number of magazines, even though the table is correct. Thanks for your help - this is very cool!

elizabeth · May 17, 2000

Sorry, I don't understand why this is happening with you. Mine are counting accurately. I have 5 household records with MagA and my intersection of rowA with ColA is 5.

Milin · May 17, 2000

ok elizabeth - I think I see why. in my households, I sometimes have duplicate responses to magazine A (partly a dirty data problem and partly someone that responded to the same mag, say 6 mos apart). So I can use this report based on the numbers being the total individual response by mag. My final questions (really! ;-) ) would there be a way to have the queries look for and count only the unique household numbers where magazine A resides -- whether it be 1 or 10 responses to the same mag? That would give me a true household report. Thanks so much

elizabeth · May 17, 2000

Yup, set your first query's Unique Values property to yes. In the QBE grid, right click in the relationship window and select Properties (or click on the Properties Icon), select Unique Values/Yes. If you then view your SQL window you'll see the word DISTINCT added. BTW, you can use the Find Duplicates query wizard to clean up your db, and once it's done you can set a unique index in your table on the combiend fields Household ID & MagID to disallow further dupes. If you're not familiar with building concatenated indexes, just yell. Nobody is really counting the number of questions you ask in a row

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Making Crosstab Query - "Number of Services" Matrix

Milin

Technical User

DougP

MIS

gol4

Technical User

elizabeth

IS-IT--Management

Milin

Technical User

elizabeth

IS-IT--Management

Milin

Technical User

elizabeth

IS-IT--Management

Milin

Technical User

elizabeth

IS-IT--Management

Milin

Technical User

elizabeth

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor