Query

J

Jeff Kaufman

I am currently using Access 2003. I currently have a table that has the
following fields: Month, Team, ID. I would like to be able set up a query(I
believe a cross-tab query) that would bring back the following results.

So if I had the following data in my table
Col1 Col2 Col3
May - Team 1 - ID 1
May - Team 1 - ID 2
May - Team 1 - ID 3
May - Team 2 - ID 4
May - Team 2 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1
June - Team 1 - ID 4
June - Team 2 - ID 6

I would get the following results

May - Team 1 - ID 1 - ID 2 - ID 3
May - Team 2 - ID 4 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1 - ID 4
June - Team 2 - ID 6
 
D

Duane Hookom

First create a ranking query [qselTeamRanking] with SQL like:
SELECT tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID,
Count(tblNeedsAName.ID) AS CountOfID
FROM tblNeedsAName AS tblNeedsAName_1 RIGHT JOIN tblNeedsAName ON
(tblNeedsAName_1.Team = tblNeedsAName.Team) AND (tblNeedsAName_1.Mth =
tblNeedsAName.Mth)
WHERE (((tblNeedsAName_1.ID)<=[tblNeedsAName].[ID]))
GROUP BY tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID;

Then create a crosstab based on the ranking query:

TRANSFORM First(qselTeamRanking.ID) AS FirstOfID
SELECT qselTeamRanking.Mth, qselTeamRanking.Team
FROM qselTeamRanking
GROUP BY qselTeamRanking.Mth, qselTeamRanking.Team
PIVOT qselTeamRanking.CountOfID;
 
J

Jeff Kaufman

I am having troubly getting the first query to work... I used the following
SQL and I am getting a Syntax error (Missing operator) in query expression
'Table - Employee Hirearchy.Performance Month.' Thoughts? I have posted my
SQL below, I can't seem find where I messed up.

