Hi Jerry - in looking through the macro, there are two spreadsheets imported
into the Master Table. One that contains the charge detail and one that
gives each patient a sample #. It appears that there are three queries that
are run:
This is first:
INSERT INTO [New Master with Sample] ( [Sample #], CLAIM_NO, PATIENT_MPI,
FACILITY_CODE, LAST_NAME, FIRST_NAME, MID_INIT, FACILITY_NAME,
FINANCIAL_CLASS_CODE, FINANCIAL_CLASS_DESC, PLAN_GROUP, TREATMENT_CODE,
TREATMENT_DESC, TREATMENT_REVENUE_CODE, HCPC_CODE, EPO_MODIFIER,
UNIT_MEASURE_ABBR, SERVICE_DATE, ENDING_SERVICE_DATE, [YEAR], [MONTH],
MODALITY_CODE, MODALITY_DESCRIPTION, QTY, NET_AMOUNT, ICD9_CODE1, ICD9_CODE2,
ICD9_CODE3, ICD9_CODE4, ICD9_CODE5, ICD9_CODE6 )
SELECT [Step 2 - Combine Charge Detail with Sample Numb Info].[Sample ID],
[Step 2 - Combine Charge Detail with Sample Numb Info].[Claim No], [Step 2 -
Combine Charge Detail with Sample Numb Info].[PATIENT MPI], [Step 2 - Combine
Charge Detail with Sample Numb Info].[FACILITY CODE], [Step 2 - Combine
Charge Detail with Sample Numb Info].[LAST NAME], [Step 2 - Combine Charge
Detail with Sample Numb Info].[FIRST NAME], [Step 2 - Combine Charge Detail
with Sample Numb Info].[MID INIT], [Step 2 - Combine Charge Detail with
Sample Numb Info].[FACILITY NAME], [Step 2 - Combine Charge Detail with
Sample Numb Info].[FINANCIAL CLASS CODE], [Step 2 - Combine Charge Detail
with Sample Numb Info].[FINANCIAL CLASS DESC], [Step 2 - Combine Charge
Detail with Sample Numb Info].[PLAN GROUP], [Step 2 - Combine Charge Detail
with Sample Numb Info].[TREATMENT CODE], [Step 2 - Combine Charge Detail with
Sample Numb Info].[TREATMENT DESC], [Step 2 - Combine Charge Detail with
Sample Numb Info].[TREATMENT REVENUE CODE SUM], [Step 2 - Combine Charge
Detail with Sample Numb Info].[HCPC CODE], [Step 2 - Combine Charge Detail
with Sample Numb Info].[EPO MODIFIER], [Step 2 - Combine Charge Detail with
Sample Numb Info].[UNIT MEASURE ABBR], [Step 2 - Combine Charge Detail with
Sample Numb Info].[SERVICE DATE], [Step 2 - Combine Charge Detail with Sample
Numb Info].[ENDING SERVICE DATE], [Step 2 - Combine Charge Detail with Sample
Numb Info].[YEAR SUM], [Step 2 - Combine Charge Detail with Sample Numb
Info].[MONTH SUM], [Step 2 - Combine Charge Detail with Sample Numb
Info].[MODALITY CODE], [Step 2 - Combine Charge Detail with Sample Numb
Info].[MODALITY DESCRIPTION], [Step 2 - Combine Charge Detail with Sample
Numb Info].[QTY SUM], [Step 2 - Combine Charge Detail with Sample Numb
Info].[NET AMOUNT SUM], [Step 2 - Combine Charge Detail with Sample Numb
Info].[ICD9 CODE1], [Step 2 - Combine Charge Detail with Sample Numb
Info].[ICD9 CODE2], [Step 2 - Combine Charge Detail with Sample Numb
Info].[ICD9 CODE3], [Step 2 - Combine Charge Detail with Sample Numb
Info].[ICD9 CODE4], [Step 2 - Combine Charge Detail with Sample Numb
Info].[ICD9 CODE5], [Step 2 - Combine Charge Detail with Sample Numb
Info].[ICD9 CODE6]
FROM [Step 2 - Combine Charge Detail with Sample Numb Info];
This is second:
INSERT INTO [Master Table] ( ID, [Order], [Claim Number],
tblSamplePatient_MPI, FACILITY_CODE, LAST_NAME, FIRST_NAME, FACILITY_NAME,
PRIMARY_FC_MAP, TREATMENT_CODE, TREATEMENT_DESC, [Revenue Code], HCPC_CODE,
EPO_MODIFIER, [Service From], [Service To], MODALITY_CODE, SumOfQTY,
SumOfNET_AMOUNT, [ICD-9 Code], [ICD9 Code_2] )
SELECT [New Master with Sample].ID, [New Master with Sample].[Sample #],
[New Master with Sample].CLAIM_NO, [New Master with Sample].PATIENT_MPI, [New
Master with Sample].FACILITY_CODE, [New Master with Sample].LAST_NAME, [New
Master with Sample].FIRST_NAME, [New Master with Sample].FACILITY_NAME, [New
Master with Sample].FINANCIAL_CLASS_DESC, [New Master with
Sample].TREATMENT_CODE, [New Master with Sample].TREATMENT_DESC, [New Master
with Sample].TREATMENT_REVENUE_CODE, [New Master with Sample].HCPC_CODE, [New
Master with Sample].EPO_MODIFIER, [New Master with Sample].SERVICE_DATE, [New
Master with Sample].ENDING_SERVICE_DATE, [New Master with
Sample].MODALITY_CODE, [New Master with Sample].QTY, [New Master with
Sample].NET_AMOUNT, [New Master with Sample].ICD9_CODE1, [New Master with
Sample].ICD9_CODE2
FROM [New Master with Sample];
this is third, although this does not appear to impact the codes portion of
the DB:
UPDATE [Master Table] SET [Master Table].[Revised Wastage QTY] = [Master
Table]!SumOfQTY, [Master Table].[Revised QTY] = [Master Table]!SumOfQTY,
[Master Table].[If No_A1] = 0, [Master Table].[If No_A2] = 0, [Master
Table].[If No_A3] = 0, [Master Table].[If No_W1] = 0, [Master Table].[If
No_W2] = 0, [Master Table].[If No_W3] = 0, [Master Table].[If No_B1] = 0,
[Master Table].[If No_B2] = 0, [Master Table].[If No_B3] = 0, [Master
Table].DOS_Affected_1 = 0, [Master Table].DOS_Affected_2 = 0, [Master
Table].DOS_Affected_3 = 0, [Master Table].[Revised Revenue] = [Master
Table]!SumOfNET_AMOUNT, [Master Table].[If No_B4] = 0, [Master Table].[If
No_B5] = 0, [Master Table].[If No_W5] = 0, [Master Table].[If No_W4] = 0,
[Master Table].[If No_A4] = 0, [Master Table].[If No_A5] = 0, [Master
Table].DOS_Affected_5 = 0, [Master Table].DOS_Affected_4 = 0;
Thanks for any help you can give me...
Jerry Whittle said:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.
Also is the Excel worksheet linked to the database or is the data imported
into Access?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I'm working with a DB that someone else created which (as far as I can tell)
uses an Append Query to bring data from an Excel spreadsheet into a table.
The problem I am having is that it does not bring all of the data. In the
spreadsheet, there is a column named ICD9 Code1. The column is formatted for
text entry and is filled with codes that are numbers (ex 588.81) or an
alphanumeric code (V07.39).
The append query places the data in a table. The data type for the field
these codes are placed in is Text.
The append query pulls all the numeric codes, but does not pull the
alphanumeric codes.
Any suggestions?
