Datestamp for Checkbox-Query

[Ran]

The reason a join query wont work is because there a two purposes for my db, first I receive raw uncoded files from my IT guys in the hospital, these reports get loaded into my db, queries perform alpha split by worker so they can code the procedures, now they have do major research to see what insurance they have i.e FC Groups=MCL,COMM and Payor Detls=TEXT, there thousand of procedures, multiple lines per patient, would take them hours to manually. That is why I want to have an update query update these fields for them, there are other fields needed updating, but trying to get this one to work. I was previously doing these updates in excel by vlookups, but I wanted to automate.
Second part of the db, after these alpha splits are coded, they are extracted and OLIED into our BILLING system to bill, those coded alpha splits are then re-imported into my HIM MASTER file for collectors to use for other queries and forms I have built for them to use to collect on major/high priced surgeries that require documentation. I built all kinds of tables/relationships/forms/queries for the collectors and everything works fine for them. I am trying to automate the pre-process for my coders to speed up there porcess by updating 3 columns with data from other tables or a query. Help a little better? Would it help if I post the data that needs to be updated to the HIM RAW DATA table?

[June7]

That whole business flow is very confusing and guess I will just have to accept that your back is against a wall and must do this update.

I did suggest that data would be helpful. Both the HIM RAW DATA and other tables.

[Ran]

Well you are correct, it isnt a must, I just really wanted to help them and i know it can be done, I am just teaching myself as I go with VBA and mySQL, I learn quick as I wrote a regular UPDATE query, but I dont want to write 78 UPDATE queries. I built this db for myself a year ago to help keep track of major procedures, then I started developing other queries that were helping my Analyst, they love it. Then my Director caught wind of it and wants to implement it, this is where all these new additions are coming from. But if these last two UPDATE queries are completed, the db will be backloaded with tons of data and ready to release for users to use

[June7]

Encountered issues with the datasets you provided.

Earlier you said there were 78 records in Insurance Groups table, I have only 38. HIM RAW DATA has 54 (55 if you count Null) distinct Sins values.

CPT field in HIM RAW DATA is number type, in CPT-RVU it is text. Cannot join these fields directly. I created a query to convert the CPT to text, then can join the data.

HIM RAW DATA has 3593 records. When I LEFT join it to CPT-RVU I have 3746 records. That tells me there are duplicate CPT codes in CPT-RVU.

I added Insurance Groups to the query by RIGHT join on Sins fields and the record count goes to 3892, means duplicate Sins codes in Insurance Groups. Confirmed duplicate of G79 and X30 and deleted dup. The record count went back to 3746.

Fix the data issues and this query might be what you want:

SELECT HIMcpttext.*, [CPT-RVU].[RVU-RCA], [Insurance Groups].[FC Groups], [Insurance Groups].[Payor Details]
FROM [Insurance Groups] RIGHT JOIN
((SELECT [HIM RAW DATA].*, Format([CPT],"0") AS CPTtext
FROM [HIM RAW DATA]) As HIMcpttext LEFT JOIN [CPT-RVU] ON HIMcpttext.CPTtext = [CPT-RVU].CPT) ON [Insurance Groups].[S ins] = HIMcpttext.[S ins];

You can either use that query for the users to view data or use it as basis for UPDATE.

[Ran]

June7, I am sorry about that, you are correct about the Insurance groups table, I had it filtered for MCL (which equals 38) only when I copied the data into the zipped db.

Yeah cpt field is a number type, reason being the excel sheets I receive are a general type, then converted to a number format for no import errors. Will that affect other queries that have the cpt field number type if converted to text?

As for the Insurance Groups and S ins tables they were created by another analyst, my fault for not verifying the data. Thank you once again for the time in responding to my questions. Most helpful in getting a better understanding of a cleaner db.

When CPT-RVU is joined to HIM RAW DATA CPT field, there are patients that have multiple different surgeries/procedures done on same day, next day, etc. so there always duplicates cpt's. We have a unique identifier/primary key to solve that issue Charge-ID, that field was created to to let us know it is not a duplicate.

I am back to work tomorrow, I will try the sql statement. But if I do want it as a UPDATE query, so as I import new data into the table, it will apply all new rows with corresponding group/detail, just change the SELECT to UPDATE?

[June7]

Will that affect other queries that have the cpt field number type if converted to text?

Certainly if attempting to join to a number field. Display in textboxes should not be issue.

change the SELECT to UPDATE

No, can't just change the SELECT word to UPDATE. UPDATE requires the UpdateTo row of the query design grid be set. Also, the update will not work because of duplicate CPT code records in CPT-RVU. The query won't know which one to use. Actually, I think the last record read of each CPT in CPT-RVU will provide the update value.

Example:
CPT 77003
10 records in HIM RAW DATA
3 records in CPT-RVU, RVU-RCA values are 1.84, 0.97, 0.87
Which of the 3 CPT-RVU records should be used to update the 10 records in HIM RAW DATA? The one with the last ID number? Or the sum of all three?

[Ran]

Ok did some review of the tables and you are correct about S ins table having 2 dups, removed those. As for the CPT-RVU table, ran a duplicate query and it seems like there are 159 records that have multiple dups by same CPT/RVU, I will go over these values today and remove. As for CPT 77003 having 3 different RVU values, I wll find out which one will be used for coding and go over all these CPT/RVU's.
Once I remove all these duplicates and remove CPT codes like 77003 can the query you provided be changed to an update query to update these columns?

[Ran]

Ok I removed all duplicates from Insurance Groups and CPT-RVU tables, verified data. Now is there a way to rewrite the the SELECT Query statement to make it an UPDATE query to update the FC Groups, Payor Dtls and CPT-RVU fields with relational data? A little excited to see this work!

[June7]

Open the SELECT query in design view, click UPDATE from the query design tab. Under the fields that need to be updated, in the UPDATE TO row type in the field that the value needs to come from. Prefix with table name: [CPT-RVU].[RVU-RCU]. Fields that don't need to be updated don't need to be in the grid but should not be an issue. Be sure to test this on copy of HIM RAW DATA. Access Help has guidelines on building queries with the designer.

[Ran]

June7, I came to work today and input the UPDATE TO fields and changed the tables and fields, and WA-LA! After removing the duplicates from all tables like you said, life was a lot easier, I trusted my analyst to provide me these tables she used, and that made my life harder in trying to build an UPDATE query. Soon as the dups are removed it works like a charm.

I just want to say thank you for all your help and being patient with understanding the workflow! The db is pretty close to being operational as I am building all new master tables to reflect all of 2011 to back load the db. Thank you once again.