Display Remaining Units within time frame

[Stanggirlie]

I have a database being used to track and bill therapy units. I have a table that stores the pre-authorizations that tells me within a [start date] and [end date] I am limited with X amount of units (that is all the client's insurance and/or funding source will pay for within the time period). I also have about 4 different codes I can be authorized for which is also stored in this table - each code a separate pre-auth record.



Another table is where I enter in the type of therapy (service code) I've completed with the client. I put in the Service date, start time and end time and it will automatically give me the amount of units to bill (1 hour=1 unit). If I happen to choose a code or enter in a service date that does not match up with a client's pre-auth (Service date is between start date and end date and matches code from pre-auth table) I have a simple text box that says "No auth for this code or service date" thanks to an IIF statement in a query. I'd like to take this a step further and give me the remaining units for the pre-authorization left when it does find a match.

What is happening is when it finds the authorization, it's not taking into consideration all the services I've done for that time frame. For example: I have a pre-auth from 12/1/12 to 1/31/13 for code 90806 for 12 units. I served the client for that code a total of 10 units thus far. I can't figure out how to link my 4 service records dated 12/3/12 (2 units), 12/16/12 (4 units), 12/27/12 (2 units), and 1/3/13 (2 units) to add together and then subtract from my auth for 12 units. I know the dates are the key but I'm lost. I'm hoping I'm just overlooking a simple step - I'm a bit burned out.

I don't know SQL. If SQL is the only way, I'll give it a shot. Any help you can provide will be greatly appreciated.

[June7]

You build queries then you know SQL. SQL is the language for queries. Open a query in SQL View and you will see the SQL statement. VBA and macros are the programming tools in Access.

So you need to sum the services for that client for that authorization for that authorization's date range? Is 90806 the authorization code? Is it a unique field in the authorization table? Is that code saved in the services table? If that is a unique ID (primary key?) in authorization table and it is also in the services table as foreign key, don't think the dates have a bearing on the issue. Also, if that code is associated with only one client, this should be quite simple.

Depending on answers to above questions, possible solutions:

Create a field with a DSum expression in query that retrieves the authorization records, something like:

SumServices: DSum("Units", "Services", "ClientID=" & [ClientID] & " AND AuthCode = '" & [AuthCode] & "' AND ServiceDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

Assuming Code is a text field. If it isn't, remove the apostrophe delimiters.

Or maybe a GROUP BY (aggregate) query that sums the data of the Services table by grouping on the authorization code then join that query to the authorizations table linking on the AuthCode.

SELECT AuthCode, Sum(Units) As SumUnits FROM Services GROUP BY AuthCode;

[Stanggirlie]

You build queries then you know SQL. SQL is the language for queries. Open a query in SQL View and you will see the SQL statement. VBA and macros are the programming tools in Access.

Sorry, I meant VBA. I'm not versed in VBA but I can read it so I guess I'm halfway there! hehe!. I'm very versed in Macros however. Thank you for your response. I am going to try your suggestions today.

[Stanggirlie]

Thank you for the suggestions. I got it to work and sum correctly. I appreciate the time you took to help. I will mark this solved.