Adding a unique identifier to each record

[Jessica240]

Hello everyone!

Here is what I'm trying to do:

I have a single table that lists patients and different surgical procedures they have had. Each surgery is identified by a CPT code. For example:

Patient Procedure Description CPT Code



Bob Leg Amputation 76532
Cindy Spine Repair 45883
Pat Vasectomy 69335
Doug Spine Repair 45883

All I want to do is assign each code its own identifier which could just be a single letter, so then I would have:


Patient Procedure Description CPT Code Identifier

Bob Leg Amputation 76532 A
Cindy Spine Repair 45883 B
Pat Vasectomy 69335 C
Doug Spine Repair 45883 B

Every month I am going to have a new table with new patients with different procedures. The table will always have less than 26 procedures, so that's why I figured the alphabet could be the identifier. When I refresh the table in my database, I need it to be identified by a variable, so that when I run my other queries, they will work the way I need them to.

So I have built a table that is strictly the alphabet:

Letter

A
B
C
....


How do I tell Access to slap these letters onto my patient table, keeping them unique to the CPT code?

Any help is much much appreciated

[June7]

Have another field in the alphabet table for the CPT code. But don't really understand the dynamics of your data.

One month the associations will be:

A 76532
B 45883
C 69335

and next month could be

A 94173
B 76539
C 43910

???

[Jessica240]

Each month I get the table that lists patients and the surgical procedures they have had. I then have to calculate a dollar amount of what the patient owes based on a set of pre-determined rates that are associated with each procedure.

For example, say leg amputations cost $9,000.00, skin grafts cost $500.00, and a spine repair cost $850.00.

Then say a patient, Rex, comes and has one of each of these procedures all on the same day. The most expensive procedure is charged at 100% and all subsequent charges are billed at 50%.

I then would have a formula that says Rex owes $X amount:

X = $9000.00 + ($850)(50%) + ($500)(50%)
X = $9000.00 + $425 + $250
X = $9675.00

My patient table lists the patient, the procedure, and the rate for that procedure. What I'm doing is designing a set of queries that will identify the most expensive procedure and multiply that times 1 or 100%. Then, I have some queries that will multiply the rate times 0.5 if they are not the highest costing procedure.

So lets say I have a patient, Judy, who has 2 leg amputations on the same day. She would owe the full amount for the first surgery, and 50% for the second surgery:

X = $9000.00 + ($9000.00)(50%)
X = $13,500.00

The reason I wanted to have the identifying variables is so that when I create my queries for the calculations, I can use variables in the formula that will be the constant.

Here is an example of one of my calculated fields:

a Payable: IIf([998 - AMOUNT of surgeries 1]![a]=[998 - AMOUNT of surgeries 1]![MAX Of MCARE RATE] And [998 - COUNT of Surgeries 1]![Total Of SURGERIES]>1,[998 - AMOUNT of surgeries 1]![a]+([998 - COUNT of Surgeries 1]![C a]-1)*([998 - AMOUNT of surgeries 1]![a]/2),[998 - Bricker AMOUNT of surgeries 1]![a]/2)

So with the formula above, lets say that "a" is the constant that is associated with the procedure leg amputation which cost $9000.00. The count for leg amputations = 2 for Judy. The formula is telling Access that if $9000 = the highest amount of surgeries for Judy and there are more than 1 of these surgeries, then take $9000.00 + (2-1)($9,000/2); otherwise, take $9,000 divided by 2.

So, in this case, both the criteria in the iif statement are true: $9,000 was the highest costing surgery and the count was greater than 1 so Judy owes a total of $13,500.00.


I simply need a way to run this same query every month when I get my new patient tables without having to manually plug in the CPT code or the dollar amount into the formula each time.

[rpeare]

this is my example data:

