By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

Help in transposing data itno columns

P: 5
Hoping someone can help me. I have 2 Access tables with a one to many relationship. the 1 side table holds the billing information, the many table side holds the billing id and modifier#. Now I need to transpose the data into: billing id, modifier1, and modifier 2.

If I do a crosstab query I get different modifiers as column headings but I only want the 2 columns.

for example:

billing id modifier 1 modifier2

1100 50 49
1101 60 50
1102 47
1103 50

Can someone point me in the way of achieving this. Thanks!
Dec 20 '07 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Use a subquery to "rank" by your modifier and then do your crosstab on the rank.
Dec 20 '07 #2

P: 5
I don't know what you mean "to do a rank in a subquery". How would I do that?


Hoping someone can help me. I have 2 Access tables with a one to many relationship. the 1 side table holds the billing information, the many table side holds the billing id and modifier#. Now I need to transpose the data into: billing id, modifier1, and modifier 2.

If I do a crosstab query I get different modifiers as column headings but I only want the 2 columns.

for example:

billing id modifier 1 modifier2

1100 50 49
1101 60 50
1102 47
1103 50

Can someone point me in the way of achieving this. Thanks!
Dec 21 '07 #3

jaxjagfan
Expert 100+
P: 254
Does each billingid only have 2 possible modifiers? If not then your crosstab is going to display the maximum number of modifiers. If billingid (X) has had 10 modifiers and no others have had more than 10 then the crosstab will display 10 columns.
Dec 21 '07 #4

Rabbit
Expert Mod 10K+
P: 12,357
Well, I'm guessing your data is like this:
Expand|Select|Wrap|Line Numbers
  1. Field1   Field2
  2. 1100     50
  3. 1100     49
  4. 1101     60
  5. 1101     50
  6. 1102     47
  7. 1103     50
  8.  
This:
Expand|Select|Wrap|Line Numbers
  1. SELECT Field1, Field2, 
  2.        (SELECT Count(*)
  3.        FROM Table1
  4.        WHERE Field1 = x.Field1 AND Field2 <= x.Field2) AS Rank
  5. FROM Table1 AS x;
  6.  
Will give you:
Expand|Select|Wrap|Line Numbers
  1. Field1   Field2   Rank
  2. 1100     50       2
  3. 1100     49       1
  4. 1101     60       2
  5. 1101     50       1
  6. 1102     47       1
  7. 1103     50       1
  8.  
Then you crosstab on the Rank field.
Dec 21 '07 #5

P: 5
Thank you so much for your help. I get the query to work but now when I try to do the crosstab query on the query I get a message that microsoft jet does not recognize x.billingid. I'm at a lost.

Well, I'm guessing your data is like this:
Expand|Select|Wrap|Line Numbers
  1. Field1   Field2
  2. 1100     50
  3. 1100     49
  4. 1101     60
  5. 1101     50
  6. 1102     47
  7. 1103     50
  8.  
This:
Expand|Select|Wrap|Line Numbers
  1. SELECT Field1, Field2, 
  2.        (SELECT Count(*)
  3.        FROM Table1
  4.        WHERE Field1 = x.Field1 AND Field2 <= x.Field2) AS Rank
  5. FROM Table1 AS x;
  6.  
Will give you:
Expand|Select|Wrap|Line Numbers
  1. Field1   Field2   Rank
  2. 1100     50       2
  3. 1100     49       1
  4. 1101     60       2
  5. 1101     50       1
  6. 1102     47       1
  7. 1103     50       1
  8.  
Then you crosstab on the Rank field.
Jan 3 '08 #6

Rabbit
Expert Mod 10K+
P: 12,357
You shouldn't be using x.billingid in your crosstab unless your query is actually named x. Don't confuse the alias with the name of your query.
Jan 3 '08 #7

P: 5
I'm using the query to build the crosstab. Here is what I've done.
I have the query "qryBillingDetailRank" which uses the code you provided me:

[code]
SELECT [billingid], [modifier], (SELECT Count(*) FROM tblBillingDetail WHERE billingid = billingdetailrank.billingid AND modifier <= billingdetailrank.modifier) AS Rank
FROM tblBillingDetail AS billingdetailrank; [code]

then I build a crosstab on using the qryBillingDetailRank.

[code]
TRANSFORM First(qryBillingDetailRank.modifier) AS FirstOfmodifier
SELECT qryBillingDetailRank.billingid, First(qryBillingDetailRank.modifier) AS [Total Of modifier]
FROM qryBillingDetailRank
GROUP BY qryBillingDetailRank.billingid
PIVOT qryBillingDetailRank.Rank; [code]

When I run it, I get the message "the microsoft jet database engine does not recognize "billingdetailrank.billingid" as a valid field name or expression.

billingdetailrank.billingid is in the first query.

Is my problem that I have to make a table and run the crosstab on the table and not the query?

I truly appreciate your help. HZ




You shouldn't be using x.billingid in your crosstab unless your query is actually named x. Don't confuse the alias with the name of your query.
Jan 4 '08 #8

Rabbit
Expert Mod 10K+
P: 12,357
I understand now, it does look like you have to create a table first before using the crosstab. I'll test it out and get back to you.
Jan 4 '08 #9

Rabbit
Expert Mod 10K+
P: 12,357
Yep, looks like you'll have to create a table from the ranking query first before crosstabbing.
Jan 4 '08 #10

P: 5
Thank you so much for your patience and help
Jan 4 '08 #11

Rabbit
Expert Mod 10K+
P: 12,357
Not a problem, good luck.
Jan 5 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.