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

  • Thread starter Thread starter GoBrowns!
  • Start date Start date
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!!!! :-)
 
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");
 
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");
 
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.
 
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.
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top