How di I modify a sql query in Access?

Hi,
I have a query created in Access 2000 called qsel_SurveyDetails.
which returns a table. (see attachment)
I want to replace the ObjectRef column
With a column called  ref_num from another table called call_req.
When I add the new table in the Design View and then run the query it returns no data.
How can I replace the ObjectRef column with the call_req.ref_num column using the Design view or other method?
Thanks


.......OK to summarize the question.
How can I alter the code below to replacethe ObjectRef column
with the ref_num column from the call_req table........
 
SELECT survey.id, survey.sym AS SurveyName, CvrtFromUnixTime([survey]![last_mod_dt]) AS LastModDate, IIf(IsNull([c_first_name]),[c_last_name],[c_last_name] & ", " & [c_first_name]) AS LastModBy, survey.description, survey.comment_label, survey.nx_comment, [survey]![object_type]+":"+CStr([survey]![object_id]) AS ObjectRef, OBTypeToWord([object_type]) AS OBType, survey_question.sequence, survey_question.id, survey_question.txt, survey_answer.selected, IIf([survey_answer].[selected],1,0) AS CountOfAnswer, IIf(([response]=0 And [mult_resp_flag]=0),1,0) AS CountOfBlankAns, IIf([survey_answer].[selected],"*","") AS ChoiceSign, survey_answer.sequence, survey_answer.id, survey_answer.txt, CDate([Forms]![Report Selector]![StartDate]) AS FromDate, CDate([Forms]![Report Selector]![EndDate]) AS ToDate, survey_question.qcomment_label, survey_question.qcomment
FROM (survey INNER JOIN (survey_question INNER JOIN survey_answer ON survey_question.id = survey_answer.own_srvy_question) ON survey.id = survey_question.owning_survey) INNER JOIN ctct ON survey.last_mod_by = ctct.id
WHERE (((survey.sym) Like [Forms]![Report Selector]![cboSurvey]) AND ((survey.last_mod_dt)>=[Forms]![Report Selector]![txtUnixStart] And (survey.last_mod_dt)<=[Forms]![Report Selector]![txtUnixEnd]))
ORDER BY survey.id, survey.sym, survey_question.sequence, survey_answer.sequence

Open in new window

AccessReport-incident-numberV2.doc
blossomparkAsked:
Who is Participating?
 
Daniel WilsonCommented:
Well, Access is always big on parentheses.  Let's try this:


SELECT survey.id, survey.sym AS SurveyName, CvrtFromUnixTime([survey]![last_mod_dt]) AS LastModDate, 
        IIf(IsNull([c_first_name]),[c_last_name],[c_last_name] & ", " & [c_first_name]) AS LastModBy, 
        survey.description, survey.comment_label, survey.nx_comment, 
        [survey]![object_type]+":"+CStr([survey]![object_id]) AS ObjectRef, 
        OBTypeToWord([object_type]) AS OBType, survey_question.sequence, survey_question.id, survey_question.txt, survey_answer.selected, 
        IIf([survey_answer].[selected],1,0) AS CountOfAnswer, IIf(([response]=0 And [mult_resp_flag]=0),1,0) AS CountOfBlankAns, 
        IIf([survey_answer].[selected],"*","") AS ChoiceSign, survey_answer.sequence, survey_answer.id, survey_answer.txt, 
        CDate([Forms]![Report Selector]![StartDate]) AS FromDate, CDate([Forms]![Report Selector]![EndDate]) AS ToDate, 
        survey_question.qcomment_label, survey_question.qcomment,
        Call_req.Ref_Num
FROM ((survey INNER JOIN 
        (survey_question INNER JOIN survey_answer ON survey_question.id = survey_answer.own_srvy_question) ON survey.id = survey_question.owning_survey) INNER JOIN 
        ctct ON survey.last_mod_by = ctct.id) Left OUTER Join call_req on survey.object_id = call_req.id
WHERE (((survey.sym) Like [Forms]![Report Selector]![cboSurvey]) AND ((survey.last_mod_dt)>=[Forms]![Report Selector]![txtUnixStart] And (survey.last_mod_dt)<=[Forms]![Report Selector]![txtUnixEnd]))
ORDER BY survey.id, survey.sym, survey_question.sequence, survey_answer.sequence

Open in new window

0
 
Daniel WilsonCommented:
what field(s) relate survey (or other tables listed) to ObjectRef?

Knowing that, I can write you a query.
0
 
UnifiedISCommented:
You need to join the call_req table.  I didn't see from your screenshots how you tried to join call_req but you might have joined it by the wrong columns.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Daniel WilsonCommented:
Yeah, the call_req table -- that's the one I meant.  What field in it relates to a field in Survey or one of the other tables you already have?
0
 
blossomparkAuthor Commented:
Hi DanielWilson
 The ahd.survey.object_id in the survey table. references the ahd.call_req.id. The ahd.call_req table contains the ref_num column.

ahd is the name of the database on the underlying sql server 2000
0
 
Daniel WilsonCommented:
OK, I've joined in call_req on a LEFT JOIN so that missing call_req records won't eliminate Survey records.  That may have been the problem w/ the join the designer created for you.  If you want to get rid of the ObjectRef field, just delete it from the query.

 
SELECT survey.id, survey.sym AS SurveyName, CvrtFromUnixTime([survey]![last_mod_dt]) AS LastModDate, 
	IIf(IsNull([c_first_name]),[c_last_name],[c_last_name] & ", " & [c_first_name]) AS LastModBy, 
	survey.description, survey.comment_label, survey.nx_comment, 
	[survey]![object_type]+":"+CStr([survey]![object_id]) AS ObjectRef, 
	OBTypeToWord([object_type]) AS OBType, survey_question.sequence, survey_question.id, survey_question.txt, survey_answer.selected, 
	IIf([survey_answer].[selected],1,0) AS CountOfAnswer, IIf(([response]=0 And [mult_resp_flag]=0),1,0) AS CountOfBlankAns, 
	IIf([survey_answer].[selected],"*","") AS ChoiceSign, survey_answer.sequence, survey_answer.id, survey_answer.txt, 
	CDate([Forms]![Report Selector]![StartDate]) AS FromDate, CDate([Forms]![Report Selector]![EndDate]) AS ToDate, 
	survey_question.qcomment_label, survey_question.qcomment,
	Call_req.Ref_Num
FROM (survey INNER JOIN 
	(survey_question INNER JOIN survey_answer ON survey_question.id = survey_answer.own_srvy_question) ON survey.id = survey_question.owning_survey) INNER JOIN 
	ctct ON survey.last_mod_by = ctct.id Left Join call_req on survey.object_id = call_req.id
WHERE (((survey.sym) Like [Forms]![Report Selector]![cboSurvey]) AND ((survey.last_mod_dt)>=[Forms]![Report Selector]![txtUnixStart] And (survey.last_mod_dt)<=[Forms]![Report Selector]![txtUnixEnd]))
ORDER BY survey.id, survey.sym, survey_question.sequence, survey_answer.sequence

Open in new window

0
 
blossomparkAuthor Commented:
Hi DanielWilson
ran the above and got;
syntax error missing operator in query expression
"survey.last_mod_by = ctct.id Left Join call_req on survey.object_id = call_req.id"
0
 
blossomparkAuthor Commented:
Fantastic DanielWilson!!!!!!!!!!!!!!!!!!!!!!! The weekend starts here!!!!
Thanks that is absolutely brilliant... Made my day bigtime!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.