Grouping - Distinct

  • Thread starter Thread starter Darrel
  • Start date Start date
D

Darrel

I'm trying to create a query from 2 joined tables.
EMP & Quizzes are joined on EMP_ID
In the results, I'd like to have one row for each employee with a column
showing their store, ID, name, and hire date (from the EMP table) and then a
column for each quiz date and quiz score for each quiz number. The Quizzes
table has a field for quiz number (b01 - b05), date taken, and score. It
should come out something like this:

STORE EMP_ID NAME Hire Date b01 Date b01 Score b02 Date b02 Score b03
Date b03 Score b04 Date b04 Score b05 Date b05 Score
3 84 JOE TEST 1/16/2009 12/22/2009 10:30:28 AM 80.00% 12/21/2009 11:58:24
AM 93.33% 12/28/2009 4:45:33 PM 93% 3/9/2009 4:50:16 PM 80% 1/27/2010 8:18:20
AM 93% 12/22/2009 11:20:56 AM 87%

3 90 JANE DOE Account Rep 1/6/2010 1/12/2010 1:40:31 PM 73.33% 1/7/2010
11:00:05 AM 93.33% 1/7/2010 11:19:05 AM 100% 1/7/2010 12:16:02 PM 93%
(sorry for the lost table format)
I can generate all of the data using expressions for each quiz date and
score, but it creates a seperate line for each one (if an employee took all 5
quizzes, there would be 5 lines).
I've tried grouping, select distinct, and select distinctrow to no avail. In
the past I've just used the grouping and changed the expressions to
"expression" instead of group by, and that worked fine, but in this instance,
i get the error "You tried to execute a query that does not include the
specified expression....as part of an aggregate function." The expression I
built looks like this:
Expr2: IIf([Quizzes]![quizid]="b01",[Quizzes]![date],"")
Expr3: IIf([Quizzes]![quizid]="b01",[Quizzes]![score],"")

I'm sure someone out there probably knows a better way.

thanks,
D
 
Try this --
TRANSFORM First(Quizzes.[date taken]) & " " & Quizzes.Score AS [b0_ Score]
SELECT store, ID, name, [hire date]
FROM (EMP LEFT JOIN Quizzes ON EMP.EMP_ID = Quizzes.EMP_ID)
GROUP BY store, ID, name, [hire date]
PIVOT [quiz number];
 
I'm a little lost on this since I've never ventured into using TRANSFORM &
PIVOT.
I tried to copy and paste your example but got a syntax error (missing
operator) on the TRANSFORM line. I also had to modify it slightly since i
used example table & field names when i posted the initial thread. Here it is
with the correct names:

TRANSFORM First(Quiz Summary.[date]) & " " & Quiz Summary.Score AS [b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

KARL DEWEY said:
Try this --
TRANSFORM First(Quizzes.[date taken]) & " " & Quizzes.Score AS [b0_ Score]
SELECT store, ID, name, [hire date]
FROM (EMP LEFT JOIN Quizzes ON EMP.EMP_ID = Quizzes.EMP_ID)
GROUP BY store, ID, name, [hire date]
PIVOT [quiz number];

--
Build a little, test a little.


Darrel said:
I'm trying to create a query from 2 joined tables.
EMP & Quizzes are joined on EMP_ID
In the results, I'd like to have one row for each employee with a column
showing their store, ID, name, and hire date (from the EMP table) and then a
column for each quiz date and quiz score for each quiz number. The Quizzes
table has a field for quiz number (b01 - b05), date taken, and score. It
should come out something like this:

STORE EMP_ID NAME Hire Date b01 Date b01 Score b02 Date b02 Score b03
Date b03 Score b04 Date b04 Score b05 Date b05 Score
3 84 JOE TEST 1/16/2009 12/22/2009 10:30:28 AM 80.00% 12/21/2009 11:58:24
AM 93.33% 12/28/2009 4:45:33 PM 93% 3/9/2009 4:50:16 PM 80% 1/27/2010 8:18:20
AM 93% 12/22/2009 11:20:56 AM 87%

3 90 JANE DOE Account Rep 1/6/2010 1/12/2010 1:40:31 PM 73.33% 1/7/2010
11:00:05 AM 93.33% 1/7/2010 11:19:05 AM 100% 1/7/2010 12:16:02 PM 93%
(sorry for the lost table format)
I can generate all of the data using expressions for each quiz date and
score, but it creates a seperate line for each one (if an employee took all 5
quizzes, there would be 5 lines).
I've tried grouping, select distinct, and select distinctrow to no avail. In
the past I've just used the grouping and changed the expressions to
"expression" instead of group by, and that worked fine, but in this instance,
i get the error "You tried to execute a query that does not include the
specified expression....as part of an aggregate function." The expression I
built looks like this:
Expr2: IIf([Quizzes]![quizid]="b01",[Quizzes]![date],"")
Expr3: IIf([Quizzes]![quizid]="b01",[Quizzes]![score],"")

I'm sure someone out there probably knows a better way.

thanks,
D
Click to expand...
Click to expand...
 
TRANSFORM & PIVOT make this into a crosstab query.

If you use a space in field or table names you have to enclose them in
brackets --
TRANSFORM First([Quiz Summary].[date]) & " " & [Quiz Summary].Score
AS[b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

--
Build a little, test a little.


Darrel said:
I'm a little lost on this since I've never ventured into using TRANSFORM &
PIVOT.
I tried to copy and paste your example but got a syntax error (missing
operator) on the TRANSFORM line. I also had to modify it slightly since i
used example table & field names when i posted the initial thread. Here it is
with the correct names:

TRANSFORM First(Quiz Summary.[date]) & " " & Quiz Summary.Score AS [b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

KARL DEWEY said:
Try this --
TRANSFORM First(Quizzes.[date taken]) & " " & Quizzes.Score AS [b0_ Score]
SELECT store, ID, name, [hire date]
FROM (EMP LEFT JOIN Quizzes ON EMP.EMP_ID = Quizzes.EMP_ID)
GROUP BY store, ID, name, [hire date]
PIVOT [quiz number];

--
Build a little, test a little.


Darrel said:
I'm trying to create a query from 2 joined tables.
EMP & Quizzes are joined on EMP_ID
In the results, I'd like to have one row for each employee with a column
showing their store, ID, name, and hire date (from the EMP table) and then a
column for each quiz date and quiz score for each quiz number. The Quizzes
table has a field for quiz number (b01 - b05), date taken, and score. It
should come out something like this:

STORE EMP_ID NAME Hire Date b01 Date b01 Score b02 Date b02 Score b03
Date b03 Score b04 Date b04 Score b05 Date b05 Score
3 84 JOE TEST 1/16/2009 12/22/2009 10:30:28 AM 80.00% 12/21/2009 11:58:24
AM 93.33% 12/28/2009 4:45:33 PM 93% 3/9/2009 4:50:16 PM 80% 1/27/2010 8:18:20
AM 93% 12/22/2009 11:20:56 AM 87%

3 90 JANE DOE Account Rep 1/6/2010 1/12/2010 1:40:31 PM 73.33% 1/7/2010
11:00:05 AM 93.33% 1/7/2010 11:19:05 AM 100% 1/7/2010 12:16:02 PM 93%
(sorry for the lost table format)
I can generate all of the data using expressions for each quiz date and
score, but it creates a seperate line for each one (if an employee took all 5
quizzes, there would be 5 lines).
I've tried grouping, select distinct, and select distinctrow to no avail. In
the past I've just used the grouping and changed the expressions to
"expression" instead of group by, and that worked fine, but in this instance,
i get the error "You tried to execute a query that does not include the
specified expression....as part of an aggregate function." The expression I
built looks like this:
Expr2: IIf([Quizzes]![quizid]="b01",[Quizzes]![date],"")
Expr3: IIf([Quizzes]![quizid]="b01",[Quizzes]![score],"")

I'm sure someone out there probably knows a better way.

thanks,
D
Click to expand...
Click to expand...
Click to expand...
 
I'm now getting errors in the JOIN statement.
I'm going to try and dummy this down even more since it's my first crosstab
query.

My table (actually the results of a sub query) is now named
Q_newest_date_score and contains 4 fields; EMP_NBR, QUIZ_NBR, QUIZ_DATE, &
QUIZ_SCORE.

EMP_NBR is the filed that I need to have a unique row for.
I have up to 20 different QUIZ_NBR values for each employee each having a
QUIZ_DATE & QUIZ_SCORE
I need a column for each QUIZ_NBR to contain the QUIZ_DATE value
I need a column for each QUIZ_NBR to contain the QUIZ_SCORE

Is using a crosstab query the right solution?

thanks so much for your patience.
Darrel


KARL DEWEY said:
TRANSFORM & PIVOT make this into a crosstab query.

If you use a space in field or table names you have to enclose them in
brackets --
TRANSFORM First([Quiz Summary].[date]) & " " & [Quiz Summary].Score
AS[b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

--
Build a little, test a little.


Darrel said:
I'm a little lost on this since I've never ventured into using TRANSFORM &
PIVOT.
I tried to copy and paste your example but got a syntax error (missing
operator) on the TRANSFORM line. I also had to modify it slightly since i
used example table & field names when i posted the initial thread. Here it is
with the correct names:

TRANSFORM First(Quiz Summary.[date]) & " " & Quiz Summary.Score AS [b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

KARL DEWEY said:
Try this --
TRANSFORM First(Quizzes.[date taken]) & " " & Quizzes.Score AS [b0_ Score]
SELECT store, ID, name, [hire date]
FROM (EMP LEFT JOIN Quizzes ON EMP.EMP_ID = Quizzes.EMP_ID)
GROUP BY store, ID, name, [hire date]
PIVOT [quiz number];

--
Build a little, test a little.


:

I'm trying to create a query from 2 joined tables.
EMP & Quizzes are joined on EMP_ID
In the results, I'd like to have one row for each employee with a column
showing their store, ID, name, and hire date (from the EMP table) and then a
column for each quiz date and quiz score for each quiz number. The Quizzes
table has a field for quiz number (b01 - b05), date taken, and score. It
should come out something like this:

STORE EMP_ID NAME Hire Date b01 Date b01 Score b02 Date b02 Score b03
Date b03 Score b04 Date b04 Score b05 Date b05 Score
3 84 JOE TEST 1/16/2009 12/22/2009 10:30:28 AM 80.00% 12/21/2009 11:58:24
AM 93.33% 12/28/2009 4:45:33 PM 93% 3/9/2009 4:50:16 PM 80% 1/27/2010 8:18:20
AM 93% 12/22/2009 11:20:56 AM 87%

3 90 JANE DOE Account Rep 1/6/2010 1/12/2010 1:40:31 PM 73.33% 1/7/2010
11:00:05 AM 93.33% 1/7/2010 11:19:05 AM 100% 1/7/2010 12:16:02 PM 93%
(sorry for the lost table format)
I can generate all of the data using expressions for each quiz date and
score, but it creates a seperate line for each one (if an employee took all 5
quizzes, there would be 5 lines).
I've tried grouping, select distinct, and select distinctrow to no avail. In
the past I've just used the grouping and changed the expressions to
"expression" instead of group by, and that worked fine, but in this instance,
i get the error "You tried to execute a query that does not include the
specified expression....as part of an aggregate function." The expression I
built looks like this:
Expr2: IIf([Quizzes]![quizid]="b01",[Quizzes]![date],"")
Expr3: IIf([Quizzes]![quizid]="b01",[Quizzes]![score],"")

I'm sure someone out there probably knows a better way.

thanks,
D
Click to expand...
Click to expand...
Click to expand...
 
After playing with crosstab queries for a bit, I think I got it resolved. I
actually needed to do two seperate crosstab queries, one for the date and one
for the score (now i understand why you were concatonating them) and then
created a new query to join them to the employee file. Works like a charm.
Thanks for enlightening me to a new form of query. It was much overdue.

Darrel


Darrel said:
I'm now getting errors in the JOIN statement.
I'm going to try and dummy this down even more since it's my first crosstab
query.

My table (actually the results of a sub query) is now named
Q_newest_date_score and contains 4 fields; EMP_NBR, QUIZ_NBR, QUIZ_DATE, &
QUIZ_SCORE.

EMP_NBR is the filed that I need to have a unique row for.
I have up to 20 different QUIZ_NBR values for each employee each having a
QUIZ_DATE & QUIZ_SCORE
I need a column for each QUIZ_NBR to contain the QUIZ_DATE value
I need a column for each QUIZ_NBR to contain the QUIZ_SCORE

Is using a crosstab query the right solution?

thanks so much for your patience.
Darrel


KARL DEWEY said:
TRANSFORM & PIVOT make this into a crosstab query.

If you use a space in field or table names you have to enclose them in
brackets --
TRANSFORM First([Quiz Summary].[date]) & " " & [Quiz Summary].Score
AS[b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

--
Build a little, test a little.


Darrel said:
I'm a little lost on this since I've never ventured into using TRANSFORM &
PIVOT.
I tried to copy and paste your example but got a syntax error (missing
operator) on the TRANSFORM line. I also had to modify it slightly since i
used example table & field names when i posted the initial thread. Here it is
with the correct names:

TRANSFORM First(Quiz Summary.[date]) & " " & Quiz Summary.Score AS [b0_Score]
SELECT [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
FROM [LOC-EMP] LEFT JOIN [Quiz Summary] ON [LOC-EMP].[EMP-NBR] = [Quiz
Summary].[EMP-NBR]
GROUP BY [LOC-EMP].[EMP-STORE-NBR], [LOC-EMP].[EMP-NBR],
[LOC-EMP].[EMP-FIRST-NAME-MI], [LOC-EMP].[EMP-FIRST-EMPLOYMENT-DATE]
PIVOT [Quiz];

:

Try this --
TRANSFORM First(Quizzes.[date taken]) & " " & Quizzes.Score AS [b0_ Score]
SELECT store, ID, name, [hire date]
FROM (EMP LEFT JOIN Quizzes ON EMP.EMP_ID = Quizzes.EMP_ID)
GROUP BY store, ID, name, [hire date]
PIVOT [quiz number];

--
Build a little, test a little.


:

I'm trying to create a query from 2 joined tables.
EMP & Quizzes are joined on EMP_ID
In the results, I'd like to have one row for each employee with a column
showing their store, ID, name, and hire date (from the EMP table) and then a
column for each quiz date and quiz score for each quiz number. The Quizzes
table has a field for quiz number (b01 - b05), date taken, and score. It
should come out something like this:

STORE EMP_ID NAME Hire Date b01 Date b01 Score b02 Date b02 Score b03
Date b03 Score b04 Date b04 Score b05 Date b05 Score
3 84 JOE TEST 1/16/2009 12/22/2009 10:30:28 AM 80.00% 12/21/2009 11:58:24
AM 93.33% 12/28/2009 4:45:33 PM 93% 3/9/2009 4:50:16 PM 80% 1/27/2010 8:18:20
AM 93% 12/22/2009 11:20:56 AM 87%

3 90 JANE DOE Account Rep 1/6/2010 1/12/2010 1:40:31 PM 73.33% 1/7/2010
11:00:05 AM 93.33% 1/7/2010 11:19:05 AM 100% 1/7/2010 12:16:02 PM 93%
(sorry for the lost table format)
I can generate all of the data using expressions for each quiz date and
score, but it creates a seperate line for each one (if an employee took all 5
quizzes, there would be 5 lines).
I've tried grouping, select distinct, and select distinctrow to no avail. In
the past I've just used the grouping and changed the expressions to
"expression" instead of group by, and that worked fine, but in this instance,
i get the error "You tried to execute a query that does not include the
specified expression....as part of an aggregate function." The expression I
built looks like this:
Expr2: IIf([Quizzes]![quizid]="b01",[Quizzes]![date],"")
Expr3: IIf([Quizzes]![quizid]="b01",[Quizzes]![score],"")

I'm sure someone out there probably knows a better way.

thanks,
D
Click to expand...
Click to expand...
Click to expand...
 
Back
Top