Using a Query to Update 1 field from Another Field

[Gizmo0917]

I have 2 tables

Table 1
LabID (This is a number that designates what type of lab it is depending on the number)this is the primary key
LabNam (this is the Name of the LAB)

Table 2

LabNum (this is the same # as LabID and links the two tables)
TypeLoc (loc num such as 10N(trauma) which tells where they were treated)
Labtype (Same as LabName in table 1)

I would like to create a query that will change the typeLoc from 10N to Trauma as well as the other TypeLoc codes such as 3N/C to SICU and so on for each TypeLoc. Then I want it to give me a table that shows the LabName performed for each Type Loc and how many were done for each Lab Name and Unit. In other words right now I get the info in a txt format. I manually change the LabNum to the LabName depending on what number is associated with it. Then I manually change the name of each type Local as follows

10N-Trauma, 3N/C-SICU 5SPI-PICU, S441-BURN and (4CN,4EN1,4EN2,4NI1,4NI2,4NI3,4NI4)-are all NICU I do a cross tab query in excel to total all of these and then enter them into the excel spreadsheet for each type on a monthly basis. I would like to automate this process as much as possible but I am having trouble. Any help would be appreciated. I am not good at VB Code and have more knowledge of Access but any help would be appreciated.

 

[rac2]

The association of codes and descriptions can be achieved automatically by creating a table just for that purpose. In any queries where you want to show the description instead of the code you JOIN this table to the table with the detail data. The JOIN is based on the code column which is used in both tables.

Suppose the codes and descriptions are in a table named DecoderRing with columns code and description; and that the details about laboratory tests are in a table named LabWork. LabWork has a code column that you want to translate into a description.

SELECT dr.description, lw.specimen_type, lw.result, lw.technician
FROM LabWork lw
JOIN DecoderRing dr ON 
     lw.code = dr.code

This can also be used with aggregate queries to count and total.

If you already have a table with the various codes in it then you can add a new column to it with the descriptions.


For your problem it looks like Table 1 would be the table to add the column with the descriptions and Table 2 would be the detail table.

 

[Gizmo0917]

Thanks!