Help in transposing data itno columns

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!

re: Help in transposing data itno columns

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

re: Help in transposing data itno columns

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

re: Help in transposing data itno columns

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.

re: Help in transposing data itno columns

Well, I'm guessing your data is like this:
This:
Will give you:
Then you crosstab on the Rank field.

re: Help in transposing data itno columns

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.

re: Help in transposing data itno columns

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.

re: Help in transposing data itno columns

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

re: Help in transposing data itno columns

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.

re: Help in transposing data itno columns

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

re: Help in transposing data itno columns

Thank you so much for your patience and help

re: Help in transposing data itno columns

Not a problem, good luck.