ID	ClientID	ProcedureDate	ProcedureID	ProcedureCost
1	1	1/1/2014	1	$9,000.00
2	1	1/1/2014	1	$9,000.00
3	2	1/2/2014	1	$9,000.00
4	2	1/2/2014	2	$850.00
5	2	1/2/2014	3	$500.00

This is my SQL:

SELECT tblTest.ClientID, tblTest.ProcedureDate, Max(tblTest.[procedurecost]) AS MaxCost, Sum(tblTest.ProcedureCost) AS TotalCost, Max([procedurecost])+((Sum([procedurecost])-Max([procedurecost]))*0.5) AS BillableFROM tblTest
GROUP BY tblTest.ClientID, tblTest.ProcedureDate;

This is my result:

ClientID	ProcedureDate	MaxCost	TotalCost	Billable
1	1/1/2014	$9,000.00	$18,000.00	$13,500.00
2	1/2/2014	$9,000.00	$10,350.00	$9,675.00

[Jessica240]

Wow that's genius!

Although I created the exact table you had and named it "tblTest". Then I copied the SQL and tried to run it, but a window popped up saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

[rpeare]

Jessica240.zip

There aren't any reserved words in it here's a sample

[rpeare]

you may need to put a space after FROM to get it to work (after looking at what I pasted before, not sure where the space went)

[Jessica240]

Thank you! Fixing the space got it to work perfectly!!! I'm very impressed!

But I need the resulting table to include the ProcedureID.

So I added the "tblTest.procedureID" to the SELECT clause, to make your SQL look like this:

SELECT tblTest.ClientID, tblTest.ProcedureDate, Max(tblTest.procedurecost) AS MaxCost, Sum(tblTest.ProcedureCost) AS TotalCost, Max([procedurecost])+((Sum([procedurecost])-Max([procedurecost]))*0.5) AS Billable, tblTest.PROCEDUREID
FROM tblTest
GROUP BY tblTest.ClientID, tblTest.ProcedureDate, tblTest.PROCEDUREID;


This is the result:

ClientID	ProcedureDate	MaxCost	TotalCost	Billable	PROCEDUREID
1	1/1/2014	$9,000.00	$18,000.00	13500	1
2	1/2/2014	$9,000.00	$9,000.00	9000	1
2	1/2/2014	$850.00	$850.00	850	2
2	1/2/2014	$850.00	$850.00	850	3

When one patient has only one procedure, the Billable calculations are correct. But when one patient has more than one procedure, it splits up each procedure on its own row, causing "Billable" to not calculate properly.

Is there anyway to get around that?

[rpeare]

Are you attempting to find the procedure ID of the most expensive procedure of the day?

[Jessica240]

Yes, even if a patient has surgeries on more than one day they still have to pay 100% for the highest costing surgery for each day and 50% for all other surgeries that same day.

[rpeare]

so what happens if on the same day a patient has two surgeries that cost the same? How are you going to determine which one shows as the procedure of record?

[Jessica240]

We don't have 2 procedures that cost the same so that wouldn't be an issue.

[rpeare]

Just because you don't now, doesn't mean you wouldn't in the future. If you wanted to link it by dollar value then you can create another query linking the one I already gave you back to your procedures table and link the two by the cost and return the procedure ID from your procedure table, not from the services rendered table.

[Jessica240]

Thank you so much for your help! You really have made my day! I added to your reputation! You are for sure an Access GENIUS!


Have a great weekend and THANK YOU again!!!!!!!

Jessica

[rpeare]

Just one more word of caution here... if you update the prices in your services table and you are not keeping a history the query will only work for what you have NOW. Let's say you have two services that are currently 450 and 500 and the 500$ service is the most expensive service TODAY, a month from now you update the costs associated with each procedure so that the 450 service is now 500 and the 500 service is now 600, the query will show, if you reprint the data for the same period, that the 450 dollar service was the most expensive one.

What I'm trying to say is linking data between tables that DOES NOT follow the PK/FK rules is asking for trouble down the road.