Cross-tab won't work here...any ideas (1 Viewer)

[Therat]

I have two columns of data: Person ID and a charge code. A person can have a number of different charge codes (different procedures). I want to create a table with one person ID per record and their associated charge codes. I don't want a cross-tab because there are too many charge codes and it would be ugly.

If someone could write an expression for me that numbers each person charge code starting at one, I could create a cross-tab table from the new table. The order of the codes is not important in these data.

My goal format:

Person |Chargecode1|Chargecode2|etc.
1 |55145 |66145 |55891
2 |55895 |67715 |55525
etc.....

I've attached a very small example file for you to add the expression.

Thanks in advance!
TheRat

 

[Jon K]

You can do it with a series of two queries. Run the second query in the database.

 

[Therat]

Thanks Jon!

Works like a charm.

 

[Therat]

For learning sake.....

How do you write the statement if the person ID is a text field and the charge code is a text field?


Thanks in advance,
TheRat

 

[Jon K]

The PersonID and ChargeCode fields in the original database are actually text fields.


If what you mean is when PersonID and ChargeCode are both numeric fields, then you can change qryOne to:-

SELECT CInt(MasterCPT4.PersonID) AS PersonID, MasterCPT4.Chargecode, "ChargeCode" & Right(space(3) & DCount("*","MasterCPT4","[PersonID]=" & [PersonID] & " and [ChargeCode]<=" & [ChargeCode]),3) AS Num
FROM MasterCPT4;

that is, remove the four single quotes that surround PersonID and ChargeCode. Numeric fields don't need any delimiters.