|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
May 3rd, 2007, 02:32 AM
posted to microsoft.public.access.reports
|
|||
|
|||
One Result from multiple memo fields
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 
|
|
#2
May 3rd, 2007, 04:23 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
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" wrote: 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
|
|
#3
May 3rd, 2007, 06:45 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
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 .. "Sunflower" wrote in message oups.com... 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
|
|
#4
May 4th, 2007, 03:29 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
On May 3, 10:45 am, "John Spencer" wrote:
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 . "Sunflower" wrote in message oups.com... 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 
|
|
#5
May 4th, 2007, 04:57 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
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" wrote in message ups.com... On May 3, 10:45 am, "John Spencer" wrote: 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 . "Sunflower" wrote in message oups.com... 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
|
|
#6
May 4th, 2007, 07:24 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
On May 4, 8:57 am, "John Spencer" wrote:
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" wrote in message ups.com... On May 3, 10:45 am, "John Spencer" wrote: 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 . "Sunflower" wrote in message groups.com... 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 - 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.
|
|
#7
May 6th, 2007, 04:00 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
On May 3, 8:23 am, NetworkTrade
wrote: 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" wrote: 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 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 
|
|
#8
May 6th, 2007, 03:24 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
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 '================================================= === Sunflower wrote: On May 4, 8:57 am, "John Spencer" wrote: 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" wrote in message ups.com... On May 3, 10:45 am, "John Spencer" wrote: 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 . "Sunflower" wrote in message oups.com... 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 - 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.
|
|
#9
May 7th, 2007, 04:45 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
On May 6, 7:24 am, John Spencer wrote:
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 '================================================= === Sunflower wrote: On May 4, 8:57 am, "John Spencer" wrote: 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" wrote in message roups.com... On May 3, 10:45 am, "John Spencer" wrote: 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 . "Sunflower" wrote in message legroups.com... 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 - 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.- Hide quoted text - - Show quoted text - 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.
|
|
#10
May 7th, 2007, 08:30 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
One Result from multiple memo fields
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.
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
