One Result from multiple memo fields

S

Sunflower

I am an Access newbie, so I am not even sure this can be done...

I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...

My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;

The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")

What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------


I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
 
G

Guest

since no one has replied - I will give one suggestion; consider splitting
your task in two....

first I would consider establishing the results you want using a query -
without attempting to insert the default phrase.....

with the correct query then, if I understand you correctly, you will have a
set of records and some will have info in the [Memo] field and some will have
blank fields in the [Memo] field. (it will probably require an outer join in
order to return blank fields or you can rely on the query design that will
return all records somehow....and you don't have to use all fields in your
report)

this would be the first step.

Create your report sourced on this query...... once that looks right (except
for the blanks in some)

and then the second step would be to add to the Report's "OnFormat" event
the VBA that IF the Memo Field is Null (or maybe [Memo]="" ) THEN [Memo]="No
Material listing found"

and that phrase will appear where the blanks were....
probably need to experiment with correct VB as I'm not sure what a blank is
for a memo field property in terms of it being Null or just nothing "" - but
in any case I think this would work....
 
J

John Spencer

Can I assume that the following solution did not work? This solution was
posted on April 24th in response to your earlier posting. Since I did not
see any response that it failed or that you could not implement it, my guess
was that it worked for you.

SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;

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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

Sunflower

Can I assume that the following solution did not work? This solution was
posted on April 24th in response to your earlier posting. Since I did not
see any response that it failed or that you could not implement it, my guess
was that it worked for you.

SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;

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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




I am an Access newbie, so I am not even sure this can be done...
Click to expand...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
Click to expand...
My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;
Click to expand...
The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
Click to expand...
What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------
Click to expand...
I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
-----------------
Click to expand...
All and any help greatly appreciated- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I am very sorry...
I thought I had reponded to you and let you know that implemented and
failed
I just figured my question was dead, so I reposted.

I tried your new one
---------------------------------------------------
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
-----------------------------------------------------
and I get a "Syntax Error in FROM clause" error
I do not know enough about SQL to understand where the problem may be.

Again I aplogize for following up with you.

Thank you for putting up with such a newbie :)
 
J

John Spencer

My fault I left off the join criteria

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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T

ON dbo_RB_JobNOTE.JobID = T.ID


If that fails we can try to do this in two steps. Step one would build a
query that returns all the records where the note contains the word Material
at the start of the note. With that query as a saved query. After that you
would build a second query using your table and the saved query to get what
you need.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Sunflower said:
Can I assume that the following solution did not work? This solution was
posted on April 24th in response to your earlier posting. Since I did
not
see any response that it failed or that you could not implement it, my
guess
was that it worked for you.

SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;

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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




I am an Access newbie, so I am not even sure this can be done...
Click to expand...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
Click to expand...
My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;
Click to expand...
The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
Click to expand...
What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------
Click to expand...
I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
-----------------
Click to expand...
All and any help greatly appreciated- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I am very sorry...
I thought I had reponded to you and let you know that implemented and
failed
I just figured my question was dead, so I reposted.

I tried your new one
---------------------------------------------------
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
-----------------------------------------------------
and I get a "Syntax Error in FROM clause" error
I do not know enough about SQL to understand where the problem may be.

Again I aplogize for following up with you.

Thank you for putting up with such a newbie :)
Click to expand...
 
S

Sunflower

My fault I left off the join criteria

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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T

ON dbo_RB_JobNOTE.JobID = T.ID

If that fails we can try to do this in two steps. Step one would build a
query that returns all the records where the note contains the word Material
at the start of the note. With that query as a saved query. After that you
would build a second query using your table and the saved query to get what
you need.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




Can I assume that the following solution did not work? This solution was
posted on April 24th in response to your earlier posting. Since I did
not
see any response that it failed or that you could not implement it, my
guess
was that it worked for you.
SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

I am an Access newbie, so I am not even sure this can be done...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;
The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------
I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
Click to expand...
Click to expand...
I am very sorry...
I thought I had reponded to you and let you know that implemented and
failed
I just figured my question was dead, so I reposted.
Click to expand...
I tried your new one
---------------------------------------------------
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
Click to expand...
Again I aplogize for following up with you.
Click to expand...
Thank you for putting up with such a newbie :)- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I put in your revised SQL...
I also replaced my Materials text box with "MaterialNote"
Now I get the dialog box "Enter Parameter Value - ID"

Is this where I try the 2 queries?

Thanks again for helping me.
 
S

Sunflower

since no one has replied - I will give one suggestion; consider splitting
your task in two....

first I would consider establishing the results you want using a query -
without attempting to insert the default phrase.....

with the correct query then, if I understand you correctly, you will have a
set of records and some will have info in the [Memo] field and some will have
blank fields in the [Memo] field. (it will probably require an outer join in
order to return blank fields or you can rely on the query design that will
return all records somehow....and you don't have to use all fields in your
report)

this would be the first step.

Create your report sourced on this query...... once that looks right (except
for the blanks in some)

and then the second step would be to add to the Report's "OnFormat" event
the VBA that IF the Memo Field is Null (or maybe [Memo]="" ) THEN [Memo]="No
Material listing found"

and that phrase will appear where the blanks were....
probably need to experiment with correct VB as I'm not sure what a blank is
for a memo field property in terms of it being Null or just nothing "" - but
in any case I think this would work....

--
NTC



Sunflower said:
I am an Access newbie, so I am not even sure this can be done...
Click to expand...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
Click to expand...
My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;
Click to expand...
The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
Click to expand...
What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------
Click to expand...
I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
-----------------
Click to expand...
All and any help greatly appreciated- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I tried to figure out your suggestion with the VBA,
unfortunately I am very new to Access, so trying to code with VBA is a
little (maybe alot) over my head.

