MER - complicated join

M

mark r

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

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
 
J

John W. Vinson

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
Click to expand...

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
Click to expand...

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]
 
M

mark r

Thanks, three items please:
Your table structure IS WRONG.
Click to expand...

1. I agree. This started as a "simple need" and now the user requirement
changed. If I set up the proper table structure as you suggested, what do I
do with the current data (2500 records)? I would have to strip out the 2500
current procedure codes and insert them somehow into the new ProceduresUsed
table.
How? Could I copy the old encounters table (data and structures) and then
delete all fields except 'procedure' and then set a one-to-many relationship?

2. You foresee no problems placing the multiple procedure codes for a single
encounter onto a single report page, for 50 encounters in a report?

3. FOR temporary purposes, while I am restructuring all the TABLES and
REPORTS and Queries, please show me how to do all the LEFT OUTER JOINS using
the code below. Perhaps you could explain how these left outer joins work
conceptually so that I may understand joins better.
 
J

John W. Vinson

Thanks, three items please:


1. I agree. This started as a "simple need" and now the user requirement
changed. If I set up the proper table structure as you suggested, what do I
do with the current data (2500 records)? I would have to strip out the 2500
current procedure codes and insert them somehow into the new ProceduresUsed
table.
How? Could I copy the old encounters table (data and structures) and then
delete all fields except 'procedure' and then set a one-to-many relationship?
Click to expand...

Create the new normalized table, and run four append queries. It's not as hard
as it sounds!
2. You foresee no problems placing the multiple procedure codes for a single
encounter onto a single report page, for 50 encounters in a report?
Click to expand...

No more problems than you already have, no. You can base the report on a
Crosstab query to spread the procedure codes across the page instead of down.
Don't forget that data *storage* and data *presentation* are two quite
separate processes with different requirements!
3. FOR temporary purposes, while I am restructuring all the TABLES and
REPORTS and Queries, please show me how to do all the LEFT OUTER JOINS using
the code below. Perhaps you could explain how these left outer joins work
conceptually so that I may understand joins better.
Click to expand...

The default INNER JOIN returns a record only if values exist in both tables. A
LEFT OUTER JOIN returns the record from the "left" ("Preserved") table,
regardless of whether there is a match or not.

Your existing query doesn't seem to be complete, or doesn't make sense to me:
it starts in the middle with a FROM, and I don't know what the field or table
names are. In the query grid, you could add your main (encounters?) table, and
then add the procedures table *four times*. Drag proc_code to proc_code in the
first instance, proc_code1 to proc_code in the second instance, and so on;
select each of the four join lines in the grid and view the join Properties.
Select option 2 - "Show all records in <main table> and matching records in
Procedures".

John W. Vinson [MVP]
 
M

mark r

then add the procedures table *four times*. Drag proc_code to proc_code in the
first instance, proc_code1 to proc_code in the second instance, and so on;
select each of the four join lines in the grid and view the join Properties.
Select option 2 - "Show all records in <main table> and matching records in
Procedures".
Click to expand...
I did this - it does not work . . .you ignored the very first part of my
posting from the get go. I wrote that 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 think the code writer gets confused on how to order the joins. that is why
I asked you to write it out manually. PArt of your reporting solution won't
work for me, so I need this temporary fix before I start rewriting this
entire application. So I need to get this join to work temporarily.

SOFAR I understand the code should look like this, however this join
expression is not supported:

procedurecodes AS procedurecodes_1 LEFT OUTER 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

If I remove the keywords OUTER I get the "ambiguous" message and if I do an
INNER , I drop records
 
M

mark r

then add the procedures table *four times*. Drag proc_code to proc_code in the
first instance, proc_code1 to proc_code in the second instance, and so on;
select each of the four join lines in the grid and view the join Properties.
Select option 2 - "Show all records in <main table> and matching records in
Procedures".
Click to expand...

As I explained at the ery beginning of my post, your suggestion does not work.

