Where In operator (1 Viewer)

aymarhoon

Registered User.
Local time
Today, 10:03
Joined
Sep 18, 2018
Messages
10
Hi guys

Is it possible to have specific values and values from a select query in IN*Operator. I mean something like this

SELECT tblStudents.*, Grade
FROM tblStudents
WHERE Grade In ("A","A-","B+","B","B-","C+","C","C-","D+","D","F",

SELECT DISTINCT tblStudents AS Grade
FROM tblStudents
WHERE Grade In ("DIS","E", "W") AND Subject Like "ehs*")

)


I want to generate a crosstab query to have fixed column heading (grades A to F) but dynamic for (DIS, W, E, etc).

Your help is greatly appreciated..

Thanks

Sent from my ANE-LX1 using Tapatalk
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
not sure if I understand your question. It is not very clear. But you can force column headings using column headings in the crosstab query properties. This will make a column even if there is not data. So if there is no students with F the column still exists.
 

aymarhoon

Registered User.
Local time
Today, 10:03
Joined
Sep 18, 2018
Messages
10
Thanks MajP

I want fixed column from A to F followed by dynamic columns based on returned data.. Dynamic columns may include W, Ab, DIS, WF, etc

Sent from my ANE-LX1 using Tapatalk
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:03
Joined
Feb 19, 2002
Messages
42,971
For a crosstab query, either the columns are fixed (included in the column property) or variable. You can't have a combination. What is the point anyway? If even a single row has one of the "variable" values, then ALL rows of the crosstab will include it.
 

June7

AWF VIP
Local time
Today, 09:03
Joined
Mar 9, 2014
Messages
5,423
How many of these 'dynamic' values are there? Post 1 shows only 3 yet Post 3 indicates there are more.

Why did question in other thread use NOT qualifier?
 

aymarhoon

Registered User.
Local time
Today, 10:03
Joined
Sep 18, 2018
Messages
10
I want to use the result of query to generate a chart but I don't want fixed Headings for (W, DIS, WF, etc) after the F to appear on the xaxis.

For instance, the xaxis will show
A - F followed by W only
or A - F followed by W, DIS

I thought it might be possible to write a select query to get values of W, DIS, WF, etc then add to IN operator as I mentioned in post 1

Sent from my ANE-LX1 using Tapatalk
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:03
Joined
Oct 29, 2018
Messages
21,358
I want to use the result of query to generate a chart but I don't want fixed Headings for (W, DIS, WF, etc) after the F to appear on the xaxis.

For instance, the xaxis will show
A - F followed by W only
or A - F followed by W, DIS

I thought it might be possible to write a select query to get values of W, DIS, WF, etc then add to IN operator as I mentioned in post 1

Sent from my ANE-LX1 using Tapatalk

Hi. Maybe it's just me, but I'm a little confused by this question because the In() operator in a WHERE clause does not dictate the fixed column headers.
 

Micron

AWF VIP
Local time
Today, 13:03
Joined
Oct 20, 2018
Messages
3,476
Sorry, missed that cross posting was already pointed out.
 

June7

AWF VIP
Local time
Today, 09:03
Joined
Mar 9, 2014
Messages
5,423
As already pointed out, this won't work. Not even in CROSSTAB pivot clause. CROSSTAB headings are either all dynamic based on data or all explicitly specified.

So how many of these other values are there?
 

aymarhoon

Registered User.
Local time
Today, 10:03
Joined
Sep 18, 2018
Messages
10
Is it possible to make a function to have A-F then we add to it the result coming from a select query?
 

June7

AWF VIP
Local time
Today, 09:03
Joined
Mar 9, 2014
Messages
5,423
Again, CROSSTAB column headers must all be generated from data or explicitly specified with IN() array list and that list cannot be dynamic. So no, cannot be built with a function.

Could use QueryDefs code to modify saved object.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
Could use QueryDefs code to modify saved object.
Yes you could accomplish this that way.
1. First find out which of (W, Ab, DIS, WF,) are in the data set.
2. Modify the query def of the the crosstab and add the column headers which ever values are included.

I think of have an example of doing this somewhere.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:03
Joined
Oct 29, 2018
Messages
21,358
Hi. Maybe it's just me, but I'm a little confused by this question because the In() operator in a WHERE clause does not dictate the fixed column headers.

Oh, I think I get it now. You guys are talking about the Pivot In() and not the Where In() as was shown in the original post.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
This works for me

Code:
Public Function GetAdditionalHeaders()

  Dim rs As DAO.Recordset
  Dim strColumns As String
  'get the non required columns in the data set
  Set rs = CurrentDb.OpenRecordset("qryNotRequired")
  Do While Not rs.EOF
    If strColumns = "" Then
      strColumns = "'" & rs!Grade & "'"
    Else
      strColumns = strColumns & ",'" & rs!Grade & "'"
    End If
    rs.MoveNext
  Loop
  If strColumns <> "" Then strColumns = "," & strColumns
  GetAdditionalHeaders = strColumns
