One Result from multiple memo fields

On May 7, 12:30 pm, "John Spencer" wrote:
Looks as if we need to add at least one more field to the subquery and join
on that field also

SELECT dbo_RB_JOBNOTE.JOBID
, NZ(T.NOTE, "No Material Listing Found") as MaterialNote
, dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE LEFT JOIN
(SELECT JobID, NOTE, OWNERFULLNAME
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
ON dbo_RB_JobNOTE.JobID = T.JobID
AND dbo_RB_JobNOTE.OWNERFULLNAME = T.OWNERFULLNAME

Somehow I missed the OwnerFullName in your earlier postings. Hopefully the
combination of JobID and OwnerFullName is sufficient to give you the results
you want.

If not, construct a query that gives you what you want if Note is Like
"Material*". Save that query and then use it to build a query with
dbo_RB_Jobnote where you join on the relevant fields. Double-click on the
join lines and set them to show all records in dbo_RB_Jobnote and only
matching in the saved query. If there are no "material" notes then you
will get a record with fields from dbo_RB_JobNote and blank fields for the
fields from the saved query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.





I put in your SQL
Here is a sample of what I am getting:
-------------------------------------------------------
Notes
Jesi
Materials:3 pieces Letter paper
Janice
Materials:3 pieces Letter paper

Notes
George
Materials Used
16 feet of 24 inch satin photo base
16 feet of 40 inch 5 mil laminate
George
Materials Used
16 feet of 24 inch satin photo base
16 feet of 40 inch 5 mil laminate

Notes
George
No Material Listing Found
George
No Material Listing Found
Janice
No Material Listing Found

Notes
Travis
No Material Listing Found
Janice
No Material Listing Found
Janice
No Material Listing Found

Notes
Janice
No Material Listing Found
Janice
No Material Listing Found
-------------------------------------
I am not sure why it is doing this, but I do know it is repeating.- Hide quoted text -

- Show quoted text -

I constructed a query that gave me what I wanted if Note is Like
"Material*".
Saved that query as qryMATERIALNOTES

SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.NOTE
FROM dbo_RB_JOBNOTE
WHERE (((dbo_RB_JOBNOTE.NOTE) Like "Materials*"))
ORDER BY dbo_RB_JOBNOTE.JOBID;

--------------------------------
Built a query with dbo_RB_Jobnote where I joined on the JOBID fields.
Saved that query as qryNULL_MATERIALS

SELECT qryMATERIALNOTES.JOBID, qryMATERIALNOTES.NOTE
FROM qryMATERIALNOTES RIGHT JOIN dbo_RB_JOBNOTE ON
qryMATERIALNOTES.JOBID = dbo_RB_JOBNOTE.JOBID;

----------------------------------
I Double-clicked on the join lines of qryNULL_MATERIALS and set them
to show all records only matching in the saved query.

------------------------------------
In my subreport -- subrptMATERIALNOTES
with control source of -- qryNULL_MATERIALS
I created a text box -- txtMaterialNote
with the following control source:
=IIf(IsNull([NOTE]),"No Material listing found",[NOTE])

-------------------------------------

I am getting the "Enter Parameter Value ID" dialog box,
when I click OK... I get repeated values like before...

I am clueless on how to even know where to start fixing this

AUUUGHH! as Charlie Brown says.