How to query


N

Nova

I have 2 fields in a table
JobNo Employee
1 A
1 B
2 X
2 Y
3 H
3 J
3 K

and want to query result
JobNo Employee1 Employee2 Employee3
1 A B
2 X Y
3 H J K

How to create query?
 
Ad

Advertisements

K

Krzysztof Naworyta

Juzer Nova <[email protected]> napisa³
| I have 2 fields in a table
| JobNo Employee
| 1 A
| 1 B
| 2 X
| 2 Y
| 3 H
| 3 J
| 3 K
|
| and want to query result
| JobNo Employee1 Employee2 Employee3
| 1 A B
| 2 X Y
| 3 H J K
|
| How to create query?


You can not!
You need third column: No

JobNo Employee No
1 A 1
1 B 2
2 X 1
2 Y 2
3 H 1
3 J 2
3 K 3


and now you can use crosstab query
 
J

John Spencer

Hopefully, you know how to use the SQL window to build queries. If not, you
cannot build the first query in design view. Post back and ask for step by
step instructions on setting up the first query or using DCount to get the
rank in design view. DCOUNT will be slow if you have any significant number
of records to process.

First Saved Query:
SELECT A.JobNo, A.Employee
, 1 + Count(B.Employee) As Rank
FROM [YourTable] As A LEFT JOIN [YourTable] as B
ON A.JobNo = B.JobNo
AND A.Employee>B.Employee
GROUP BY A.JobNo, A.Employee

Then you use a crosstab query against that result.
TRANSFORM First(Employee) as TheValue
SELECT JobNo
FROM RankingQuery
GROUP BY JobNo
PIVOT "Employee" & Rank

If you are planning to use this as the source for a report, there are some
refinements that can be made to the queries.



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
K

Krzysztof Naworyta

Juzer John Spencer <[email protected]> napisa³

| Hopefully, you know how to use the SQL window to build queries. If
| not, you cannot build the first query in design view. Post back and
| ask for step by step instructions on setting up the first query or
| using DCount to get the rank in design view. DCOUNT will be slow if
| you have any significant number of records to process.

There is no need of using DCount() function, while we have subquerys.

| First Saved Query:
| SELECT A.JobNo, A.Employee
| , 1 + Count(B.Employee) As Rank
| FROM [YourTable] As A LEFT JOIN [YourTable] as B
| ON A.JobNo = B.JobNo
| AND A.Employee>B.Employee
| GROUP BY A.JobNo, A.Employee

Or:

Select
JobNo,
Employee,
(Select count(*) from YourTable t2
where t2.JobNo = t1.JobNo
and t2.id <= t1.id) as Rank
From
YourTable t1

(I hope "YourTable" has primary key ID ;) )

I have not tested this and I don't know which query will be faster for
relatively large tables (mine with subquery or yours with join and group
by)

And which pivot-query based on those two querys will be faster?
 
Ad

Advertisements

K

Krzysztof Naworyta

Juzer Krzysztof Naworyta <[email protected]> napisa³


(...)
| I have not tested this and I don't know which query will be faster for
| relatively large tables (mine with subquery or yours with join and group
| by)
|
| And which pivot-query based on those two querys will be faster?

I've tested it and can say that your grouping query with join is very much
faster.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top