SELECT DISTINCT Query

[CoachPhil]

Hello all

I have a DB with
1-table = FeedbackSession
2 columns- 'Certified Listeners Name' and 'Did the CL give Feedback in a Timely Manner'
I have five individuals listed in this table as Certified Listeners, I am trying to determine their average score from a second column. I cannot get SELECT DISTINCT to work in the query.

"SELECT DISTINCT [Certified Listeners Name].FeedbackSession FROM FeedbackSession WHERE [Did the CL give Feedback in a Timely Manner].FeedbackSession LIKE [Certified Listeners Name].FeedbackSession"

any ideas?

CoachPhil

 

[raskew]

Hi -

You have the table name and field name reversed. Table name comes first, then the field name, e.g.

[Certified Listeners Name].FeedbackSession should read
FeedbackSession.[Certified Listeners Name]

HTH - Bob

 

[CoachPhil]

Select Distinct

Thanks for the assist Bob, when I change it to this;

"SELECT DISTINCT FeedbackSession.[Certified Listeners Name] FROM FeedbackSession WHERE FeedbackSession.[Did the CL give Feedback in a Timely Manner] LIKEFeedbackSession. [Certified Listeners Name]"

it still returns nothing. What could I be missing?

CoachPhil

 

[raskew]

Hi -

Several observations:
1) Your field names are ungainly. They aren't meant to describe an entire scenario, rather just enough to identify the field--preferably without spaces since this is what requires us to enclose the field name in brackets.

- Instead of [Certified Listeners Name] try strListener. The str prefix identifies the data type (string) of the field.
- Instead of [Did the CL give Feedback in a Timely Manner] try ListenerResponse prefixed with an abreviation indicating the field's data type.

2) Can't tell the data type of ListenerResponse. This is particularly relevant since you've stated ...I am trying to determine their average score . The field name would indicate a yes/no (boolean) response. Since you want an average, that indicates that each listener will have several responses. Please advise.

3) Rather than try to build this from scratch, suggest you start with the query builder. Indicate New Query, pull down table FeedbackSession and include both fields in your query. Then, depending on the answers to 2) above, we can go on from there.

Bob

 

[CoachPhil]

Sql

Bob,

I am trying to populate the following report, thus the ungainly names, for ease of use, believe it or not.
I will try

 

[CoachPhil]

I will try to adjust as many names as possible, good suggestion, thank you

CoachPhil

 

[raskew]

OK, great.

Your report is FeedbackSessionReportByDate, but the table you've described doesn't include a session date. How does that work?

The report is displaying scores (1 to 6) for multiple topics. How are you capturing this information and what do the numbers represent?

If there are additional fields that haven't been mentioned, please describe.

Bob

 

[georgedwilkinson]

Hello again, coach. Hope things are going well.

Didn't want to butt in but I noticed you're using "LIKE" to compare two fields.

You'll probably want to use "=" instead of like in this case.

Stick with Bob's suggestions. There is kinda a lazy art to naming object names. Make the names short enough to type quickly but long enough to have meaning in context.

Also, you'll avoid a lot of problems with SQL syntax by using the visual query design tool to prototype your queries. That'll avoid things like getting the table/column order wrong and misspelling names. It also does a moderately decent syntax check.

 

[CoachPhil]

Sql Select

Bob,

Sorry for the ommission, I was trying to simplify the question. The fields accept the answers from a questionnaire, there are six questions, where the person who completes the form rates the user on a scale of 1 to 5, then can offer additional comments on the rating.

1 table - FeedbackSession

16 columns-
ID
Date
strListener
longTimelyManner
strComments
longComfortableObservation
strComments1
longComfortableSession
strComments2
longPerformanceAdjustments
strComments3
longProfessionalManner
strComments4
longRedirectingAction
strComments5
longImproveSession

I then have three Reports,
1 to include ratings and comments
1 to include ratings only
1 to include comments only

Hope this clarifies my situation

CoachPhil

 

[CoachPhil]

Thanks again George