error messages such as: Ambiguous join error . . . . . .it
tells me to create a separate query and then include that query in the sql -

procedurecodes as procedurecodes_1 LEFT OUTER 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

does not work.

The maintable is ENCOUNTER; it needs to join to
Diagnosis
PT_ALF (which in turn ties to ALF)
Procedurecodes four times

I tried selection to (left outer) with all joins:
drag encounter.proc_code to procedurecodes.procedure option 2
drag encounter.diagcode to Diagnosis.diagcode option 2
drag encounter.pt_ID_alf to PT_ALF.pt_ID_alf

but I still get the "ambiguous" error code
 
J

John W. Vinson

As I explained at the ery beginning of my post, your suggestion does not work.

error messages such as: Ambiguous join error . . . . . .it
tells me to create a separate query and then include that query in the sql -

procedurecodes as procedurecodes_1 LEFT OUTER 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

does not work.
Click to expand...

This is just a PORTION of the SQL.

Please copy and paste the entire SQL code.

John W. Vinson [MVP]
 
M

mark r

SELECT UPIN.upin, pt_ALF.ins_PIN, pt_ALF.ins_assgnmt,
HCFA_ADD_SUP33.sup_name, HCFA_ADD_SUP33.sup_street,
HCFA_ADD_SUP33.sup_city_zip, HCFA_ADD_SUP33.sup_tele,
PROVIDERTAXID.provid_taxid, PROVIDERTAXID.SSN_EIN, encounter.date0service,
pt_ALF.ALFid, pt_ALF.lname, pt_ALF.fname, pt_ALF.dob, pt_ALF.ID_pt_ALF,
encounter.encounterID, pt_ALF.sex, pt_ALF.[MCR#], pt_ALF.MCR_letter,
pt_ALF.ssn, ALF.ALFname, ALF.ALFaddress, ALF.ALFcity, ALF.ALFzip,
ALF.ALFstate, ALF.ALFsite_tele, ALF.ALFadmin_cell, ALF.ALFowner_cell,
IIf(Len([ALF.ALFsite_tele] &
"")>0,[ALF.ALFsite_tele],IIf(Len([ALF.ALFadmin_cell] &
"")>0,[ALF.ALFadmin_cell],IIf(Len([ALF.ALFowner_cell] &
"")>0,[ALF.ALFowner_cell],[ALF.ALFsite_tele]))) AS TELE, encounter.proc_code,
procedurecodes.ins_proc_fee, encounter.modifier, encounter.placecode,
diagnosis.diagcode, pt_ALF.sigonfiledate, encounter.currillness,
encounter.HoldToBill, pt_ALF.[MCD#], pt_ALF.Mpass_no, pt_ALF.goldcard,
pt_ALF.INStype, encounter.currillness, encounter.billingnotes,
encounter.proc_code2, procedurecodes_1.ins_proc_fee AS fee2,
encounter.modifier2, IIf(Len([encounter.proc_code2] &
"")>0,[encounter.date0service],"") AS date0service2,
IIf(Len([encounter.proc_code2] & "")>0,[encounter.placecode],"") AS
placecode2, IIf(Len([encounter.proc_code3] & "")>0,"1","") AS unit2,
encounter.proc_code3, procedurecodes_2.ins_proc_fee AS fee3,
encounter.modifier3, IIf(Len([encounter.proc_code3] &
"")>0,[encounter.date0service],"") AS date0service3,
IIf(Len([encounter.proc_code3] & "")>0,[encounter.placecode],"") AS
placecode3, IIf(Len([encounter.proc_code3] & "")>0,"1","") AS unit3,
encounter.proc_code4, procedurecodes_3.ins_proc_fee AS fee4,
encounter.modifier4, fee4+fee3+fee2+procedurecodes.ins_proc_fee AS
TOTALFEEfour, IIf(Len([encounter.proc_code4] &
"")>0,[encounter.date0service],"") AS date0service4,
IIf(Len([encounter.proc_code4] & "")>0,[encounter.placecode],"") AS
placecode4, IIf(Len([encounter.proc_code4] & "")>0,"1","") AS unit4

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

GROUP BY UPIN.upin, pt_ALF.ins_PIN, pt_ALF.ins_assgnmt,
HCFA_ADD_SUP33.sup_name, HCFA_ADD_SUP33.sup_street,
HCFA_ADD_SUP33.sup_city_zip, HCFA_ADD_SUP33.sup_tele,
PROVIDERTAXID.provid_taxid, PROVIDERTAXID.SSN_EIN, encounter.date0service,
pt_ALF.ALFid, pt_ALF.lname, pt_ALF.fname, pt_ALF.dob, pt_ALF.ID_pt_ALF,
encounter.encounterID, pt_ALF.sex, pt_ALF.[MCR#], pt_ALF.MCR_letter,
pt_ALF.ssn, ALF.ALFname, ALF.ALFaddress, ALF.ALFcity, ALF.ALFzip,
ALF.ALFstate, ALF.ALFsite_tele, ALF.ALFadmin_cell, ALF.ALFowner_cell,
encounter.proc_code, procedurecodes.ins_proc_fee, encounter.modifier,
encounter.placecode, diagnosis.diagcode, pt_ALF.sigonfiledate,
encounter.currillness, encounter.HoldToBill, pt_ALF.[MCD#], pt_ALF.Mpass_no,
pt_ALF.goldcard, pt_ALF.INStype, encounter.currillness,
encounter.billingnotes, encounter.proc_code2, procedurecodes_1.ins_proc_fee,
encounter.modifier2, encounter.proc_code3, procedurecodes_2.ins_proc_fee,
encounter.modifier3, encounter.proc_code4, procedurecodes_3.ins_proc_fee,
encounter.modifier4, encounter.billed
HAVING (((encounter.placecode)="13" Or (encounter.placecode)="31" Or
(encounter.placecode)="32" Or (encounter.placecode)="12") AND
((encounter.HoldToBill)=False) AND ((pt_ALF.INStype)="mcr") AND
((encounter.billed)=False));


this code works fine. as soon as you add multiple occurences of
procedurecodes you drop records. if you change the join to option 2 or 3 in
any combination, you get error message: ambiguous or unsupported.
 
J

John W. Vinson

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
Click to expand...

What are the tables UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, and PROVIDERTAXID?
These are in the query as a Cartesian join, no join expression at all! Unless
they are all single-record tables, this will certainly mess up your query.

And this query as written does NOT include procedurecodes four times - only
once.

The query is sufficiently complex that I'm really leery of trying to tweak it
just in text mode. I wish I could be more help, but this is getting beyond
what I'm comfortable doing on the newsgroup. Perhaps another volunteer could
jump in - I'll ask around.


John W. Vinson [MVP]
 
S

Stefan Hoffmann

hi Mark,

mark said:
this code works fine. as soon as you add multiple occurences of
procedurecodes you drop records. if you change the join to option 2 or 3 in
any combination, you get error message: ambiguous or unsupported.
Click to expand...
Can you publish a working example .mdb somewher?


mfG
--> stefan <--
 
M

mark r

To publish an .mdb, I would have to dummy data and maybe I could use
YOUSENDIT.com if you gave me your email address. But really, this is all
very unnecessary. This solution can be offered conceptually if someone out
there really understands SQL as a day job. I am am part timer.

These files John is worrying about are single record files that get attached
to every output record
[ UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, and PROVIDERTAXID ]

I posted the code the works. The output from that code gives me output
records with the correct fee from procedurecodes associated with
encounter.proc_code for evey output record. There once was a 4GL called
FOCUS wherein you could save this output to a new "output table" by using the
command ON MATCH SAVE AS NEWTABLE.

Then you could use that interim NEW OUTPUT-TABLE to match up once again to
procedurecodes and pick-up the fee for proc_code1 and again ON MATCH SAVE AS
NEWTABLE2. Then you could match again for proc_code2 and then again
Proc_code3 and finally have you end result.

The ACCESS error code I am getting "tells me to create a separate query and
then include that query in the sql -" this sounds to me like the FOCUS type
of logic I just described. I do not know how to this this in ACCESS.

Realize the output to this point is satisfactory and I just need to make
three more passes at procedurecodes to pick up three more fees. How messy
doesnot matter. As long as it works. Then I can get to the business of
normalizing the tables and changing all the queries and reports that follow.

Thanks
 
M

mark r

Yes, these files you are worrying about are single record files that get
attached to every output record
[ UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, and PROVIDERTAXID ]

I posted the code the works. The output from that code gives me output
records with the correct fee from procedurecodes associated with
encounter.proc_code for evey output record. There once was a 4GL called
FOCUS wherein you could save this output to a new "output table" by using the
command ON MATCH SAVE AS NEWTABLE.

Then you could use that interim NEW OUTPUT-TABLE to match up once again to
procedurecodes and pick-up the fee for proc_code1 and again ON MATCH SAVE AS
NEWTABLE2. Then you could match again for proc_code2 and then again
Proc_code3 and finally have you end result.

The ACCESS error code I am getting "tells me to create a separate query and
then include that query in the sql -" this sounds to me like the FOCUS type
of logic I just described. I do not know how to this this in ACCESS.

Realize the output to this point is satisfactory and I just need to make
three more passes at procedurecodes to pick up three more fees. How messy
doesnot matter. As long as it works. Then I can get to the business of
normalizing the tables and changing all the queries and reports that follow.

Thanks
 
M

Michel Walsh

Make a query like:

SELECT *
FROM procedurecodes AS procedurecodes_1
INNER JOIN (procedurecodes
INNER JOIN (ALF
INNER JOIN (pt_ALF
INNER JOIN (encounter
INNER JOIN diagnosis
ON encounter.diagcode = diagnosis.diagcode)
ON pt_ALF.ID_pt_ALF = encounter.ID_pt_ALF)
ON ALF.ALFid = pt_ALF.ALFid)
ON procedurecodes.procedure = encounter.proc_code)
ON procedurecodes_1.procedure = encounter.proc_code2



Note that the ON clause can only refer to tables in its scope. As example:


(ALF
INNER JOIN (pt_ALF
INNER JOIN (encounter
INNER JOIN diagnosis
ON encounter.diagcode = diagnosis.diagcode)
ON pt_ALF.ID_pt_ALF = encounter.ID_pt_ALF)
ON ALF.ALFid = pt_ALF.ALFid)


the last ON clause can use ALF, pt_alf, encounter, or diagnosis, but not
procedureCodes_1, neither proceduresCode.



When you are sure that this query 'works', then use its name in the
original query:

SELECT ...
FROM UPIN, HCFA_ADD_FAC32, HCFA_ADD_SUP33, PROVIDERTAXID, savedQueryHere
GROUP BY ...
HAVING ...


Oh, last thing, the syntax is not [tableName.FieldName] but
[tableName].[fieldName]. Furthermore, since some of the fields will now come
from the query, you will have to use queryName.fieldname.



Vanderghast, Access MVP


mark r said:
SELECT UPIN.upin, pt_ALF.ins_PIN, pt_ALF.ins_assgnmt,
HCFA_ADD_SUP33.sup_name, HCFA_ADD_SUP33.sup_street,
HCFA_ADD_SUP33.sup_city_zip, HCFA_ADD_SUP33.sup_tele,
PROVIDERTAXID.provid_taxid, PROVIDERTAXID.SSN_EIN, encounter.date0service,
pt_ALF.ALFid, pt_ALF.lname, pt_ALF.fname, pt_ALF.dob, pt_ALF.ID_pt_ALF,
encounter.encounterID, pt_ALF.sex, pt_ALF.[MCR#], pt_ALF.MCR_letter,
pt_ALF.ssn, ALF.ALFname, ALF.ALFaddress, ALF.ALFcity, ALF.ALFzip,
ALF.ALFstate, ALF.ALFsite_tele, ALF.ALFadmin_cell, ALF.ALFowner_cell,
IIf(Len([ALF.ALFsite_tele] &
"")>0,[ALF.ALFsite_tele],IIf(Len([ALF.ALFadmin_cell] &
"")>0,[ALF.ALFadmin_cell],IIf(Len([ALF.ALFowner_cell] &
"")>0,[ALF.ALFowner_cell],[ALF.ALFsite_tele]))) AS TELE,
encounter.proc_code,
procedurecodes.ins_proc_fee, encounter.modifier, encounter.placecode,
diagnosis.diagcode, pt_ALF.sigonfiledate, encounter.currillness,
encounter.HoldToBill, pt_ALF.[MCD#], pt_ALF.Mpass_no, pt_ALF.goldcard,
pt_ALF.INStype, encounter.currillness, encounter.billingnotes,
encounter.proc_code2, procedurecodes_1.ins_proc_fee AS fee2,
encounter.modifier2, IIf(Len([encounter.proc_code2] &
"")>0,[encounter.date0service],"") AS date0service2,
IIf(Len([encounter.proc_code2] & "")>0,[encounter.placecode],"") AS
placecode2, IIf(Len([encounter.proc_code3] & "")>0,"1","") AS unit2,
encounter.proc_code3, procedurecodes_2.ins_proc_fee AS fee3,
encounter.modifier3, IIf(Len([encounter.proc_code3] &
"")>0,[encounter.date0service],"") AS date0service3,
IIf(Len([encounter.proc_code3] & "")>0,[encounter.placecode],"") AS
placecode3, IIf(Len([encounter.proc_code3] & "")>0,"1","") AS unit3,
encounter.proc_code4, procedurecodes_3.ins_proc_fee AS fee4,
encounter.modifier4, fee4+fee3+fee2+procedurecodes.ins_proc_fee AS
TOTALFEEfour, IIf(Len([encounter.proc_code4] &
"")>0,[encounter.date0service],"") AS date0service4,
IIf(Len([encounter.proc_code4] & "")>0,[encounter.placecode],"") AS
placecode4, IIf(Len([encounter.proc_code4] & "")>0,"1","") AS unit4

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

GROUP BY UPIN.upin, pt_ALF.ins_PIN, pt_ALF.ins_assgnmt,
HCFA_ADD_SUP33.sup_name, HCFA_ADD_SUP33.sup_street,
HCFA_ADD_SUP33.sup_city_zip, HCFA_ADD_SUP33.sup_tele,
PROVIDERTAXID.provid_taxid, PROVIDERTAXID.SSN_EIN, encounter.date0service,
pt_ALF.ALFid, pt_ALF.lname, pt_ALF.fname, pt_ALF.dob, pt_ALF.ID_pt_ALF,
encounter.encounterID, pt_ALF.sex, pt_ALF.[MCR#], pt_ALF.MCR_letter,
pt_ALF.ssn, ALF.ALFname, ALF.ALFaddress, ALF.ALFcity, ALF.ALFzip,
ALF.ALFstate, ALF.ALFsite_tele, ALF.ALFadmin_cell, ALF.ALFowner_cell,
encounter.proc_code, procedurecodes.ins_proc_fee, encounter.modifier,
encounter.placecode, diagnosis.diagcode, pt_ALF.sigonfiledate,
encounter.currillness, encounter.HoldToBill, pt_ALF.[MCD#],
pt_ALF.Mpass_no,
pt_ALF.goldcard, pt_ALF.INStype, encounter.currillness,
encounter.billingnotes, encounter.proc_code2,
procedurecodes_1.ins_proc_fee,
encounter.modifier2, encounter.proc_code3, procedurecodes_2.ins_proc_fee,
encounter.modifier3, encounter.proc_code4, procedurecodes_3.ins_proc_fee,
encounter.modifier4, encounter.billed
HAVING (((encounter.placecode)="13" Or (encounter.placecode)="31" Or
(encounter.placecode)="32" Or (encounter.placecode)="12") AND
((encounter.HoldToBill)=False) AND ((pt_ALF.INStype)="mcr") AND
((encounter.billed)=False));


this code works fine. as soon as you add multiple occurences of
procedurecodes you drop records. if you change the join to option 2 or 3
in
any combination, you get error message: ambiguous or unsupported.
Click to expand...
 
M

mark r

you are a genius. . . . that seems to work pretty well so far. I have one
small gliche, not a biggy , but maybe you can see what I am doing wrong. I
am trying to get a sum total of the three fee fields. I get no error message
- however neither do I get a total. A snippet of the code follows:


SELECT UPIN.upin,
HCFA1500_mcare_WALSHstep2.fee1, HCFA1500_mcare_WALSHstep2.fee2,
HCFA1500_mcare_WALSHstep2.fee3, HCFA1500_mcare_WALSHKstep2.fee4,
HCFA1500_mcare_WALSHstep2.proc_code,
(HCFA1500_mcare_WALSHstep2.fee1+ HCFA1500_mcare_WALSHstep2.fee2+
HCFA1500_mcare_WALSHstep2.fee3+ HCFA1500_mcare_WALSHstep2.fee4 ) AS
TOTALFEEFOUR
FROM UPIN, HCFA1500_mcare_WALSHstep2
GROUP BY UPIN.upin, HCFA1500_mcare_WALSKstep2.fee1,
HCFA1500_mcare_WALSHstep2.fee2, HCFA1500_mcare_WALSHstep2.fee3,
HCFA1500_mcare_WALSHstep2.fee4,HCFA1500_mcare_WALSHstep2.proc_code;


I also tried putting the sum in the first query (WALSHstep2) and including
HCFA1500_mcare_WALSHstep2.TOTALFEEFOUR in the SELECT statement of the second
query, but that didn't result in a sum either.

i TRIED IT WITH AND WITHOUT PARENTHESIS AND THAT DIDNT MATTER EITHER
 
J

John W. Vinson

you are a genius. . . . that seems to work pretty well so far. I have one
small gliche, not a biggy , but maybe you can see what I am doing wrong. I
am trying to get a sum total of the three fee fields. I get no error message
- however neither do I get a total. A snippet of the code follows:
Click to expand...

If any of the fields is NULL, a sum of them will be NULL. Try changing the
expression to

(NZ(HCFA1500_mcare_WALSHstep2.fee1)+ NZ(HCFA1500_mcare_WALSHstep2.fee2)+
NZ(HCFA1500_mcare_WALSHstep2.fee3)+NZ(HCFA1500_mcare_WALSHstep2.fee4)) AS
TOTALFEEFOUR


John W. Vinson [MVP]
 
M

mark r

Now you're cookin' with grease.
That worked great. Interestingly, the fee fields were of the currency format
with two decimal places $100.00 yet the TOTALFEEFOUR field did not keep the
currency format - it was just whole digits 100

anyway to force the format on the fly? If not, I will just hard code that on
the report - not a biggy

Thanks
 
D

Douglas J. Steele

Format(NZ(HCFA1500_mcare_WALSHstep2.fee1)+
NZ(HCFA1500_mcare_WALSHstep2.fee2)+
NZ(HCFA1500_mcare_WALSHstep2.fee3)+NZ(HCFA1500_mcare_WALSHstep2.fee4)),
"Currency") AS TOTALFEEFOUR


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
M

mark r

Thanks Doug, you come thru again. . . .just a note for anyone else using this
thread to fix their stuff. . . . .
there is an excess paren below. . . . .just omit the last paren after the
word "currency"


go DOUG
 
D

Douglas J. Steele

Omitting the last parenthesis after the word currency won't work. The error
is that there are two closing parentheses before the word currency: there
should only be one.

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
 
Top