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

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
You might 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
WHERE [IDH] Is Not Null And [IDH Description] Is Not Null And [Quantity] Is
Not Null
GROUP BY [Work Center], [Shift], [IDH]
PIVOT Format([Scheduled Date],"m/d/yy");


GoBrowns! said:
Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

KARL DEWEY said:
Post the SQL of your query.

GoBrowns! said:
Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
Currently, it looks like this:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH1 IDH1

IDH Descr IDH Descr
Qty Qty
Work Center1 1 IDH2

IDH Descr

Qty
Work Center1 1 IDH3
IDH Descr
Qty
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty
Click to expand...

In other words, it repeats Work Center1 and Shift 1 multiple times. I want
it to only show up once and scrunch all the IDH, IDH Descr, Qty data in as
much as possible.




KARL DEWEY said:
Your said you wanted it to look like this --
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
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

Where is it putting the extra spaces?

GoBrowns! said:
Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

KARL DEWEY said:
Post the SQL of your query.

:

Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
it repeats Work Center1 and Shift 1 multiple times.
That is normal. Set the Hide Duplicates property in your report.

The added vertical spacing is what I thought was your problem. You may have
a record with Work Center, Shift, and date without other information to cause
the vertical spacing. Check your data.

GoBrowns! said:
Currently, it looks like this:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH1 IDH1

IDH Descr IDH Descr
Qty Qty
Work Center1 1 IDH2

IDH Descr

Qty
Work Center1 1 IDH3
IDH Descr
Qty
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty
Click to expand...

In other words, it repeats Work Center1 and Shift 1 multiple times. I want
it to only show up once and scrunch all the IDH, IDH Descr, Qty data in as
much as possible.




KARL DEWEY said:
Your said you wanted it to look like this --
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
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

Where is it putting the extra spaces?

GoBrowns! said:
Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

:

Post the SQL of your query.

:

Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
Yes, the vertical spacing is causing me huge problems. My report is giving me
on line per IDH.... so, if IDH "A" runs on Work Center1 in Shift 1 for three
of the five days of the week, it get is its own line. Then, if IDH "B" runs
on Work Center 1 in Shift 1 for two days, it gets its own line. What I am
left with (in terms of IDH only, each IDH does indeed have its own IDH Descr
and Qty, which is not an issue), makes my report look like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
WorkCenter1 1 IDH "A" IDH "A"
IDH "A"
IDH "B" IDH "B"


IDH "C"

This makes my report something like 41 pages long! Is there a way to scrunch
it up so that it looks like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
Work Center1 1 IDH "A" IDH "B" IDH "A" IDH "C" IDH "A"
IDH "B"

I have put Hide Duplicates in the properties... which got rid of my
duplicate work center listings, thanks!

I have tried "Can Shrink"... and unless I am putting this in the wrong
place, it is not working.

THANKS SO MUCH FOR THE HELP!!!!!!!!!!


KARL DEWEY said:
That is normal. Set the Hide Duplicates property in your report.

The added vertical spacing is what I thought was your problem. You may have
a record with Work Center, Shift, and date without other information to cause
the vertical spacing. Check your data.

GoBrowns! said:
Currently, it looks like this:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH1 IDH1

IDH Descr IDH Descr
Qty Qty
Work Center1 1 IDH2

IDH Descr

Qty
Work Center1 1 IDH3
IDH Descr
Qty
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty
Click to expand...

In other words, it repeats Work Center1 and Shift 1 multiple times. I want
it to only show up once and scrunch all the IDH, IDH Descr, Qty data in as
much as possible.




KARL DEWEY said:
Your said you wanted it to look like this --
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
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

Where is it putting the extra spaces?

:

Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

:

Post the SQL of your query.

:

Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
Drop the [IDH] from the GROUP BY --
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! said:
Yes, the vertical spacing is causing me huge problems. My report is giving me
on line per IDH.... so, if IDH "A" runs on Work Center1 in Shift 1 for three
of the five days of the week, it get is its own line. Then, if IDH "B" runs
on Work Center 1 in Shift 1 for two days, it gets its own line. What I am
left with (in terms of IDH only, each IDH does indeed have its own IDH Descr
and Qty, which is not an issue), makes my report look like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
WorkCenter1 1 IDH "A" IDH "A"
IDH "A"
IDH "B" IDH "B"


IDH "C"

This makes my report something like 41 pages long! Is there a way to scrunch
it up so that it looks like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
Work Center1 1 IDH "A" IDH "B" IDH "A" IDH "C" IDH "A"
IDH "B"

I have put Hide Duplicates in the properties... which got rid of my
duplicate work center listings, thanks!

I have tried "Can Shrink"... and unless I am putting this in the wrong
place, it is not working.

THANKS SO MUCH FOR THE HELP!!!!!!!!!!


KARL DEWEY said:
it repeats Work Center1 and Shift 1 multiple times.
Click to expand...
That is normal. Set the Hide Duplicates property in your report.

The added vertical spacing is what I thought was your problem. You may have
a record with Work Center, Shift, and date without other information to cause
the vertical spacing. Check your data.

GoBrowns! said:
Currently, it looks like this:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH1 IDH1

IDH Descr IDH Descr
Qty Qty
Work Center1 1 IDH2

IDH Descr

Qty
Work Center1 1 IDH3
IDH Descr
Qty
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

In other words, it repeats Work Center1 and Shift 1 multiple times. I want
it to only show up once and scrunch all the IDH, IDH Descr, Qty data in as
much as possible.




:

