Creating A Report using Pivoted Query

[pcbins]

I dont know what Im doing wrong.

I have a table like:
Salesman MONTH PROD_A PROD_B PROD_C
JONES JAN 15 5 1
JONES FEB 14 3 0
JONES MAR 16 6 2
RODRIG JAN 0 20 5
RODRIG FEB 2 16 1
RODRIG MAR 1 21 6
SMITH JAN 5 15 20
SMITH FEB 4 13 17
SMITH MAR 6 16 21

And I want a report that will display the information like:

Salesman: JONES
JAN FEB MAR
PROD A 15 14 16
PROD B 5 3 6
PROD C 1 0 2

Salesman: RODRIG
JAN FEB MAR
PROD A 0 2 1
PROD B 20 16 21
PROD C 5 1 6

Salesman: SMITH
JAN FEB MAR
PROD A 5 4 6
PROD B 15 13 16
PROD C 20 17 21

I have no trouble creating the Pivot table, but have no idea how to get a
report to display this information correctly!

What am I doing wrong?

 

[KARL DEWEY]

Your table is wrong.
Use this union query to make it like below then a crosstab query --
SELECT Salesman, MONTH, "A" AS PROD, PROD_A AS QTY
YourTable
WHERE PROD_A > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_B > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_C > 0;

Better still would be to use a DateTime field instead of the text month name.

Salesman MONTH PROD QTY
JONES JAN A 15
JONES FEB A 14
JONES MAR A 16
RODRIG JAN A 0
RODRIG FEB A 2
RODRIG MAR A 1
SMITH JAN A 5
SMITH FEB A 4
SMITH MAR A 6
JONES JAN A 5
JONES FEB A 3
JONES MAR A 6
RODRIG JAN B 20
RODRIG FEB B 16
RODRIG MAR B 21
SMITH JAN B 15
SMITH FEB B 13
SMITH MAR B 16
JONES JAN C 1
JONES FEB C 0
JONES MAR C 2
RODRIG JAN C 5
RODRIG FEB C 1
RODRIG MAR C 6
SMITH JAN C 20
SMITH FEB C 17
SMITH MAR C 21

 

[pcbins]

Wow, that's going to be a lot of work, just to re-create the table. We have
17 products, some of which they want totals added together since they are
slight variations of the same product.

Then I will have see what I have to do to complete the crosstab query. But,
I guess if that is what needs to be done...

I can easily change the Month to a DateTime, but I dont see how that will
make the process any easier... ?

 

[pcbins]

Ok, I have created a union query to display the information as you have shown
it below.

I have tried several ways to build a crosstab that will display the
information i need, but with no success. Exactly what is the purpose of the
crosstab and how will it help me create the report I need?

 

[KARL DEWEY]

The union query can feed the crosstab without making a table from it for now.

The crosstab will give you this --
Salesman PROD JAN FEB MAR
JONES A 15 14 16
JONES B 5 3 6
JONES C 1 0 2
RODRIG A 0 2 1
RODRIG B 20 16 21
RODRIG C 5 1 6
SMITH A 5 4 6
SMITH B 15 13 16
SMITH C 20 17 21

Then in the report use the Grouping and Sorting with Salesman as group with
header for Salesman text box.

 

[pcbins]

Thank you Karl, I understand now. Thank you so much. My brain has been mush
lately... too much Hulu I guess.

Now can you help me out again?

I need to be able to select one or more Months (or datetimes). I've tried
using the "[Enter Date]" but for some reason this doesnt seem to work with
Crosstab Querys.

 

[pcbins]

Oh, I get this error message when i try to input a date range:
"The Microsoft Jet database engine does not recognize "[Enter Beginning
Date]" as a valid field name or expression."

Perhaps that will help.

Thank you Karl, I understand now. Thank you so much. My brain has been mush
lately... too much Hulu I guess.

Now can you help me out again?

I need to be able to select one or more Months (or datetimes). I've tried
using the "[Enter Date]" but for some reason this doesnt seem to work with
Crosstab Querys.

Your table is wrong.
Use this union query to make it like below then a crosstab query --
SELECT Salesman, MONTH, "A" AS PROD, PROD_A AS QTY
YourTable
WHERE PROD_A > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_B > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_C > 0;

Better still would be to use a DateTime field instead of the text month name.

Salesman MONTH PROD QTY
JONES JAN A 15
JONES FEB A 14
JONES MAR A 16
RODRIG JAN A 0
RODRIG FEB A 2
RODRIG MAR A 1
SMITH JAN A 5
SMITH FEB A 4
SMITH MAR A 6
JONES JAN A 5
JONES FEB A 3
JONES MAR A 6
RODRIG JAN B 20
RODRIG FEB B 16
RODRIG MAR B 21
SMITH JAN B 15
SMITH FEB B 13
SMITH MAR B 16
JONES JAN C 1
JONES FEB C 0
JONES MAR C 2
RODRIG JAN C 5
RODRIG FEB C 1
RODRIG MAR C 6
SMITH JAN C 20
SMITH FEB C 17
SMITH MAR C 21

 

