How to combine the VALUE of one columns with the VALUE of another column to produce a text field

[siffanee]

Hello!


I hope someone can help me. I am a novice in coding and need some guidance.
I have two columns in a table; one titled Doctor and the other Actual Charges. See image below:

I would like to create a text field (in a report) that returns the Actual Charges for each Doctor in their respective text fields. See image below:


Is this possible?

[rpeare]

You're looking at a crosstab query, the row headers might be tricky though, at first glance I'm thinking a series of crosstab queries for each charge type, your table structure isn't really conducive to this type of crosstab.

It would be better if the structure was like:

tblDoctor
Doc_ID  Doc_Name
1       Wolfe
2       Ahmad
3       Condevaux

tblChargeType
CH_ID  CH_Name
1      Regular Charges
2      OB Charges

tblDoctorLog
DC_ID  Doc_ID DC_Date

tblDoctorLogCharge
DLC_ID  DC_ID  CH_ID  CH_Qty  CH_Amt

In other words a properly normalized structure.

If you can't or don't want to normalize your structure you would likely have to artificially normalize it with a 'pre' query then run your crosstab query based on the 'pre' query.

[siffanee]

Thank you, rpeare! I will restructure my db and look up crosstab queries! Thanks again!