Your said you wanted it to look like this --
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
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

Where is it putting the extra spaces?

:

Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

:

Post the SQL of your query.

:

Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
I see the problem, last post will not do it.

I will have to think some more on it.

GoBrowns! said:
Yes, the vertical spacing is causing me huge problems. My report is giving me
on line per IDH.... so, if IDH "A" runs on Work Center1 in Shift 1 for three
of the five days of the week, it get is its own line. Then, if IDH "B" runs
on Work Center 1 in Shift 1 for two days, it gets its own line. What I am
left with (in terms of IDH only, each IDH does indeed have its own IDH Descr
and Qty, which is not an issue), makes my report look like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
WorkCenter1 1 IDH "A" IDH "A"
IDH "A"
IDH "B" IDH "B"


IDH "C"

This makes my report something like 41 pages long! Is there a way to scrunch
it up so that it looks like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
Work Center1 1 IDH "A" IDH "B" IDH "A" IDH "C" IDH "A"
IDH "B"

I have put Hide Duplicates in the properties... which got rid of my
duplicate work center listings, thanks!

I have tried "Can Shrink"... and unless I am putting this in the wrong
place, it is not working.

THANKS SO MUCH FOR THE HELP!!!!!!!!!!


KARL DEWEY said:
it repeats Work Center1 and Shift 1 multiple times.
Click to expand...
That is normal. Set the Hide Duplicates property in your report.

The added vertical spacing is what I thought was your problem. You may have
a record with Work Center, Shift, and date without other information to cause
the vertical spacing. Check your data.

GoBrowns! said:
Currently, it looks like this:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH1 IDH1

IDH Descr IDH Descr
Qty Qty
Work Center1 1 IDH2

IDH Descr

Qty
Work Center1 1 IDH3
IDH Descr
Qty
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

In other words, it repeats Work Center1 and Shift 1 multiple times. I want
it to only show up once and scrunch all the IDH, IDH Descr, Qty data in as
much as possible.




:

Your said you wanted it to look like this --
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
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

Where is it putting the extra spaces?

:

Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

:

Post the SQL of your query.

:

Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
Hey!

Any updates?

:-)

KARL DEWEY said:
I see the problem, last post will not do it.

I will have to think some more on it.

GoBrowns! said:
Yes, the vertical spacing is causing me huge problems. My report is giving me
on line per IDH.... so, if IDH "A" runs on Work Center1 in Shift 1 for three
of the five days of the week, it get is its own line. Then, if IDH "B" runs
on Work Center 1 in Shift 1 for two days, it gets its own line. What I am
left with (in terms of IDH only, each IDH does indeed have its own IDH Descr
and Qty, which is not an issue), makes my report look like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
WorkCenter1 1 IDH "A" IDH "A"
IDH "A"
IDH "B" IDH "B"


IDH "C"

This makes my report something like 41 pages long! Is there a way to scrunch
it up so that it looks like this:

Work Center Shift 5/1/09 5/2/09 5/3/09 5/4/09 5/5/09
Work Center1 1 IDH "A" IDH "B" IDH "A" IDH "C" IDH "A"
IDH "B"

I have put Hide Duplicates in the properties... which got rid of my
duplicate work center listings, thanks!

I have tried "Can Shrink"... and unless I am putting this in the wrong
place, it is not working.

THANKS SO MUCH FOR THE HELP!!!!!!!!!!


KARL DEWEY said:
it repeats Work Center1 and Shift 1 multiple times.
That is normal. Set the Hide Duplicates property in your report.

The added vertical spacing is what I thought was your problem. You may have
a record with Work Center, Shift, and date without other information to cause
the vertical spacing. Check your data.

:

Currently, it looks like this:

Work Center Shift 5/3/09 5/4/09 5/5/09 (etc)
Work Center1 1 IDH1 IDH1

IDH Descr IDH Descr
Qty Qty
Work Center1 1 IDH2

IDH Descr

Qty
Work Center1 1 IDH3
IDH Descr
Qty
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

In other words, it repeats Work Center1 and Shift 1 multiple times. I want
it to only show up once and scrunch all the IDH, IDH Descr, Qty data in as
much as possible.




:

Your said you wanted it to look like this --
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
Work Center2 1 IDH IDH IDH
IDH Descr IDH Descr IDH Descr
Qty Qty Qty

Where is it putting the extra spaces?

:

Here is my query...

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");

I tried the "Can Shrink" thing in my report.....

:

Post the SQL of your query.

:

Karl;

That doesn't work because of the repeating Work Center and Shift...

I am not so familiar with this, but is there a way to write it as a formula?

Something like:

"if Work Center and Shift are equal, can shrink lines"?

Or can we somehow use "First" of Work Center and Shift at this point?!?

Thanks, as always!!!!!!!!!!

:

In the report you can set the Can Shrink property to Yes.

:

I was hoping that it would look like this:

Work Center Shift 6/1/09 6/2/09
1313A 1st Shift 819923 827693
NailPower Heavy PRS 10.2
OZ RT600
DutyConstrAdh 301ml ADH TRC 12SC

11000 1400
827693
PRS 10.2 OZ RT600
ADH TRC 12SC
22000

I don't want all those blank spaces in there where there may only be one IDH
for that day, because it makes the report really difficult to read and very
long.

Maybe there is a way to format it in the report so that all those blanks are
gone?

I hope that makes sense.......

Thanks again!



:

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?

:

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!!!!!!

:

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");

:

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!!!!




:

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");


:

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!




:

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");


:

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!!!! :-)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top