Join not working Properly

[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;

 

[Jeff Boyce]

You've described a "how" question ... but it will depend on "what". What is
the underlying data structure from which you are trying to do this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[RobUCSD]

two tables, 1) tblPMHdx 2) tblPMHlookUP both have primary keys. in talble 2
all fields are populated, table 1 is a patient table that is linked by PINo
to a primary table.

The problem is that while I am getting datafrom fldDxDescrption in the newly
joined data set, the other 2 fields from talbe 2 are not bringing any data. I
have no Idea why. Hope you can help.

thanks, R

 

[Jeff Boyce]

Are you using an "equi-join" (only find data when value match in both
tables)?

What if you don't have values in the patient table -- what should the
join/query show?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[RobUCSD]

It is an equi join, if there are no values in the patient table then there
should be no values in any of the fields. The query is filtered by form on
the PINo.

 

[Gary Walter]

Are you saying that with this *exact* query
(no filtering), the 2 fields are "blank"
-- for all records?
-- for some records?

SELECT
X.fldPmhDxNo,
X.fldPINO,
U.fldICD9code, <--no get
U.fldDxDescription, <--get
X.fldDateAware,
X.fldDateResolved,
X.fldReasonResolved,
X.fldNotes,
U.fldTypeCode <--no get
FROM
tblPmhDX AS X
INNER JOIN
tblPMHlookUp AS U
ON
X.fldDXLUno=U.fldDxLUNo;

Are these 2 fields type TEXT?

Curious to know what you get
for Len1 and Len2:

SELECT
X.fldPmhDxNo,
X.fldPINO,
Len(U.fldICD9code & "") As Len1,
U.fldDxDescription,
X.fldDateAware,
X.fldDateResolved,
X.fldReasonResolved,
X.fldNotes,
Len(U.fldTypeCode & "") As Len2
FROM
tblPmhDX AS X
INNER JOIN
tblPMHlookUp AS U
ON
X.fldDXLUno=U.fldDxLUNo;

 

[RobUCSD]

for all records.

Are you saying that with this *exact* query
(no filtering), the 2 fields are "blank"
-- for all records?
-- for some records?

SELECT
X.fldPmhDxNo,
X.fldPINO,
U.fldICD9code, <--no get
U.fldDxDescription, <--get
X.fldDateAware,
X.fldDateResolved,
X.fldReasonResolved,
X.fldNotes,
U.fldTypeCode <--no get
FROM
tblPmhDX AS X
INNER JOIN
tblPMHlookUp AS U
ON
X.fldDXLUno=U.fldDxLUNo;

Are these 2 fields type TEXT?

Curious to know what you get
for Len1 and Len2:

SELECT
X.fldPmhDxNo,
X.fldPINO,
Len(U.fldICD9code & "") As Len1,
U.fldDxDescription,
X.fldDateAware,
X.fldDateResolved,
X.fldReasonResolved,
X.fldNotes,
Len(U.fldTypeCode & "") As Len2
FROM
tblPmhDX AS X
INNER JOIN
tblPMHlookUp AS U
ON
X.fldDXLUno=U.fldDxLUNo;