Prev: Using a Yes/No field to trigger a calculation in another field - J
Next: Delete Records Where The Like* Can Be used??
From: Nova on 24 Mar 2010 04:51
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?

From: Stefan Hoffmann on 24 Mar 2010 04:54
hi Nova,

On 24.03.2010 09:51, Nova wrote:
> I have 2 fields in a table
> JobNo Employee
> 1 A
> and want to query result
> JobNo Employee1 Employee2 Employee3
> 1 A B
>
> How to create query?
Use the Crosstab Query Wizard to create this kind of result.

http://allenbrowne.com/ser-67.html


mfG
--> stefan <--
From: Krzysztof Naworyta on 24 Mar 2010 06:44
Juzer Nova <Nova(a)discussions.microsoft.com> 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

--
KN


From: John Spencer on 24 Mar 2010 08:38
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

Nova wrote:
> 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?
>
From: Krzysztof Naworyta on 24 Mar 2010 09:18
Juzer John Spencer <spencer(a)chpdm.edu> 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?

--
KN



 |  Next  |  Last
Pages: 1 2
Prev: Using a Yes/No field to trigger a calculation in another field - J
Next: Delete Records Where The Like* Can Be used??