Update or Append Query

[Ran]

Hello,



FYI-I am pretty good with excel and access (2007), just need some advice. Basically I work for a huge hospital and I created a db for a small group of coworkers (10) to identify high priced surgeries by payor (insurance) and boss caught wind and wants to implement more solutions in it.
I have coders that need these raw reports by Alpha, I import raw reports into db, query pulls data, criteria splits by alpha. But now I need to have 2 different tables that would help the coders in coding surgeries faster and limit research. Table 2 I want to update or append data (have the data in the columns) to table 1 before the Alpha split. If cant be done by access, VBA would be helpful. PLease help.

Table 1: HIM RAW DATA (Needs fileds "FC Groups" & "Payor Dtls" updated, these fields quoted have blank fields) Also the "S ins" has data in this field on this table. there is a relationship between both tables.

Table 2: Ins Dtls (has fields called "S ins","FC Groups","Payor Dtls") all three of these fields have data in them 78 rows. Basically "Payor Dtls" and "FC Groups" are related to each other as well as "S ins"


If mtSQL statement needed please let me know. Thank you in advance!

[June7]

Splitting table might not be needed. Would have to know more about data structure. Users should not be working directly with tables anyway. Use forms as data entry interface. Can have specialized forms to limit what fields coders can input to.

[Ran]

Splitting table might not be needed. Would have to know more about data structure. Users should not be working directly with tables anyway. Use forms as data entry interface. Can have specialized forms to limit what fields coders can input to.

Thanks for the reply, I am not splitting db till db is complete. I agree with about users using tables, I am in the implementation mode right now, I will backload data once everything works. This is pretty much last query/update needed. As for data structure I have 2 master tables that all queries are pulled from and forms working off queries. Just need to figure sql statement to force both fields from table "FC Groups" and "Payor Details to update my master table fields "FC Groups" and "Payor Dtls."

So basically all fields in my master table are same as my other two tables, but these to tables have the insurance info and the master table has the raw data but those fieelds are empty and need to be filled with insurance data so coders can work quicker without research

[June7]

Still not understanding. Sounds like form/subform arrangement called for. Do you want to provide project for analysis? Copy database, remove confidential data, run Compact & Repair, zip if large, attach to post. The Attachment Manager is below the Advanced post editor.

[Ran]

Hi June7, I really dont need any project analysis, but thanks. My db is pretty much complete except this last little addition that was thrown at me last second.
I have all the queries and forms working correctly and giving me the data I want for co-workers to see. So no subform needed, as all I want is to have a table "FC Groups and Payor Details," update my existing HIM RAW DATA table with insurance info based off what the patient has for insurance.
HIM RAW DATA table has 28 columns and the FC Groups and Payor Detail tables have same columns but have the data I need in these columns to update my HIM RAW DATA table with insurnace info. Can I paste some sample mySQL to see if any language can be added to fix this?

[Stingaway]

If you can pull the data out into a query, you can update the tables in question with that data.

Sometimes, based on relationships and where I am pulling data from (and depending on the complexity of the query) - in Access I find I have to do a temporary make table, then run the update query from the make table instead of the original query itself. It's not elegant, but it works - and you can macro it to death so that it uses the same table each time, just clears the results before each run of the macro.

Long and the short of it - if you have the data in a separate query or table already and you want to update another table with that data, it is possible.

[Ran]

Thank for the reply Stingaway, I understand your reply if I just had to updated column with one value but not the case.
The issue I am having is that my HIM RAW DATA table has over 5000 records with an Identifier "S ins" column has i.e "X27" (there are 77 other Insurance identifiers) the other two columns FC Groups and Payor Dtls are blank and needs data updated that is associated with data in my other two tables I created FC Groups and Payor Details, the data in these tables are related to "X27" = FC Group "VHP" then = Payor Dtls "Valley Health Plan". My HIM RAW DATA table has the "X27" but empty fileds for FC Groups and Payor Dtls. Is this a VBA solution or SQL?

[Stingaway]

Should be able to do it with sql. If you can sql query the data that you want to populate - inclusive of field lookup values from other tables - you should be able to update any and or all columns in a table from that data set, providing that you have a unique identifier (if using an update) for the particular record in question. You may have to massage the dataset first, but you should still be able to use an update (or append if you like) to get said data into your other table.

[Ran]

Yeah I figured it can be done with mySQL, I just tried creating an UPDATE query by design with this statement just for the FC Groups. See below, let me know what you think since it doesn't work LOL. It said updated "0" rows.

UPDATE [Copy Of HIM RAW DATA] INNER JOIN [Combined SFP] ON [Copy Of HIM RAW DATA].[S ins] = [Combined SFP].[S ins] SET [Copy Of HIM RAW DATA].[FC Groups] = "MCL,MCL,MCL,MCL,COM,MCL,MCL,MCL,MCL,MCL,MCL,COM,C OM,COM,COM,COM,COM,COM,MCL,MCL,MCL,MCL,MCL,MCL,ERR OR,COM,COM,COM,COM,COM,COM,COM,MCL,COM,MCL,MCR,COM ,MCL,MCL,MCL,COM,COM,MCL,MCL,MCR,MCR,MCR,MCR,MCR,M CR,MCR,ERROR,ERROR,MCL,MCL,MCL,MCL,COM,COM,MCL,MCA L,MCL,MCL,COM,COM,MCR,COM,COM,COM,MCL,MCL,COM,COM, MCL,MCL,MCL,MCLMCL"
WHERE ((([Copy Of HIM RAW DATA].[FC Groups])="[Copy Of HIM RAW DATA].[FC Groups]=[Combined SFP].[[FC Groups]"));

Now for the Payor Dtls Column in HIM RAW DATA, I have another Payor Details table that has another 78 rows of data that equal FC Groups.