Hello All,
Perhaps someone can help solve this dilemma:
I receive a large flat file from an Rx vendor – from this file I need to be able to run reports based on the Rx name and therapeutic class/subclass.
The Rx Names has to be derived from a more detailed field (Label Name) in the vendor’s file.
Question: 1) What is the best way to link the original file (tblA) to the list of rx names and classes (tblB)
Step 1: Import Rx Data (large flat file) from Vendor
tblA: Fields
CLIENT ID Text
GROUP ID Text
GROUP NAME Text
MBR FIRST NAME Text
MBR LAST NAME Text
MBR ID# Text
RELATION Text
PERSON CODE Text
MBR DOB Date/Time
SS# Text
PH NABP# Text
PH NAME Text
RX# Text
DATE FILLED Date/Time
NEW-REFILL Text
SUB DATE Date/Time
PAY TYPE Text
NDC # Text
LABEL NAME Text
CLAIM STAT Text
PRES ID# Text
PRES DEA# Text
Quantity Double
Days Supply Double
AWP EX AMT Currency
PVT PAY AMT Double
ING COST Currency
DISP FEE Currency
Sales Tax Currency
Copay Currency
CL DUE Currency
Rx Count Double
DMR+HMS Count Double
InvDate Date/Time
Step 2: Separate the Rx name from the Label Name field
The label name includes additional information, which is not important for the following steps
"SELECT DISTINCT Left([label name],InStr([label name]," & "'" & " ')) AS fldRxName FROM tblA"
In some cases the Rx Name is more than 1 word and the RxName will have to be manually edited (such as METFORMIN ER) for reporting
LABEL NAME RxName
LORAZEPAM TAB 1MG LORAZEPAM
PENICILLN VK TAB 500MG PENICILLN
HYDROCO/APAP TAB 5-325MG HYDROCO/APAP
BUSPIRONE TAB 15MG BUSPIRONE
ATENOLOL TAB 50MG ATENOLOL
METFORMIN ER TAB 1000MG METFORMIN ER
The unique names are then added to tblB and assigned a class and subclass (both of which are stored in separate tables)
fldRxName (no duplicates allowed)
fldID (PK – autonumber)
fldClass
fldSubclass
VBA:
strSQL1 = "SELECT DISTINCT Left([label name],InStr([label name]," & "'" & " ')) AS fldRxName FROM tblA"
Set RST = DB.OpenRecordset(strSQL1)
Set rst1 = DB.OpenRecordset("tblB")
'Append data to tblB (unique values only)
RST.MoveFirst
With RST
Do Until .EOF
rst1.AddNew
rst1!fldrxname = !fldrxname
rst1.Update
.MoveNext
Loop
End With
Each month when the flat file is received, this process will be run, because Rx names are added to the list as they appear in the utilization reports.
How do I link tblA to tblB and can I do it via VBA? I prefer not to store a lot of querydefs.
Thanks!
Perhaps someone can help solve this dilemma:
I receive a large flat file from an Rx vendor – from this file I need to be able to run reports based on the Rx name and therapeutic class/subclass.
The Rx Names has to be derived from a more detailed field (Label Name) in the vendor’s file.
Question: 1) What is the best way to link the original file (tblA) to the list of rx names and classes (tblB)
Step 1: Import Rx Data (large flat file) from Vendor
tblA: Fields
CLIENT ID Text
GROUP ID Text
GROUP NAME Text
MBR FIRST NAME Text
MBR LAST NAME Text
MBR ID# Text
RELATION Text
PERSON CODE Text
MBR DOB Date/Time
SS# Text
PH NABP# Text
PH NAME Text
RX# Text
DATE FILLED Date/Time
NEW-REFILL Text
SUB DATE Date/Time
PAY TYPE Text
NDC # Text
LABEL NAME Text
CLAIM STAT Text
PRES ID# Text
PRES DEA# Text
Quantity Double
Days Supply Double
AWP EX AMT Currency
PVT PAY AMT Double
ING COST Currency
DISP FEE Currency
Sales Tax Currency
Copay Currency
CL DUE Currency
Rx Count Double
DMR+HMS Count Double
InvDate Date/Time
Step 2: Separate the Rx name from the Label Name field
The label name includes additional information, which is not important for the following steps
"SELECT DISTINCT Left([label name],InStr([label name]," & "'" & " ')) AS fldRxName FROM tblA"
In some cases the Rx Name is more than 1 word and the RxName will have to be manually edited (such as METFORMIN ER) for reporting
LABEL NAME RxName
LORAZEPAM TAB 1MG LORAZEPAM
PENICILLN VK TAB 500MG PENICILLN
HYDROCO/APAP TAB 5-325MG HYDROCO/APAP
BUSPIRONE TAB 15MG BUSPIRONE
ATENOLOL TAB 50MG ATENOLOL
METFORMIN ER TAB 1000MG METFORMIN ER
The unique names are then added to tblB and assigned a class and subclass (both of which are stored in separate tables)
fldRxName (no duplicates allowed)
fldID (PK – autonumber)
fldClass
fldSubclass
VBA:
strSQL1 = "SELECT DISTINCT Left([label name],InStr([label name]," & "'" & " ')) AS fldRxName FROM tblA"
Set RST = DB.OpenRecordset(strSQL1)
Set rst1 = DB.OpenRecordset("tblB")
'Append data to tblB (unique values only)
RST.MoveFirst
With RST
Do Until .EOF
rst1.AddNew
rst1!fldrxname = !fldrxname
rst1.Update
.MoveNext
Loop
End With
Each month when the flat file is received, this process will be run, because Rx names are added to the list as they appear in the utilization reports.
How do I link tblA to tblB and can I do it via VBA? I prefer not to store a lot of querydefs.
Thanks!