Adding a unique identifier to each record

[Jessica240]

Hello again... I have run into a small problem with this query: Access is interpreting the numbers incorrectly when it determines the "max" or the "min"

For example:

My table shows:

Name.....Item#........Billable



Bob.........433.......$1037.44
Bob..........674.......$641.57
Bob..........595.......$636.92


So Bob has had 3 different items. Each with a different price. I need my query that looks at this table to give me the maximum value for Bob's billable amount, which should be item 433 for $1037.44.

Instead, Access seems to think item #674 at $641.57 is highest.


So to do a test, in my query design grid, I changed "max" to "min" and the results showed the $1037.44 as being the minimum.

Clearly whats happening is Access is looking at each digit in the amount and determining what is high or low based off of the first digits on the left side and working to the right. Since "6" is the highest first digit, followed by the "4" and so on..... The "1" in $1,037.44 is the lowest first digit so thats why Access thinks that is the minimum.

How can I work around this?

[rpeare]

is your MAX or MIN on the Item#? What are you trying to accomplish, to find out what the max billable was, and the item# associated with that or just the max bill.

if it's the latter just create a query with the 'name' field and the 'billable' field and put the 'max' aggregate in the billable column

If it's the former, create the same query above, then link that query back to your original table vial the 'name' and 'billable' columns to retrieve the item # field.

[Jessica240]

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:
Code:
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]

Procedure ID is the same thing as Item# in the first table.

The result table is what I need and it works beautifully. I need to know how much the highest procedure cost, so that I can subtract it from the total cost, and take the remaining cost times 50% to derive the Billable amount.

The end goal is to calculate what each patient owes the hospital which depends on what procedures they had on one day. The highest procedure we charge them at 100% and all other procedures from the same day are charged at 50%....

[rpeare]

I don't understand. The query I helped you with prior to this gives you the results does it not? You are showing the results after your SQL statement. It's showing you the highest cost service, the total cost, and the final bill (highest bill amount) + (remainder of bill * .5).

[Jessica240]

Yes the results typically do give me accurate results.... until today. The problem is that the field "MaxCost" is not showing me the true highest cost for one of our patients....

Bob had 3 procedures on the same day.... one cost $1037.44, another cost $641.57, and the last procedure cost is $636.92.


And so my results SHOULD look like this:

ClientID	ProcedureDate	MaxCost	TotalCost	Billable
1	1/1/2014	$1037.44	$2315.93	$1,676.65

but instead they look like this:

ClientID	ProcedureDate	MaxCost	TotalCost	Billable
1	1/1/2014	$641.57	$2315.93.00	$1,478.75

So whats happening is Access thinks that Bob's highest procedure is the one that cost $641.57 and that the lowest procedure costs is $1037.44 because the first digit "6" in $641.57 is higher than the first digit "1" in $1037.44. This results in my Billable amount being lower than it should.

[rpeare]

is your cost actually being stored as currency or is it being stored as a string (text) value? The only way this should be happening is if the original value is text instead of numeric.

[Jessica240]

The original table has the cost stored as a number. It was stored as a general number with field size set to double. I will change it to currency and see if that helps...

[rpeare]

Are you using the FORMAT command in any of your queries to change the value to show as currency? if you are, don't - use CCUR instead of FORMAT. The FORMAT command basically makes a number into a string which may be the issue.

[June7]

Maybe better - don't bother with formatting in query. Do it in textbox on form or report.

However, if this issue is occurring with only one patient - are you sure only 1 patient is incorrect?

[Jessica240]

Well I was able to change the data type to currency and get it to work... but here is the problem with that:

I have one master table that all of my queries are based off of. The "ProcedureCost" field was set to general number in this table, so I went into the design view of the table and changed it to currency.

So my MASTER table went from this:

RVU	ClientID	ProcedureDate	ProcedureID	ProcedureCost
1	1	1/1/2014	1	9,000
FLAT	2	1/1/2014	5	8,000

To this:

RVU	ClientID	ProcedureDate	ProcedureID	ProcedureCost
1	1	1/1/2014	1	$9,000.00
FLAT	2	1/1/2014	5	$8,000.00

So then I have a query that looks at my master table (as shown above), and calculates a new cost using the following expression:

PROCEDURECOST1: IIf([RVU]="FLAT","350",[ProcedureCost])

So what this second query does, is it basically checks the RVU field in the master table for the word "FLAT". When the RVU in the table says "FLAT", this indicates that the cost of the procedure needs to be replaced with a flat $350.00 instead of whatever the cost is in the master table.

My results give me the same fields from my master table, except now I have a field called "ProcedureCost1" instead of "ProcedureCost"

RVU	ClientID	ProcedureDate	ProcedureID	ProcedureCost1
1	1	1/1/2014	1	9,000.00
FLAT	2	1/1/2014	5	350.00

See how the 350 replaced the $8,000? But now the data in the "ProcedureCost1" field are no longer currency.

The data revert back to general number. I know its something to do with the expression I use to calculate that field, but I'm not sure how to fix the expression so that the currency formatting doesn't change.

My query that you designed for me uses this query as its data source, so I need this query to have currency as the data type for procedurecost1. Since the expression I was using is what caused the data to be the wrong data type, I took it out and just left the field "ProcedureCost" as it was. When I did this, the data turned to currency. So I saved it, and then ran the query that you designed and it worked perfectly.....

But I really need to put my expression back in there so that the flat rates stay at $350.00... But do you know of a way I can use that expression without it affecting my data types?

[June7]

Don't put 350 within quotes. However, the Currency formatting still might not carry through with any expression.

I don't even understand why the Currency field type is offered. Why bother with setting this in table or query? Set formatting in forms and reports.

[rpeare]

PROCEDURECOST1: ccur(IIf([RVU]="FLAT",350,[ProcedureCost]))

or instead of using ccur (change to currency)

just set the properties in the query design window

[Jessica240]

OOOHHHH I didn't know that the "ccur" function existed! Wow that just blew my mind!!!! That will definitely come in handy for sure! Thank you so so much! You are a life saver for sure!!!!!!