How to query

  • Thread starter Thread starter Nova
  • Start date Start date
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?
 
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
 
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
 
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?
 
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.
 
Back
Top