Please help with a Query

[Guest]

I have a table containing student # and the projects that they did. Records
are created one project per record. For example, if a student A did 4
projects, I would have 4 records, instead of one record with project
information across the record. Project information contain project code
(e.g. P123) and project description (e.g. 123 Project).

Is it possible to create a query where I can list the project information
across? For example, if a student has 3 projects:

Column A: Student #
Column B: Project Code 1
Column C: Project Description 1
Column D: Project Code 2
Column E: Project Description 2
Column F: Project Code 3
Column G: Project Description 3

I tried to use Cross-tab query, and it didn't seem to work.

Thanks.

 

[Michel Walsh]

You can, but you need to rank the projects.

Make a query like:

SELECT a.studentID, a.ProjectCode, COUNT(*) as rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.studentID=b.studentID AND a.projectCode >= b.projectCode
GROUP BY a.studentID, a.ProjectCode


Save it, as, say, qu1. Then, use a crosstab on qu1.


TRANSFORM LAST(ProjectCode)
SELECT studentID
FROM qu1
GROUP BY studentID
PIVOT rank

Sure, you can use the projectDescription rather than the projectCode (though
that seems to denote a problem of normalization of your data).

Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel,

Thanks for the SQL code. I am a little confuse.

In your code, you have a.studentID, b.studentID, a.projectCode and
b.projectCode. Does it mean there are 2 tables (Tables a and b) with the
same information? If it is 2 tables, do I need to clone it to use your code?
Currently, I have only one table containing those information.

Thanks.

 

[Guest]

Hi Michel,

Thanks again for the sql code. I got it working.

First I created two identical Select queries from the table. Then I created
the third query using the two queries per your instructions on your first
query below. Then I created the fourth one (cross-tab) per your instructions
below. I ended up creating 4 queries, and the results came out the way I
wanted. I don't know my approach was correct, but it came out right. If you
have a better way, please share with me.

I don't know how you figured out to make the query to rank. You are good.

Thanks again.

 

[Michel Walsh]

No. a and b are references, pointer, to the same table, such as two fingers
can point at different line but of a SINGLE list (table). SO, you don't need
to create TWO lists (two tables), the SQL code just creates two 'fingers' to
your one single table 'tableName'. The ON clause is like a condition that
must be respected by the two fingers. If finger 'a' is sitting on a
particular studentID, ProjectCode (as per the GROUP BY implies), the finger
'b' can (must) be positioned ON each and every record, of your list, that
satisfies the ON clause, but we are just interested by how many of such
records, for finger b, are accessible to it ( since we only pick the
COUNT(*) of these records )


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel,

Thanks for taking the time to explain to me. I have learned a lot from you
and from this experience.

Where do you think I can learn all those tricks? Is there a book?

Thanks.

 

[Michel Walsh]

I have learned it starting with the Access query designer, some books (now
out of print) and from newsgroup (the entire world has much more kind of
problems you would ever have to face, so, 'studying' these problems put you
on some kind of highway, some kind of accelerated track, of knowledge, and
opening your eyes to possibilities of what can be done).

Outside the newsgroup, not everyone react the same way to the same 'inputs',
but you can also check books like Access Developers' Handbook, at Sybex, for
a basic introduction
(http://www.amazon.com/Access-2000-D...=sr_1_1?ie=UTF8&s=books&qid=1195659466&sr=1-1)

and more general stuff like books by John L. Viescas (
http://www.amazon.com/SQL-Queries-M...d_bbs_1?ie=UTF8&s=books&qid=1195659030&sr=1-1 )
, You can also take a look at Joe Celko's books, but those are for advanced
stuff, and not necessary going well with Jet-syntax.

and also, some 'papers' or articles in the Microsoft Knowledge Database
(such as
http://office.microsoft.com/en-us/access/results.aspx?qu=Query&sc=9&av=ZAC120)



Vanderghast, Access MVP

 

[Guest]

Hi Michel,

Thanks for sharing the sources. I will check those links out. Can you help
me one more thing?

Can we add a filter to the code below to filter if rank = 1? I tried to add
a code WHERE a.rank=1 between the lines FROM and GROUP BY, and I got a prompt
for the rank value.

SELECT a.studentID, a.ProjectCode, COUNT(*) as rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.studentID=b.studentID AND a.projectCode >= b.projectCode
GROUP BY a.studentID, a.ProjectCode

Thanks again for your help. I am sorry that I have no knowledge in SQL for
creating queries. I always create queries from Design mode.

 

[Michel Walsh]

SELECT a.studentID, a.ProjectCode, COUNT(*) as rank
FROM tableName AS a INNER JOIN tableName AS b
ON a.studentID=b.studentID AND a.projectCode >= b.projectCode
GROUP BY a.studentID, a.ProjectCode
HAVING COUNT(*)=1


The aggregate exists only after the GROUP are done, so we must use the
HAVING clause, to occur after the aggregations, rather than the WHERE
clause, which occurs before aggregations.


Since a rank of 1 is the minimum, you could do it simpler and faster with:


SELECT studentID, MIN(projectCode)
FROM tableName
GROUP BY studentID





Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

You are a genius. Is there a "Dummie for SQL to Create Access Queries" book
that I can buy? Would you recommend any book where I can learn more about
SQL coding?

Where would I use the code below? Would I use it in the same query (right
below the first code)?

SELECT studentID, MIN(projectCode)
FROM tableName
GROUP BY studentID

Thanks again.

 

[Michel Walsh]

The query with the MIN would replace the query you were to add the HAVING
COUNT(*) = 1, AND the crosstab, since adding the HAVING COUNT(*)=1, the
crosstab would generate just one pivoted column. The query with the MIN just
shorten the whole process. Note that adding a 'filter' on the rank to be = 1
was indeed simplifying greatly the result, isn't it?


If Access Developers' Handbook chapter 4 is already mastered, I would
suggest the John L. Viescas' book.


Vanderghast, Access MVP