• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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
0
blossompark
Asked:
blossompark
  • 4
  • 3
1 Solution
 
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
 
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
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
 
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
 
blossomparkAuthor Commented:
Fantastic DanielWilson!!!!!!!!!!!!!!!!!!!!!!! The weekend starts here!!!!
Thanks that is absolutely brilliant... Made my day bigtime!!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now