End Function

Private Sub cmdQry_Click()
  Dim Headers As String
  Dim qdf As QueryDef
  Dim strSql As String
  
  Headers = "'A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F'"
  Headers = Headers & GetAdditionalHeaders
  Debug.Print Headers
  Set qdf = CurrentDb.QueryDefs("qryXtabGrades")
  strSql = "TRANSFORM Count(tblGrades.PersonID) AS CountOfPersonID SELECT tblGrades.Class FROM tblGrades "
  strSql = strSql & "WHERE tblGrades.Class = 'EHS' "
  strSql = strSql & "GROUP BY tblGrades.Class PIVOT tblGrades.Grade In (" & Headers & ")"
  qdf.SQL = strSql
  DoCmd.OpenQuery qdf.Name
End Sub

qryNotRequired
Code:
SELECT DISTINCT tblGradeTypes.Grade
FROM tblGradeTypes 
INNER JOIN tblGrades ON tblGradeTypes.Grade = tblGrades.Grade
WHERE tblGradeTypes.Required=False AND tblGrades.Class="EHS";

TblGradeTypess
Code:
ID	Grade	Required	Sort
1	A+	True	1
2	A	True	2
3	A-	True	3
4	B+	True	4
5	B	True	5
6	B-	True	6
7	C+	True	7
8	C	True	8
9	C-	True	9
10	D+	True	10
11	D	True	11
12	D-	True	12
13	F	True	13
14	AB	False	14
15	W	False	15
16	Dis	False	16
 

Attachments

  • Grades.accdb
    908 KB · Views: 104

June7

AWF VIP
Local time
Today, 09:03
Joined
Mar 9, 2014
Messages
5,423
Should also be able to programmatically change RowSource property of graph on form, not sure about report.
 

aymarhoon

Registered User.
Local time
Today, 10:03
Joined
Sep 18, 2018
Messages
10
This works for me

Code:
Public Function GetAdditionalHeaders()

  Dim rs As DAO.Recordset
  Dim strColumns As String
  'get the non required columns in the data set
  Set rs = CurrentDb.OpenRecordset("qryNotRequired")
  Do While Not rs.EOF
    If strColumns = "" Then
      strColumns = "'" & rs!Grade & "'"
    Else
      strColumns = strColumns & ",'" & rs!Grade & "'"
    End If
    rs.MoveNext
  Loop
  If strColumns <> "" Then strColumns = "," & strColumns
  GetAdditionalHeaders = strColumns
End Function

Private Sub cmdQry_Click()
  Dim Headers As String
  Dim qdf As QueryDef
  Dim strSql As String
  
  Headers = "'A+','A','A-','B+','B','B-','C+','C','C-','D+','D','D-','F'"
  Headers = Headers & GetAdditionalHeaders
  Debug.Print Headers
  Set qdf = CurrentDb.QueryDefs("qryXtabGrades")
  strSql = "TRANSFORM Count(tblGrades.PersonID) AS CountOfPersonID SELECT tblGrades.Class FROM tblGrades "
  strSql = strSql & "WHERE tblGrades.Class = 'EHS' "
  strSql = strSql & "GROUP BY tblGrades.Class PIVOT tblGrades.Grade In (" & Headers & ")"
  qdf.SQL = strSql
  DoCmd.OpenQuery qdf.Name
End Sub

qryNotRequired
Code:
SELECT DISTINCT tblGradeTypes.Grade
FROM tblGradeTypes 
INNER JOIN tblGrades ON tblGradeTypes.Grade = tblGrades.Grade
WHERE tblGradeTypes.Required=False AND tblGrades.Class="EHS";

TblGradeTypess
Code:
ID	Grade	Required	Sort
1	A+	True	1
2	A	True	2
3	A-	True	3
4	B+	True	4
5	B	True	5
6	B-	True	6
7	C+	True	7
8	C	True	8
9	C-	True	9
10	D+	True	10
11	D	True	11
12	D-	True	12
13	F	True	13
14	AB	False	14
15	W	False	15
16	Dis	False	16
this is really awesome.. Thank you a million times for your help.. You really made things look simple.

Thanks again...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
Hopefully you can incorporate this idea into your application. Maybe someone has a simpler approach, but I could not think of one.
 

aymarhoon

Registered User.
Local time
Today, 10:03
Joined
Sep 18, 2018
Messages
10
How can I chart the result of the crosstab query? I mean from the Form the command button opens a report showing a chart instead of the crosstab query..

Sent from my ANE-LX1 using Tapatalk
 
Last edited:

Users who are viewing this thread

Top Bottom