Also, I do not want all the memos linked to a job listed in my
report,
I only want the memos that are prefaced with "Material".
If all the memos linked to a job do not have a memo prefaced with
"Material"...
then I want the text "No Material listing found" returned.
However, I do not want "No Material listing found" repeated for
every memo linked to a job.

I hope that made since :(
 
J

John Spencer

AUUUGHH! as Charlie Brown says.

The message meant that the query did not recognize a field named ID and
wanted you to tell it what value to use for ID. Obviously I should have
replace ID with JOBID when I was putting together the sample SQL
statement.

Please accept my apology and try the following query.

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
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
ON dbo_RB_JobNOTE.JobID = T.JobID


If I've messed up any other field names, please fix them also.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

My fault I left off the join criteria

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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T

ON dbo_RB_JobNOTE.JobID = T.ID

If that fails we can try to do this in two steps. Step one would build a
query that returns all the records where the note contains the word Material
at the start of the note. With that query as a saved query. After that you
would build a second query using your table and the saved query to get what
you need.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.




Can I assume that the following solution did not work? This solution was
posted on April 24th in response to your earlier posting. Since I did
not
see any response that it failed or that you could not implement it, my
guess
was that it worked for you.
SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
I am an Access newbie, so I am not even sure this can be done...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;
The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------
I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
-----------------
All and any help greatly appreciated- Hide quoted text -
- Show quoted text -
I am very sorry...
I thought I had reponded to you and let you know that implemented and
failed
I just figured my question was dead, so I reposted.
I tried your new one
---------------------------------------------------
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
-----------------------------------------------------
and I get a "Syntax Error in FROM clause" error
I do not know enough about SQL to understand where the problem may be.
Again I aplogize for following up with you.
Thank you for putting up with such a newbie :)- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

I put in your revised SQL...
I also replaced my Materials text box with "MaterialNote"
Now I get the dialog box "Enter Parameter Value - ID"

Is this where I try the 2 queries?

Thanks again for helping me.
Click to expand...
 
S

Sunflower

AUUUGHH! as Charlie Brown says.

The message meant that the query did not recognize a field named ID and
wanted you to tell it what value to use for ID. Obviously I should have
replace ID with JOBID when I was putting together the sample SQL
statement.

Please accept my apology and try the following query.

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
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
ON dbo_RB_JobNOTE.JobID = T.JobID

If I've messed up any other field names, please fix them also.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


My fault I left off the join criteria
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
ON dbo_RB_JobNOTE.JobID = T.ID
If that fails we can try to do this in two steps. Step one would build a
query that returns all the records where the note contains the word Material
at the start of the note. With that query as a saved query. After that you
would build a second query using your table and the saved query to get what
you need.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Can I assume that the following solution did not work? This solution was
posted on April 24th in response to your earlier posting. Since I did
not
see any response that it failed or that you could not implement it, my
guess
was that it worked for you.
SELECT tblJob.Job
, NZ(T.Notes,"No Material Listing Found") as TheNote
FROM tblJob LEFT JOIN
(SELECT JobID, Notes
FROM tblNotes
WHERE Notes Like "Material*") as T
ON tblJob.ID=T.JOBID;
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.
I am an Access newbie, so I am not even sure this can be done...
I need a report to pull material listing from a memo field [Notes]
or show which jobs have missing materials...
My report has the following record source:
SELECT dbo_RB_JOBNOTE.JOBID, dbo_RB_JOBNOTE.ID, dbo_RB_JOBNOTE.NOTE,
dbo_RB_JOBNOTE.OWNERFULLNAME
FROM dbo_RB_JOBNOTE;
The report has a Group header on JOBID
and a [Material] text field with the following control source:
=IIf([NOTE] Like "Material*",[NOTE],"No Material listing found")
What I get is a listing of all the notes,
-----------------
Example:
Job1
No Material listing found
Material note2
No Material listing found
Job2
No Material listing found
No Material listing found
Material note1
Job3
No Material listing found
No Material listing found
No Material listing found
Job4
Material note1
Material note2
No Material listing found
-----------------
I only want the notes if materials are listed
or the text "No Material listing found" if not,
----------------
Example:
Job1
Material note2
Job2
Material note1
Job3
No Material listing found
Job4
Material note1
Material note2
-----------------
All and any help greatly appreciated- Hide quoted text -
- Show quoted text -
I am very sorry...
I thought I had reponded to you and let you know that implemented and
failed
I just figured my question was dead, so I reposted.
I tried your new one
---------------------------------------------------
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 ID, NOTE
FROM dbo_RB_JOBNOTE
WHERE NOTE Like "Material*" ) as T
-----------------------------------------------------
and I get a "Syntax Error in FROM clause" error
I do not know enough about SQL to understand where the problem may be.
Again I aplogize for following up with you.
Thank you for putting up with such a newbie :)- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
I put in your revised SQL...
I also replaced my Materials text box with "MaterialNote"
Now I get the dialog box "Enter Parameter Value - ID"
Click to expand...
Is this where I try the 2 queries?
Click to expand...
Thanks again for helping me.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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
 
J

John Spencer

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
..
 
S

Sunflower

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
.










- Show quoted text -
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookUp for Memo field 1
Report alignment problem 6
Query not working as expected 4
Consolidating text from multiple rows 1
Help with sql query 2
Changing value of text box populated from query 3
Retrieve Info from two tables 2
Making Scanner Profiles from IT8 Targets, Coloraid vs. Colour Science 8

Top