C
CrazyJim
First, the user limits his search. And is given a result:
------------------------------------------
Me!Sub.Form.RecordSource = strNewSQL
------------------------------------------
Thats easy. The information displayed on the form is a
straight SQL statement created by the user to limit his
search.
I want to then be able to search that SQL result, and
create a crosstab:
Rows- Year of Graduation
Columns- Major
Cells- Total number of students in that year/major.
I was able to successfully solve this problem with 2
seperate queries:
-----------------------------------------
Query 1, Tallies the departments, and secondary
departments.
SELECT [dept], [class], IIf([dept]="BA",1,0) AS BA, IIf
([dept]="CS",1,0) AS CS, IIf([dept]="BSA",1,0) AS BSA, IIf
([dept]="SHS",1,0) AS SHS
FROM [QSIS DATA]
UNION ALL SELECT [dept], [class], IIf([dept2]="BA",1,0) AS
BA, IIf([dept2]="CS",1,0) AS CS, IIf([dept2]="BSA",1,0) AS
BSA, IIf([dept2]="SHS",1,0) AS SHS
FROM [QSIS DATA];
---------------------------------------------
Query 2: Adds all the departments up.
SELECT [class], sum([BA]) AS Buisness_Administration, sum
([CS]) AS Computer_Science, sum([BSA]) AS _BSA, sum([SHS])
AS _SHS
FROM query1
WHERE class>0 And class<6
GROUP BY [class]
ORDER BY [class];
------------------------------------------------
The problem was that these two queries only search on the
raw table data, and not a narrowed search version of the
table.
There are several ways this could be solved. Two things I
can think of are:
1: Is there some way I can use a visual basic form's SQL
result as a data source? If so, I could then query the
original search result. Then I could use that search
result as the data source to run my SQL tally. Then I
could use the SQL tally's results as a data source to run
the SQL summing.
2: I tried merging all three SQL statements using WHERE
IN. It seems access SQL can not handle the UNION ALL
statement nested.
Any help is appreciated. I'm new to visual basic and
access.
------------------------------------------
Me!Sub.Form.RecordSource = strNewSQL
------------------------------------------
Thats easy. The information displayed on the form is a
straight SQL statement created by the user to limit his
search.
I want to then be able to search that SQL result, and
create a crosstab:
Rows- Year of Graduation
Columns- Major
Cells- Total number of students in that year/major.
I was able to successfully solve this problem with 2
seperate queries:
-----------------------------------------
Query 1, Tallies the departments, and secondary
departments.
SELECT [dept], [class], IIf([dept]="BA",1,0) AS BA, IIf
([dept]="CS",1,0) AS CS, IIf([dept]="BSA",1,0) AS BSA, IIf
([dept]="SHS",1,0) AS SHS
FROM [QSIS DATA]
UNION ALL SELECT [dept], [class], IIf([dept2]="BA",1,0) AS
BA, IIf([dept2]="CS",1,0) AS CS, IIf([dept2]="BSA",1,0) AS
BSA, IIf([dept2]="SHS",1,0) AS SHS
FROM [QSIS DATA];
---------------------------------------------
Query 2: Adds all the departments up.
SELECT [class], sum([BA]) AS Buisness_Administration, sum
([CS]) AS Computer_Science, sum([BSA]) AS _BSA, sum([SHS])
AS _SHS
FROM query1
WHERE class>0 And class<6
GROUP BY [class]
ORDER BY [class];
------------------------------------------------
The problem was that these two queries only search on the
raw table data, and not a narrowed search version of the
table.
There are several ways this could be solved. Two things I
can think of are:
1: Is there some way I can use a visual basic form's SQL
result as a data source? If so, I could then query the
original search result. Then I could use that search
result as the data source to run my SQL tally. Then I
could use the SQL tally's results as a data source to run
the SQL summing.
2: I tried merging all three SQL statements using WHERE
IN. It seems access SQL can not handle the UNION ALL
statement nested.
Any help is appreciated. I'm new to visual basic and
access.