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

G

GoBrowns!

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!!!! :)
 
K

KARL DEWEY

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

Duane Hookom

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.
 
G

GoBrowns!

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 said:
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! said:
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...
 
K

KARL DEWEY

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! said:
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 said:
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! said:
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...
 
G

GoBrowns!

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 said:
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! said:
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 said:
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...
 
D

Duane Hookom

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! said:
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 said:
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! said:
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...
 
K

KARL DEWEY

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! said:
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 said:
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! said:
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...
 
G

GoBrowns!

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 said:
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! said:
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 said:
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...
 
K

KARL DEWEY

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! said:
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 said:
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! said:
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...
 
G

GoBrowns!

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!



KARL DEWEY said:
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! said:
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 said:
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...
 
K

KARL DEWEY

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

GoBrowns! said:
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!



KARL DEWEY said:
my query jump from 18 lines to 131 lines long.....
Click to expand...
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! said:
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...
 
G

GoBrowns!

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

KARL DEWEY said:
In the report you can set the Can Shrink property to Yes.

GoBrowns! said:
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!



KARL DEWEY said:
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...
 
K

KARL DEWEY

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

KARL DEWEY said:
In the report you can set the Can Shrink property to Yes.

GoBrowns! said:
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...
 
G

GoBrowns!

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

KARL DEWEY said:
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...
 
K

KARL DEWEY

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.

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...
 
K

KARL DEWEY

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...
 
G

GoBrowns!

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...
 
K

KARL DEWEY

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...
 
G

GoBrowns!

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

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

Is there any way to get the report to look like this? 2
Populate empty cells to use for conversion 1
GETPIVOTDATA #REF! error XL2003/2010 1
Protect cell pls help... 4
Crosstab query dynamic columns ala Duane Hookom 10
Help with modifying report from Duane Hookom - Hook'D on Access website 2
assign the same macro to every line in worksheet 1
How to add an additional summed up Quantity 8

Top