Help in transposing data itno columns

[Haydee Zimmerman]

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!

[Rabbit]

Use a subquery to "rank" by your modifier and then do your crosstab on the rank.

[Haydee Zimmerman]

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!

[jaxjagfan]

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.

[Rabbit]

Well, I'm guessing your data is like this:

Field1   Field2
1100     50
1100     49
1101     60
1101     50
1102     47
1103     50
 

This:

SELECT Field1, Field2, 
       (SELECT Count(*)
       FROM Table1
       WHERE Field1 = x.Field1 AND Field2 <= x.Field2) AS Rank
FROM Table1 AS x;
 

Will give you:

Field1   Field2   Rank
1100     50       2
1100     49       1
1101     60       2
1101     50       1
1102     47       1
1103     50       1
 

Then you crosstab on the Rank field.

[Haydee Zimmerman]

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:

Field1   Field2
1100     50
1100     49
1101     60
1101     50
1102     47
1103     50
 

This:

SELECT Field1, Field2, 
       (SELECT Count(*)
       FROM Table1
       WHERE Field1 = x.Field1 AND Field2 <= x.Field2) AS Rank
FROM Table1 AS x;
 

Will give you:

Field1   Field2   Rank
1100     50       2
1100     49       1
1101     60       2
1101     50       1
1102     47       1
1103     50       1
 

Then you crosstab on the Rank field.

[Rabbit]

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.

[Haydee Zimmerman]

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.

[Rabbit]

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.

[Rabbit]

Yep, looks like you'll have to create a table from the ranking query first before crosstabbing.

[Haydee Zimmerman]

Thank you so much for your patience and help

[Rabbit]

Not a problem, good luck.