My SQL:
SELECT Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID,
Count(Table - Employee Hierarchy.eLinkID) AS CountOfeLinkID
FROM Table - Employee Hierarchy AS Table - Employee Hierarchy_1 RIGHT JOIN
Table - Employee Hierarchy ON
(Table - Employee Hierarchy_1.Team Name = Table - Employee Hierarchy.Team
Name) AND (Table - Employee Hierarchy_1.Performance Month =
Table - Employee Hierarchy.Performance Month)
WHERE (((Table - Employee Hierarchy_1.eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID;



Duane Hookom said:
First create a ranking query [qselTeamRanking] with SQL like:
SELECT tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID,
Count(tblNeedsAName.ID) AS CountOfID
FROM tblNeedsAName AS tblNeedsAName_1 RIGHT JOIN tblNeedsAName ON
(tblNeedsAName_1.Team = tblNeedsAName.Team) AND (tblNeedsAName_1.Mth =
tblNeedsAName.Mth)
WHERE (((tblNeedsAName_1.ID)<=[tblNeedsAName].[ID]))
GROUP BY tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID;

Then create a crosstab based on the ranking query:

TRANSFORM First(qselTeamRanking.ID) AS FirstOfID
SELECT qselTeamRanking.Mth, qselTeamRanking.Team
FROM qselTeamRanking
GROUP BY qselTeamRanking.Mth, qselTeamRanking.Team
PIVOT qselTeamRanking.CountOfID;


--
Duane Hookom
Microsoft Access MVP


Jeff Kaufman said:
I am currently using Access 2003. I currently have a table that has the
following fields: Month, Team, ID. I would like to be able set up a query(I
believe a cross-tab query) that would bring back the following results.

So if I had the following data in my table
Col1 Col2 Col3
May - Team 1 - ID 1
May - Team 1 - ID 2
May - Team 1 - ID 3
May - Team 2 - ID 4
May - Team 2 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1
June - Team 1 - ID 4
June - Team 2 - ID 6

I would get the following results

May - Team 1 - ID 1 - ID 2 - ID 3
May - Team 2 - ID 4 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1 - ID 4
June - Team 2 - ID 6
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Because you've got special characters (the - character and the spaces) in
your names, you must enclose it in square brackets:

SELECT [Table - Employee Hierarchy].[Performance Month],
Table - Employee Hierarchy].[Team Name], [Table - Employee
Hierarchy].eLinkID,
Count([Table - Employee Hierarchy].eLinkID) AS CountOfeLinkID
FROM [Table - Employee Hierarchy] AS [Table - Employee Hierarchy_1]
RIGHT JOIN [Table - Employee Hierarchy]
ON ([Table - Employee Hierarchy_1].[Team Name] = [Table - Employee
Hierarchy].[Team Name])
AND ([Table - Employee Hierarchy_1].[Performance Month] = [Table - Employee
Hierarchy].[Performance Month])
WHERE ((([Table - Employee Hierarchy_1].eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY [Table - Employee Hierarchy].[Performance Month], [Table - Employee
Hierarchy].[Team Name], [Table - Employee Hierarchy].eLinkID;

Of course, the fact that you've included eLinkID as a field in your SELECT
and your GROUP BY means that you're going to get back one row for each
combination, so I don't see the point of the Count...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Kaufman said:
I am having troubly getting the first query to work... I used the following
SQL and I am getting a Syntax error (Missing operator) in query expression
'Table - Employee Hirearchy.Performance Month.' Thoughts? I have posted
my
SQL below, I can't seem find where I messed up.

My SQL:
SELECT Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID,
Count(Table - Employee Hierarchy.eLinkID) AS CountOfeLinkID
FROM Table - Employee Hierarchy AS Table - Employee Hierarchy_1 RIGHT JOIN
Table - Employee Hierarchy ON
(Table - Employee Hierarchy_1.Team Name = Table - Employee Hierarchy.Team
Name) AND (Table - Employee Hierarchy_1.Performance Month =
Table - Employee Hierarchy.Performance Month)
WHERE (((Table - Employee Hierarchy_1.eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID;



Duane Hookom said:
First create a ranking query [qselTeamRanking] with SQL like:
SELECT tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID,
Count(tblNeedsAName.ID) AS CountOfID
FROM tblNeedsAName AS tblNeedsAName_1 RIGHT JOIN tblNeedsAName ON
(tblNeedsAName_1.Team = tblNeedsAName.Team) AND (tblNeedsAName_1.Mth =
tblNeedsAName.Mth)
WHERE (((tblNeedsAName_1.ID)<=[tblNeedsAName].[ID]))
GROUP BY tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID;

Then create a crosstab based on the ranking query:

TRANSFORM First(qselTeamRanking.ID) AS FirstOfID
SELECT qselTeamRanking.Mth, qselTeamRanking.Team
FROM qselTeamRanking
GROUP BY qselTeamRanking.Mth, qselTeamRanking.Team
PIVOT qselTeamRanking.CountOfID;


--
Duane Hookom
Microsoft Access MVP


Jeff Kaufman said:
I am currently using Access 2003. I currently have a table that has
the
following fields: Month, Team, ID. I would like to be able set up a
query(I
believe a cross-tab query) that would bring back the following results.

So if I had the following data in my table
Col1 Col2 Col3
May - Team 1 - ID 1
May - Team 1 - ID 2
May - Team 1 - ID 3
May - Team 2 - ID 4
May - Team 2 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1
June - Team 1 - ID 4
June - Team 2 - ID 6

I would get the following results

May - Team 1 - ID 1 - ID 2 - ID 3
May - Team 2 - ID 4 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1 - ID 4
June - Team 2 - ID 6
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Kaufman

Okay... I got this to work and with the combination of the second query
listed below I am getting what I want, but now I want to be able to have it
prompt me for the performance month.. so I can just get the results for
August or the just the resutls for July... Any thoughts on how to do this...
when I attempt to use [Performance Month:] in the crietria it says it doesn't
recognize the field [Performance Month]

SQL

TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;


Thanks

Jeff

Douglas J. Steele said:
Because you've got special characters (the - character and the spaces) in
your names, you must enclose it in square brackets:

SELECT [Table - Employee Hierarchy].[Performance Month],
Table - Employee Hierarchy].[Team Name], [Table - Employee
Hierarchy].eLinkID,
Count([Table - Employee Hierarchy].eLinkID) AS CountOfeLinkID
FROM [Table - Employee Hierarchy] AS [Table - Employee Hierarchy_1]
RIGHT JOIN [Table - Employee Hierarchy]
ON ([Table - Employee Hierarchy_1].[Team Name] = [Table - Employee
Hierarchy].[Team Name])
AND ([Table - Employee Hierarchy_1].[Performance Month] = [Table - Employee
Hierarchy].[Performance Month])
WHERE ((([Table - Employee Hierarchy_1].eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY [Table - Employee Hierarchy].[Performance Month], [Table - Employee
Hierarchy].[Team Name], [Table - Employee Hierarchy].eLinkID;

Of course, the fact that you've included eLinkID as a field in your SELECT
and your GROUP BY means that you're going to get back one row for each
combination, so I don't see the point of the Count...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Kaufman said:
I am having troubly getting the first query to work... I used the following
SQL and I am getting a Syntax error (Missing operator) in query expression
'Table - Employee Hirearchy.Performance Month.' Thoughts? I have posted
my
SQL below, I can't seem find where I messed up.

My SQL:
SELECT Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID,
Count(Table - Employee Hierarchy.eLinkID) AS CountOfeLinkID
FROM Table - Employee Hierarchy AS Table - Employee Hierarchy_1 RIGHT JOIN
Table - Employee Hierarchy ON
(Table - Employee Hierarchy_1.Team Name = Table - Employee Hierarchy.Team
Name) AND (Table - Employee Hierarchy_1.Performance Month =
Table - Employee Hierarchy.Performance Month)
WHERE (((Table - Employee Hierarchy_1.eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID;



Duane Hookom said:
First create a ranking query [qselTeamRanking] with SQL like:
SELECT tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID,
Count(tblNeedsAName.ID) AS CountOfID
FROM tblNeedsAName AS tblNeedsAName_1 RIGHT JOIN tblNeedsAName ON
(tblNeedsAName_1.Team = tblNeedsAName.Team) AND (tblNeedsAName_1.Mth =
tblNeedsAName.Mth)
WHERE (((tblNeedsAName_1.ID)<=[tblNeedsAName].[ID]))
GROUP BY tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID;

Then create a crosstab based on the ranking query:

TRANSFORM First(qselTeamRanking.ID) AS FirstOfID
SELECT qselTeamRanking.Mth, qselTeamRanking.Team
FROM qselTeamRanking
GROUP BY qselTeamRanking.Mth, qselTeamRanking.Team
PIVOT qselTeamRanking.CountOfID;


--
Duane Hookom
Microsoft Access MVP


:

I am currently using Access 2003. I currently have a table that has
the
following fields: Month, Team, ID. I would like to be able set up a
query(I
believe a cross-tab query) that would bring back the following results.

So if I had the following data in my table
Col1 Col2 Col3
May - Team 1 - ID 1
May - Team 1 - ID 2
May - Team 1 - ID 3
May - Team 2 - ID 4
May - Team 2 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1
June - Team 1 - ID 4
June - Team 2 - ID 6

I would get the following results

May - Team 1 - ID 1 - ID 2 - ID 3
May - Team 2 - ID 4 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1 - ID 4
June - Team 2 - ID 6
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

In crosstab queries, it's critical that parameters be declared. What's the
data type of Performance Month? If it's text, try

PARAMETERS [Performance Month:] Text;
TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;

If it's numeric, try

PARAMETERS [Performance Month:] Long;
TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Kaufman said:
Okay... I got this to work and with the combination of the second query
listed below I am getting what I want, but now I want to be able to have
it
prompt me for the performance month.. so I can just get the results for
August or the just the resutls for July... Any thoughts on how to do
this...
when I attempt to use [Performance Month:] in the crietria it says it
doesn't
recognize the field [Performance Month]

SQL

TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;


Thanks

Jeff

Douglas J. Steele said:
Because you've got special characters (the - character and the spaces) in
your names, you must enclose it in square brackets:

SELECT [Table - Employee Hierarchy].[Performance Month],
Table - Employee Hierarchy].[Team Name], [Table - Employee
Hierarchy].eLinkID,
Count([Table - Employee Hierarchy].eLinkID) AS CountOfeLinkID
FROM [Table - Employee Hierarchy] AS [Table - Employee Hierarchy_1]
RIGHT JOIN [Table - Employee Hierarchy]
ON ([Table - Employee Hierarchy_1].[Team Name] = [Table - Employee
Hierarchy].[Team Name])
AND ([Table - Employee Hierarchy_1].[Performance Month] = [Table -
Employee
Hierarchy].[Performance Month])
WHERE ((([Table - Employee Hierarchy_1].eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY [Table - Employee Hierarchy].[Performance Month], [Table -
Employee
Hierarchy].[Team Name], [Table - Employee Hierarchy].eLinkID;

Of course, the fact that you've included eLinkID as a field in your
SELECT
and your GROUP BY means that you're going to get back one row for each
combination, so I don't see the point of the Count...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Kaufman said:
I am having troubly getting the first query to work... I used the
following
SQL and I am getting a Syntax error (Missing operator) in query
expression
'Table - Employee Hirearchy.Performance Month.' Thoughts? I have
posted
my
SQL below, I can't seem find where I messed up.

My SQL:
SELECT Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID,
Count(Table - Employee Hierarchy.eLinkID) AS CountOfeLinkID
FROM Table - Employee Hierarchy AS Table - Employee Hierarchy_1 RIGHT
JOIN
Table - Employee Hierarchy ON
(Table - Employee Hierarchy_1.Team Name = Table - Employee
Hierarchy.Team
Name) AND (Table - Employee Hierarchy_1.Performance Month =
Table - Employee Hierarchy.Performance Month)
WHERE (((Table - Employee Hierarchy_1.eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID;



:

First create a ranking query [qselTeamRanking] with SQL like:
SELECT tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID,
Count(tblNeedsAName.ID) AS CountOfID
FROM tblNeedsAName AS tblNeedsAName_1 RIGHT JOIN tblNeedsAName ON
(tblNeedsAName_1.Team = tblNeedsAName.Team) AND (tblNeedsAName_1.Mth =
tblNeedsAName.Mth)
WHERE (((tblNeedsAName_1.ID)<=[tblNeedsAName].[ID]))
GROUP BY tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID;

Then create a crosstab based on the ranking query:

TRANSFORM First(qselTeamRanking.ID) AS FirstOfID
SELECT qselTeamRanking.Mth, qselTeamRanking.Team
FROM qselTeamRanking
GROUP BY qselTeamRanking.Mth, qselTeamRanking.Team
PIVOT qselTeamRanking.CountOfID;


--
Duane Hookom
Microsoft Access MVP


:

I am currently using Access 2003. I currently have a table that
has
the
following fields: Month, Team, ID. I would like to be able set up
a
query(I
believe a cross-tab query) that would bring back the following
results.

So if I had the following data in my table
Col1 Col2 Col3
May - Team 1 - ID 1
May - Team 1 - ID 2
May - Team 1 - ID 3
May - Team 2 - ID 4
May - Team 2 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1
June - Team 1 - ID 4
June - Team 2 - ID 6

I would get the following results

May - Team 1 - ID 1 - ID 2 - ID 3
May - Team 2 - ID 4 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1 - ID 4
June - Team 2 - ID 6
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Kaufman

Thank you... the combination of this works great :)

Douglas J. Steele said:
In crosstab queries, it's critical that parameters be declared. What's the
data type of Performance Month? If it's text, try

PARAMETERS [Performance Month:] Text;
TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;

If it's numeric, try

PARAMETERS [Performance Month:] Long;
TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Kaufman said:
Okay... I got this to work and with the combination of the second query
listed below I am getting what I want, but now I want to be able to have
it
prompt me for the performance month.. so I can just get the results for
August or the just the resutls for July... Any thoughts on how to do
this...
when I attempt to use [Performance Month:] in the crietria it says it
doesn't
recognize the field [Performance Month]

SQL

TRANSFORM First(Query1.eLinkID) AS FirstOfeLinkID
SELECT Query1.[Performance Month], Query1.[Team Name]
FROM Query1
WHERE (((Query1.[Performance Month])=[Performance Month:]))
GROUP BY Query1.[Performance Month], Query1.[Team Name]
PIVOT Query1.CountOfeLinkID;


Thanks

Jeff

Douglas J. Steele said:
Because you've got special characters (the - character and the spaces) in
your names, you must enclose it in square brackets:

SELECT [Table - Employee Hierarchy].[Performance Month],
Table - Employee Hierarchy].[Team Name], [Table - Employee
Hierarchy].eLinkID,
Count([Table - Employee Hierarchy].eLinkID) AS CountOfeLinkID
FROM [Table - Employee Hierarchy] AS [Table - Employee Hierarchy_1]
RIGHT JOIN [Table - Employee Hierarchy]
ON ([Table - Employee Hierarchy_1].[Team Name] = [Table - Employee
Hierarchy].[Team Name])
AND ([Table - Employee Hierarchy_1].[Performance Month] = [Table -
Employee
Hierarchy].[Performance Month])
WHERE ((([Table - Employee Hierarchy_1].eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY [Table - Employee Hierarchy].[Performance Month], [Table -
Employee
Hierarchy].[Team Name], [Table - Employee Hierarchy].eLinkID;

Of course, the fact that you've included eLinkID as a field in your
SELECT
and your GROUP BY means that you're going to get back one row for each
combination, so I don't see the point of the Count...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am having troubly getting the first query to work... I used the
following
SQL and I am getting a Syntax error (Missing operator) in query
expression
'Table - Employee Hirearchy.Performance Month.' Thoughts? I have
posted
my
SQL below, I can't seem find where I messed up.

My SQL:
SELECT Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID,
Count(Table - Employee Hierarchy.eLinkID) AS CountOfeLinkID
FROM Table - Employee Hierarchy AS Table - Employee Hierarchy_1 RIGHT
JOIN
Table - Employee Hierarchy ON
(Table - Employee Hierarchy_1.Team Name = Table - Employee
Hierarchy.Team
Name) AND (Table - Employee Hierarchy_1.Performance Month =
Table - Employee Hierarchy.Performance Month)
WHERE (((Table - Employee Hierarchy_1.eLinkID)<=[Table - Employee
Hierarchy].[eLinkID]))
GROUP BY Table - Employee Hierarchy.Performance Month, Table - Employee
Hierarchy.Team Name, Table - Employee Hierarchy.eLinkID;



:

First create a ranking query [qselTeamRanking] with SQL like:
SELECT tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID,
Count(tblNeedsAName.ID) AS CountOfID
FROM tblNeedsAName AS tblNeedsAName_1 RIGHT JOIN tblNeedsAName ON
(tblNeedsAName_1.Team = tblNeedsAName.Team) AND (tblNeedsAName_1.Mth =
tblNeedsAName.Mth)
WHERE (((tblNeedsAName_1.ID)<=[tblNeedsAName].[ID]))
GROUP BY tblNeedsAName.Mth, tblNeedsAName.Team, tblNeedsAName.ID;

Then create a crosstab based on the ranking query:

TRANSFORM First(qselTeamRanking.ID) AS FirstOfID
SELECT qselTeamRanking.Mth, qselTeamRanking.Team
FROM qselTeamRanking
GROUP BY qselTeamRanking.Mth, qselTeamRanking.Team
PIVOT qselTeamRanking.CountOfID;


--
Duane Hookom
Microsoft Access MVP


:

I am currently using Access 2003. I currently have a table that
has
the
following fields: Month, Team, ID. I would like to be able set up
a
query(I
believe a cross-tab query) that would bring back the following
results.

So if I had the following data in my table
Col1 Col2 Col3
May - Team 1 - ID 1
May - Team 1 - ID 2
May - Team 1 - ID 3
May - Team 2 - ID 4
May - Team 2 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1
June - Team 1 - ID 4
June - Team 2 - ID 6

I would get the following results

May - Team 1 - ID 1 - ID 2 - ID 3
May - Team 2 - ID 4 - ID 5
May - Team 3 - ID 6
June - Team 1 - ID 1 - ID 4
June - Team 2 - ID 6
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

quick query question 3
VLookup or Macro? 0
Incremental 1, 4, 7, 10, 2
Subquery Correllated In Select Clause - Is this possible? 3
Query about an append query 1
Creating Different Headers in Word 0
Finding the right style field in VBA 0
Finding the proper style field and the numbered paragraph value within the style 0

Top