Join Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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;
 
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.
Click to expand...
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;
Click to expand...
The JOIN looks fine, so check your data. I think fldICD9code and
fldTypeCode are either NULL or "".


mfG
--> stefan <--
 
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
 
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
Click to expand...
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.
Click to expand...
Create a new database and import the two tables.


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

thanks, robert
 
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;
 
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.
Click to expand...
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 <--
 
Stefan, Thanks for sticking with this. I tried your suggestion to no avail.
Any other suggestions.

Thanks again, Robert
 
Back
Top