[KARL DEWEY]

Open query in design view, click on QUERY - Parameters. It open another
window. Enter the parameter (best to copy and paste so as not to have a typo)
and datatype. In this case it will be DateTime.

Oh, I get this error message when i try to input a date range:
"The Microsoft Jet database engine does not recognize "[Enter Beginning
Date]" as a valid field name or expression."

Perhaps that will help.

Thank you Karl, I understand now. Thank you so much. My brain has been mush
lately... too much Hulu I guess.

Now can you help me out again?

I need to be able to select one or more Months (or datetimes). I've tried
using the "[Enter Date]" but for some reason this doesnt seem to work with
Crosstab Querys.

Your table is wrong.
Use this union query to make it like below then a crosstab query --
SELECT Salesman, MONTH, "A" AS PROD, PROD_A AS QTY
YourTable
WHERE PROD_A > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_B > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_C > 0;

Better still would be to use a DateTime field instead of the text month name.

Salesman MONTH PROD QTY
JONES JAN A 15
JONES FEB A 14
JONES MAR A 16
RODRIG JAN A 0
RODRIG FEB A 2
RODRIG MAR A 1
SMITH JAN A 5
SMITH FEB A 4
SMITH MAR A 6
JONES JAN A 5
JONES FEB A 3
JONES MAR A 6
RODRIG JAN B 20
RODRIG FEB B 16
RODRIG MAR B 21
SMITH JAN B 15
SMITH FEB B 13
SMITH MAR B 16
JONES JAN C 1
JONES FEB C 0
JONES MAR C 2
RODRIG JAN C 5
RODRIG FEB C 1
RODRIG MAR C 6
SMITH JAN C 20
SMITH FEB C 17
SMITH MAR C 21

:

I dont know what Im doing wrong.

I have a table like:
Salesman MONTH PROD_A PROD_B PROD_C
JONES JAN 15 5 1
JONES FEB 14 3 0
JONES MAR 16 6 2
RODRIG JAN 0 20 5
RODRIG FEB 2 16 1
RODRIG MAR 1 21 6
SMITH JAN 5 15 20
SMITH FEB 4 13 17
SMITH MAR 6 16 21

And I want a report that will display the information like:

Salesman: JONES
JAN FEB MAR
PROD A 15 14 16
PROD B 5 3 6
PROD C 1 0 2

Salesman: RODRIG
JAN FEB MAR
PROD A 0 2 1
PROD B 20 16 21
PROD C 5 1 6

Salesman: SMITH
JAN FEB MAR
PROD A 5 4 6
PROD B 15 13 16
PROD C 20 17 21

I have no trouble creating the Pivot table, but have no idea how to get a
report to display this information correctly!

What am I doing wrong?

 

[pcbins]

I guess I dont understand. Which query do I do this in? And what parameter
are you talking about copying and pasting?

I am thinking perhaps it is the Union Query that is having trouble with this
function.

I have tried creating a query that will pull only specified dates from the
main table and then using the Union Query to rearrange the table, but It only
asks for one date, as such: "Enter Parameter value: Sales Table.Date: " Which
is fine if I need a report for only a specified DateTime. But if I need a
range, it wont work.

I have also tried editing the crosstab query to ask for a date range from
the union query, but that's when I get the afore mentioned error, and I do
not get any results at all.

Anyway, I am not sure which of these query's I need to set the DateTime
parameter and exactly what parameter is to be set as such!

Open query in design view, click on QUERY - Parameters. It open another
window. Enter the parameter (best to copy and paste so as not to have a typo)
and datatype. In this case it will be DateTime.

Oh, I get this error message when i try to input a date range:
"The Microsoft Jet database engine does not recognize "[Enter Beginning
Date]" as a valid field name or expression."

Perhaps that will help.

Thank you Karl, I understand now. Thank you so much. My brain has been mush
lately... too much Hulu I guess.

Now can you help me out again?

I need to be able to select one or more Months (or datetimes). I've tried
using the "[Enter Date]" but for some reason this doesnt seem to work with
Crosstab Querys.


:

Your table is wrong.
Use this union query to make it like below then a crosstab query --
SELECT Salesman, MONTH, "A" AS PROD, PROD_A AS QTY
YourTable
WHERE PROD_A > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_B > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_C > 0;

Better still would be to use a DateTime field instead of the text month name.

