Re: MER - complicated join
- From: John W. Vinson <jvinson@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 06 Nov 2007 00:04:49 -0700
On Mon, 5 Nov 2007 17:59:00 -0800, mark r <markr@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
I am going to need lots of help and patients with this one.
I think my basic question is:
How do I set up a join so that my main table can get prices attached to it
for 4 different fields which hold the same data type?
I have received error messages such as: Ambiguous join error . . . . . .it
tells me to create a separate query and then include that query in the sql -
- - I don't know even how to do a separate query. Can you break this down
into small steps with explanations please. cell phone: 954-234-4239
The main data table ENCOUNTER which has fields
proc_code
proc_code1
proc_code2
proc_code3
the pricing for these codes reside in table named procedurecodes
and the 3 fields in PROCEDURECODES are:
procedure fees description
So each record in ENCOUNTER has up to 4 codes populated.
I need to run a query which will result in a price for each proc_cod.
Thus
record 1 proc_code 55788 proc_code1 55899 proc_code2
55898
Your table structure IS WRONG.
If you have four codes today, someday you'll need five... or six. You have a
many (encounters) to many (procedures) relationship! The proper way to model
this is with three tables:
Encounters
RecordNo
<information about the encounter as a whole>
Procedures
Proc_Code (Primary Key)
Cost (currency)
<other information about the procedure)
ProceduresUsed
RecordNo <link to Encounters>
Proc_Code <link to Procedures>
You can then add two, or four, or seven procedures to the ProceduresUsed
table; do a very simple Totals query to sum the costs; use a Crosstab query if
you need the "many fields" view for display or export to a spreadsheet; very
simply search for which procedures were in which encounters; etc.
should RESULT IN:
record 1 proc_code 55788 $74 proc_code1 55899 $86 proc_code2 55898
$120. . . .etc
So if I do a standard join I drop records.
Below is the code I used.
below is some code I started using
FROM UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, PROVIDERTAXID, procedurecodes AS
procedurecodes_1 INNER JOIN (procedurecodes INNER JOIN ((ALF INNER JOIN
pt_ALF ON ALF.ALFid = pt_ALF.ALFid) INNER JOIN (encounter INNER JOIN
diagnosis ON encounter.diagcode = diagnosis.diagcode) ON pt_ALF.ID_pt_ALF =
encounter.ID_pt_ALF) ON procedurecodes.procedure = encounter.proc_code) ON
procedurecodes_1.procedure = encounter.proc_code2
With your current spreadsheetish structure you'll need to join the procedures
table *FOUR TIMES*, once to each Proc_Code field, all using Left Outer Joins
rather than inner joins.
You'll have exactly the same problem with your diagnosis codes, unless each
encounter/patient has one and only one diagnosis.
John W. Vinson [MVP]
.
- Prev by Date: Re: Open a Form with Info from Subform
- Next by Date: RE: Using Transactions
- Previous by thread: Re: Open a Form with Info from Subform
- Next by thread: Re: MER - complicated join
- Index(es):
Relevant Pages
|
