Hi everyone,
I can't seem to get this query right. It seems that crosstab queries don't support subqueries or is it? How should i rewrite this? It's too nasty to expand the whole query in four statements and union them together... -
SELECT t1.companyname AS Supplier, t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName AS Category, t1.Score
-
FROM (
-
-
SELECT s2.companyname, q.id as questionid, IIf((select count(*) from assignedquestions as aq where aq.assignedto=[MemberID] and aq.questionid=q.id )>0,"X","") AS Assigned, q.questionnumber as Question, q.questionweight as Weight, q.category as Category,
-
-
(select score from score as s where s.teammemberid=[MemberID] and s.supplierid=s2.id and s.questionid=q.id) as Score FROM Question AS q, supplier AS s2
-
GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname
-
-
) AS t1 LEFT JOIN Category AS c ON t1.Category = c.id;
-
-
4 3331
Hi -
One thing you might want to check is what you are selecting in the first subquery versus what's in the GROUP BY clause. When you're grouping, whatever you are SELECTing has to appear in GROUP BY. In your case, the correlated subqueries that you are aliasing as 'Assigned' and 'Score' don't appear in the grouping.
It seems like you might need to do the grouping first, then join the result to 'Assigned' and 'Score' to get what you want.
Pat
Thanks for your reply Zepphead80.
However, I tried grouping them in the outer query (you cant do this in the inner query because of the aliases). This still gives the following error message: "Database engine does not recognize q.id as a valid field name or expression"
This is the adjusted query: -
PARAMETERS MemberID Short;
-
TRANSFORM Sum(t1.Score) AS SumOfScore
-
SELECT t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName AS Category
-
FROM (SELECT s2.companyname, q.id as questionid, IIf((select count(*) from assignedquestions as aq where aq.assignedto=[MemberID] and aq.questionid=q.id )>0,"X","") AS Assigned, q.questionnumber as Question, q.questionweight as Weight, q.category as Category,
-
-
(select score from score as s where s.teammemberid=[MemberID] and s.supplierid=s2.id and s.questionid=q.id) as Score FROM Question AS q, supplier AS s2
-
GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname
-
-
) AS t1 LEFT JOIN Category AS c ON t1.Category = c.id
-
GROUP BY t1.questionid, t1.Assigned, t1.Question, t1.Weight, c.CategoryName
-
PIVOT t1.companyname;
-
Do you know what I am doing wrong? It seems that you cant refer to fields outside the subquery in a crosstab, am i correct?
christian -
I have a deadline today and won't be able to look at your query for a bit, but I will take a look at it in the next day or so. If you should happen to figure out the problem before then, just drop a line here to let us know what it was.
Pat
If you try to run the main subquery by itself (notice that I have included 'Assigned' and 'Score' in the GROUP BY clause), what happens? - SELECT s2.companyname,
-
q.id AS questionid,
-
IIf((SELECT COUNT(*) FROM assignedquestions AS aq WHERE aq.assignedto=[MemberID] AND aq.questionid=q.id)>0,"X","") AS Assigned,
-
q.questionnumber AS Question,
-
q.questionweight AS Weight,
-
q.category AS Category
-
(SELECT score FROM score AS s WHERE s.teammemberid=[MemberID] AND s.supplierid=s2.id AND s.questionid=q.id) AS Score
-
FROM Question AS q, supplier AS s2
-
GROUP BY q.id, q.questionnumber, q.questionweight, q.category, s2.id, s2.companyname, Score, Assigned
Sign in to post your reply or Sign up for a free account.
Similar topics
by: John |
last post by:
I've read several prior posts in this group about using nz() to
convert null values to zero; however, I'm not sure how/where to
implement this function in my crosstab query.
The crosstab query...
|
by: Darleen |
last post by:
I am seeking conceptual here on how to get started with a "3D Matrix"
in Access. We run a training center which holds multiple classes in
multiple cities at multiple times. So I need to create a...
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to...
|
by: Sherman H. |
last post by:
I have a few questions for crosstab and popup form questions:
1. I created a crosstab as follows:
IT Financial Operation
John 21 22 ...
|
by: newguy |
last post by:
I am trying to get the totals of a table by client by type of income.
This query will get what I am looking for with each unique combination
as a row:
SELECT Sales.Client, BillCode.Type,...
|
by: Penny |
last post by:
(Access 2003 Multiuser Split DB, Windows XP Pro)
Hi All,
I would really appreciate just some basic tips on how to make a Crosstab
Form based on a Crosstab Query. The query always has the same...
|
by: Tina |
last post by:
My employer tracks productivity/performance of clinicians (how much
they
bill) each week, its averages for the month, and the 6 months.
These averages are compared to their expected productivity....
|
by: jason.teen |
last post by:
Hi All,
I am having trouble creating a crosstab query.
In my original data I have two columns, One called "Categorized" and
one called "Mapped'
in which those columns can hold values of "true"...
|
by: salad |
last post by:
Operating in A97.
I didn't receive much of a response conserning Pivot tables in Access.
Pivot tables are nice, but a CrossTab will work for me too.
Using a Pivot table, one is actually...
|
by: m.wanstall |
last post by:
I have a crosstab query that compiles data for Months of the year. I
have a stacked select query on top of that crosstab query that uses
the latest 2 months data and exports it to a fixed length...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |