Question for Duane Hookom or anyone else that could help....

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

I would create a main report that is based on a group by query of the Work
Center and Shift fields. Then create a multiple column subreport with the
details including the date, IDH, qty, etc fields.

Plop the subreport into the detail section of the main report and link on
the Work center and Shift fields.
--
Duane Hookom
Microsoft Access MVP


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

You need Shift in the GROUP BY section --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift]
PIVOT Format([Scheduled Date],"m/d//yy");


"GoBrowns!" wrote:

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

Okay... we are getting so close, I can feel it!! :-)

Here is my next question:

How do I get this query to display MULTIPLE Work Center - IDH - IDH Descr -
Qty combinations for one date, one shift?

For instance, I have BOTH of these entries for one day for 1st Shift:

Work Center IDH IDH Descr
Qty
1313A 819923 NailPower Heavy DutyConstrAdh 301ml 11000
1313A 827693 PRS 10.2 OZ RT600 ADH TRC 12SC 22000

Currently, the query only shows the information for IDH 819923. How do I get
them both to show? Is this even possible?!?

Thanks for all the help!!!!




"KARL DEWEY" wrote:

You need Shift in the GROUP BY section --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift]
PIVOT Format([Scheduled Date],"m/d//yy");


"GoBrowns!" wrote:

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

I think the subreport solution would work as suggested earlier. If you want
to continue with the crosstab query, you can use the generic Concatenate()
function as the Value to return values from multiple "child" records. Search
Google on "Hookom Concatenate". There is a sample on Roger Carlson's site:
www.RogersAccessLibrary.com.

--
Duane Hookom
Microsoft Access MVP


"GoBrowns!" wrote:

Okay... we are getting so close, I can feel it!! :-)

Here is my next question:

How do I get this query to display MULTIPLE Work Center - IDH - IDH Descr -
Qty combinations for one date, one shift?

For instance, I have BOTH of these entries for one day for 1st Shift:

Work Center IDH IDH Descr
Qty
1313A 819923 NailPower Heavy DutyConstrAdh 301ml 11000
1313A 827693 PRS 10.2 OZ RT600 ADH TRC 12SC 22000

Currently, the query only shows the information for IDH 819923. How do I get
them both to show? Is this even possible?!?

Thanks for all the help!!!!




"KARL DEWEY" wrote:

You need Shift in the GROUP BY section --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift]
PIVOT Format([Scheduled Date],"m/d//yy");


"GoBrowns!" wrote:

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

Try this --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift], [IDH]
PIVOT Format([Scheduled Date],"m/d//yy");

"GoBrowns!" wrote:

Okay... we are getting so close, I can feel it!! :-)

Here is my next question:

How do I get this query to display MULTIPLE Work Center - IDH - IDH Descr -
Qty combinations for one date, one shift?

For instance, I have BOTH of these entries for one day for 1st Shift:

Work Center IDH IDH Descr
Qty
1313A 819923 NailPower Heavy DutyConstrAdh 301ml 11000
1313A 827693 PRS 10.2 OZ RT600 ADH TRC 12SC 22000

Currently, the query only shows the information for IDH 819923. How do I get
them both to show? Is this even possible?!?

Thanks for all the help!!!!




"KARL DEWEY" wrote:

You need Shift in the GROUP BY section --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift]
PIVOT Format([Scheduled Date],"m/d//yy");


"GoBrowns!" wrote:

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

Karl,

Closer....

Now, the only trouble is that there is a line for each IDH. So, instead of
there being one line for 1st Shift on Work Center 1313A, there are MULTIPLE
lines because there is more than one IDH for that work center in the given
time period. It makes my query jump from 18 lines to 131 lines long.....

Any other fixes?

Thanks SO, SO MUCH for the help!!!!!!

"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift], [IDH]
PIVOT Format([Scheduled Date],"m/d//yy");

"GoBrowns!" wrote:

Okay... we are getting so close, I can feel it!! :-)

Here is my next question:

How do I get this query to display MULTIPLE Work Center - IDH - IDH Descr -
Qty combinations for one date, one shift?