Salesman MONTH PROD QTY
JONES JAN A 15
JONES FEB A 14
JONES MAR A 16
RODRIG JAN A 0
RODRIG FEB A 2
RODRIG MAR A 1
SMITH JAN A 5
SMITH FEB A 4
SMITH MAR A 6
JONES JAN A 5
JONES FEB A 3
JONES MAR A 6
RODRIG JAN B 20
RODRIG FEB B 16
RODRIG MAR B 21
SMITH JAN B 15
SMITH FEB B 13
SMITH MAR B 16
JONES JAN C 1
JONES FEB C 0
JONES MAR C 2
RODRIG JAN C 5
RODRIG FEB C 1
RODRIG MAR C 6
SMITH JAN C 20
SMITH FEB C 17
SMITH MAR C 21

:

I dont know what Im doing wrong.

I have a table like:
Salesman MONTH PROD_A PROD_B PROD_C
JONES JAN 15 5 1
JONES FEB 14 3 0
JONES MAR 16 6 2
RODRIG JAN 0 20 5
RODRIG FEB 2 16 1
RODRIG MAR 1 21 6
SMITH JAN 5 15 20
SMITH FEB 4 13 17
SMITH MAR 6 16 21

And I want a report that will display the information like:

Salesman: JONES
JAN FEB MAR
PROD A 15 14 16
PROD B 5 3 6
PROD C 1 0 2

Salesman: RODRIG
JAN FEB MAR
PROD A 0 2 1
PROD B 20 16 21
PROD C 5 1 6

Salesman: SMITH
JAN FEB MAR
PROD A 5 4 6
PROD B 15 13 16
PROD C 20 17 21

I have no trouble creating the Pivot table, but have no idea how to get a
report to display this information correctly!

What am I doing wrong?

 

[pcbins]

OK, I might be getting a little closer to getting this report done.

I now have a Crosstab with the selected date range.
Now how to I get it into a report? When I try to use Report Wizard and
select my crosstab query, there are no available fields!

Open query in design view, click on QUERY - Parameters. It open another
window. Enter the parameter (best to copy and paste so as not to have a typo)
and datatype. In this case it will be DateTime.

Oh, I get this error message when i try to input a date range:
"The Microsoft Jet database engine does not recognize "[Enter Beginning
Date]" as a valid field name or expression."

Perhaps that will help.

Thank you Karl, I understand now. Thank you so much. My brain has been mush
lately... too much Hulu I guess.

Now can you help me out again?

I need to be able to select one or more Months (or datetimes). I've tried
using the "[Enter Date]" but for some reason this doesnt seem to work with
Crosstab Querys.


:

Your table is wrong.
Use this union query to make it like below then a crosstab query --
SELECT Salesman, MONTH, "A" AS PROD, PROD_A AS QTY
YourTable
WHERE PROD_A > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_B > 0
UNION ALL SELECT Salesman, MONTH, "B" AS PROD, PROD_B AS QTY
YourTable
WHERE PROD_C > 0;

Better still would be to use a DateTime field instead of the text month name.

Salesman MONTH PROD QTY
JONES JAN A 15
JONES FEB A 14
JONES MAR A 16
RODRIG JAN A 0
RODRIG FEB A 2
RODRIG MAR A 1
SMITH JAN A 5
SMITH FEB A 4
SMITH MAR A 6
JONES JAN A 5
JONES FEB A 3
JONES MAR A 6
RODRIG JAN B 20
RODRIG FEB B 16
RODRIG MAR B 21
SMITH JAN B 15
SMITH FEB B 13
SMITH MAR B 16
JONES JAN C 1
JONES FEB C 0
JONES MAR C 2
RODRIG JAN C 5
RODRIG FEB C 1
RODRIG MAR C 6
SMITH JAN C 20
SMITH FEB C 17
SMITH MAR C 21

:

I dont know what Im doing wrong.

I have a table like:
Salesman MONTH PROD_A PROD_B PROD_C
JONES JAN 15 5 1
JONES FEB 14 3 0
JONES MAR 16 6 2
RODRIG JAN 0 20 5
RODRIG FEB 2 16 1
RODRIG MAR 1 21 6
SMITH JAN 5 15 20
SMITH FEB 4 13 17
SMITH MAR 6 16 21

And I want a report that will display the information like:

Salesman: JONES
JAN FEB MAR
PROD A 15 14 16
PROD B 5 3 6
PROD C 1 0 2

Salesman: RODRIG
JAN FEB MAR
PROD A 0 2 1
PROD B 20 16 21
PROD C 5 1 6

Salesman: SMITH
JAN FEB MAR
PROD A 5 4 6
PROD B 15 13 16
PROD C 20 17 21

I have no trouble creating the Pivot table, but have no idea how to get a
report to display this information correctly!

What am I doing wrong?

 

[John... Visio MVP]

I provide help with Access applications for a reasonable fee. I could put
this all together for you for a very modest fee. Contact me if you want my
help.

Steve
(e-mail address removed)

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)

Yes, you are right but a database is the correct tool to use not a
spreadsheet.

Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP