Join Problem

[RobUCSD]

In the join below, all fields from tblPMHlookUP are populated with data (it
is a lookup table after all). When I run the query I get the info I want from
tblPmhDX but I only get the fldDxDesription from tblPMHlooUP. fldICD9code and
fldTypeCode come up with no data. These fields are populated in tblPMHlookUP,
so I don't understand why I'm not pulling the data into the results set.

Any Help would be greatly appreciated. Thank, Rob

SELECT tblPmhDX.fldPmhDxNo, tblPmhDX.fldPINO, tblPMHlookUp.fldICD9code,
tblPMHlookUp.fldDxDescription, tblPmhDX.fldDateAware,
tblPmhDX.fldDateResolved, tblPmhDX.fldReasonResolved, tblPmhDX.fldNotes,
tblPMHlookUp.fldTypeCode
FROM tblPmhDX INNER JOIN tblPMHlookUp ON
tblPmhDX.fldDXLUno=tblPMHlookUp.fldDxLUNo;

 

[Stefan Hoffmann]

hi Rob,

When I run the query I get the info I want from
tblPmhDX but I only get the fldDxDesription from tblPMHlooUP. fldICD9code and
fldTypeCode come up with no data.

SELECT
tblPmhDX.fldPmhDxNo,
tblPmhDX.fldPINO,
tblPmhDX.fldDateAware,
tblPmhDX.fldDateResolved,
tblPmhDX.fldReasonResolved,
tblPmhDX.fldNotes,
tblPMHlookUp.fldICD9code,
tblPMHlookUp.fldDxDescription,
tblPMHlookUp.fldTypeCode
FROM tblPmhDX
INNER JOIN tblPMHlookUp
ON tblPmhDX.fldDXLUno = tblPMHlookUp.fldDxLUNo;

The JOIN looks fine, so check your data. I think fldICD9code and
fldTypeCode are either NULL or "".


mfG
--> stefan <--

 

[RobUCSD]

There is data in the table tblPMHlookUP and in the it's fields fldICD9code
(for value in fldDxDescription there is a corresponding value in fldTypeCode).

Could it be my database is corrupted? I've done compact and repair several
times.

Any Ideas? Thanks, Rob

 

[Stefan Hoffmann]

hi Rob,

There is data in the table tblPMHlookUP and in the it's fields fldICD9code
(for value in fldDxDescription there is a corresponding value in fldTypeCode).
Any Ideas? Thanks, Rob

Of data type are your fields in the ON condition?

Are they numeric or string fields?
If they are numeric, are they floating point or integer?
If they are strings, have they leading or trailing whitespaces?

Could it be my database is corrupted? I've done compact and repair several
times.

Create a new database and import the two tables.


mfG
--> stefan <--

 

[RobUCSD]

These are all text strings with trailing white spaces. I tried the new
database approach and had the same outcome. Any ideas?

thanks, robert

 

[RobUCSD]

I changed things around and now I get the out put I want. The problem now is
that the query will not allow edits. I can't add text to the comments field
or add a new record. Any suggestions would be greatly appreciated.

thanks, Rob

SELECT tblPmhDX.fldPmhDxNo, tblPmhDX.fldPINO, tblPMHlookUp.fldDxDescription,
tblPmhDX.fdlICD9code, tblPmhDX.fldDateAware, tblPmhDX.fldDateResolved,
tblPmhDX.fldReasonResolved, tblPmhDX.fldNotes, tblPMHlookUp.fldTypeCode
FROM tblPmhDX INNER JOIN tblPMHlookUp ON tblPmhDX.fdlICD9code =
tblPMHlookUp.fldICD9code;

 

[Stefan Hoffmann]

hi Rob,

I changed things around and now I get the out put I want. The problem now is
that the query will not allow edits. I can't add text to the comments field
or add a new record. Any suggestions would be greatly appreciated.

Both tables need a primary key. You have to add the primary key fields
of both tables in your query. It should then be updateable.


mfG
--> stefan <--

 

[RobUCSD]

Stefan, Thanks for sticking with this. I tried your suggestion to no avail.
Any other suggestions.

Thanks again, Robert