For instance, I have BOTH of these entries for one day for 1st Shift:

Work Center IDH IDH Descr
Qty
1313A 819923 NailPower Heavy DutyConstrAdh 301ml 11000
1313A 827693 PRS 10.2 OZ RT600 ADH TRC 12SC 22000

Currently, the query only shows the information for IDH 819923. How do I get
them both to show? Is this even possible?!?

Thanks for all the help!!!!




"KARL DEWEY" wrote:

You need Shift in the GROUP BY section --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift]
PIVOT Format([Scheduled Date],"m/d//yy");


"GoBrowns!" wrote:

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)

my query jump from 18 lines to 131 lines long.....
This does not tell me what the problem might be as you wanted multiple lines
(3) per entry.
How many lines did you expect it to produce?
Is there duplication of data in the output that is producing more lines that
you expected?

Have you thought about using Duane's suggestion?

"GoBrowns!" wrote:

Karl,

Closer....

Now, the only trouble is that there is a line for each IDH. So, instead of
there being one line for 1st Shift on Work Center 1313A, there are MULTIPLE
lines because there is more than one IDH for that work center in the given
time period. It makes my query jump from 18 lines to 131 lines long.....

Any other fixes?

Thanks SO, SO MUCH for the help!!!!!!

"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift], [IDH]
PIVOT Format([Scheduled Date],"m/d//yy");

"GoBrowns!" wrote:

Okay... we are getting so close, I can feel it!! :-)

Here is my next question:

How do I get this query to display MULTIPLE Work Center - IDH - IDH Descr -
Qty combinations for one date, one shift?

For instance, I have BOTH of these entries for one day for 1st Shift:

Work Center IDH IDH Descr
Qty
1313A 819923 NailPower Heavy DutyConstrAdh 301ml 11000
1313A 827693 PRS 10.2 OZ RT600 ADH TRC 12SC 22000

Currently, the query only shows the information for IDH 819923. How do I get
them both to show? Is this even possible?!?

Thanks for all the help!!!!




"KARL DEWEY" wrote:

You need Shift in the GROUP BY section --
TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center], [Shift]
PIVOT Format([Scheduled Date],"m/d//yy");


"GoBrowns!" wrote:

Karl -

Here is what I have:

TRANSFORM First([IDH]&Chr(13)&Chr(10)&[IDH
Description]&Chr(13)&Chr(10)&[Quantity]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM Short_Date_with_Shifts
GROUP BY [Work Center]
PIVOT Format([Scheduled Date],"m/d//yy");

I am getting the error message:

"You tried to execute a query that does not include the specified expression
"Shift" as part of an aggregate function."

What do I do about that?

Thanks!




"KARL DEWEY" wrote:

Try this --
TRANSFORM First([IHD]&Chr(13)&Chr(10)&[IHD Descr]&Chr(13)&Chr(10)&[QTY]) AS
FirstOfMANY
SELECT [Work Center], [Shift]
FROM YourTable
GROUP BY [Work Center]
PIVOT Format([Date],"m/d//yy");


"GoBrowns!" wrote:

Duane;

You answered a post I wrote earlier in the month, and I have been playing
around with your suggestions off and on for the last few weeks, and I am
still stumped. I am guessing that I just do not have the skills to make your
suggestion and demo (the Calendar database you directed me to) work for my
situation.

Here was my original question:

I have a query that gives me the following fields across the top:

Work Center, Work Center Descr, IDH, IDH Descr , Date , Qty , Shift

Is there any way to get a report that would orient the data this way:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

And so forth? I want there to be a "box" for each date - workcenter - shift
combo that houses the IDH, IDH Descr, and qty needed to produce. Is that
possible? A work center - shift combination could have multiple IDH, IDH
Descr, Qty entries too.


I tried crosstabs, but I have way too many variables!

I would be happy to attach things, if it will help illustrate better.

Is there any more information that I could provide you that might result in
more detailed instructions? I have examined your example DB, but I really
can't see how to equate it to my situation.

Thanks so much for your help - I just want to be rid of this little project
- it is so frustrating!!!! :-)