Calculate Totals for Each Person?

[kev100 via AccessMonster.com]

I'm still kinda new to Access and hope to do as much of this using the
WIzards as possible.

I have a very simple table with 2 fields.

1 is FIrst Name
2 is Item Color

There are hundreds of entries. One per item purchase. There is not a unique
field.

Basically, it looks like:

Jane Blue
Tom Green
Mark Red
Kim White
Tom Red
Kim White
Jane Red
Kim Blue
Mark Red
(etc)

I have a query which simply sorts by Name then Color.

I'm hoping to get the report to simply display Each name with the Totals for
each Color. Like....

Name Blue Green Red White

Jane 1 0 1
0
Tom 0 1 1
0
Mark 0 0 2
0
Kim 1 0 0
2

Thanks

 

[kev100 via AccessMonster.com]

Shoot....the post did not display correctly.

The number under each Name should be in the White colum of the preceeding
line. Hope this makes sense.

 

[strive4peace]

Hi Kev,

What you are looking for is a Crosstab query...

click on the New button in Queries, choose the Crosstab
Query Wizard and follow the directions

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[kev100 via AccessMonster.com]

Wow....thanks so much....that worked like a charm.

One last function would be like icing on the cake.....

Would it be difficult to combine 2 or more of the color-totals into an
"Other" category?

Example......with the below table

Jane Blue
Tom Green
Mark Red
Kim White
Tom Red
Kim White
Jane Red
Kim Blue
Mark Red
(etc)

To display like....

Name Blue White Other ( total of any other
colors)

Jane 1 0 1

Tom 0 0 2

Mark 0 0 2

Kim 1 2 0

Thanks Again..

Hi Kev,

What you are looking for is a Crosstab query...

click on the New button in Queries, choose the Crosstab
Query Wizard and follow the directions

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Shoot....the post did not display correctly.

[quoted text clipped - 42 lines]

 

[Duane Hookom]

You would need to set the Column Headings property to only the colors you
want to display individually. Then add another Row Heading like:

Field: Abs(Instr("Blue,White",[Item Color])=0))
Total: Sum
Crosstab: Row Heading

--
Duane Hookom
MS Access MVP
--

Wow....thanks so much....that worked like a charm.

One last function would be like icing on the cake.....

Would it be difficult to combine 2 or more of the color-totals into an
"Other" category?

Example......with the below table

Jane Blue
Tom Green
Mark Red
Kim White
Tom Red
Kim White
Jane Red
Kim Blue
Mark Red
(etc)

To display like....

Name Blue White Other ( total of any other
colors)

Jane 1 0 1

Tom 0 0 2

Mark 0 0 2

Kim 1 2 0

Thanks Again..

Hi Kev,

What you are looking for is a Crosstab query...

click on the New button in Queries, choose the Crosstab
Query Wizard and follow the directions

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Shoot....the post did not display correctly.

[quoted text clipped - 42 lines]

Thanks

 

[kev100 via AccessMonster.com]

Field: Abs(Instr("Blue,White",[Item Color])=0))

Total: Sum
Crosstab: Row Heading

Thanks....I'm (kind of) beginning to see how this fits.....but.....I'm still
not sure just what the above will display....

....Will it create multiple column headings (Blue, White, etc)....or...
....Is it used to create a column which contains the combined totals for all
other colors (in my example, a column with the title of "Other" that would
display the total count of all orders for that person that were either Red or
Green).

Thanks

 

[Duane Hookom]

You would want to set your column headings property to
Column Headings: "Blue","White"
since those are the columns you want to see individually. All other colors
will be rolled into the other column.
Field: OtherColors: Abs(Instr("Blue,White",[Item Color])=0))

--
Duane Hookom
MS Access MVP
--

Field: Abs(Instr("Blue,White",[Item Color])=0))
Total: Sum
Crosstab: Row Heading

Thanks....I'm (kind of) beginning to see how this fits.....but.....I'm
still
not sure just what the above will display....

...Will it create multiple column headings (Blue, White, etc)....or...
...Is it used to create a column which contains the combined totals for
all
other colors (in my example, a column with the title of "Other" that would
display the total count of all orders for that person that were either Red
or
Green).

Thanks

 

[kev100 via AccessMonster.com]

You would want to set your column headings property to

Column Headings: "Blue","White"
since those are the columns you want to see individually.

Thanks Very Much...that is beginning to show results.

What I did was change :

Field: Expr1: Abs(InStr("Blue, White",[Item Color])=0)
Table: Customers
Total: Group By
Crosstab: Column Heading (nothing in properties section)

from what was:

Field: Item Color
Table: Customers
Total: Group By
Crosstab: Column Heading (nothing in properties section)

The query runs fine. However, what it outputs is.....

3 columns.

The 1st is "First Name" and lists the names correctly
The 2nd is "0" and displays the Combined totals of all Blue and White orders.
The 3rd is "1" and displays the combined totals of all other colors.

When I try to put "Blue", "White" in the properties section of "column
heading" (in the "column headings" field) error will occur when running
which says:

"Data type mismatch in critera expression"

What I'm hoping is that the query output will display:

4 Columns.

The 1st: Heading of "First Name" and lists the name on each order
The 2nd: Heading of "Blue" and has the total of all Blue orders
The 3rd: Heading of "White" and has the total of all White orders
The 4th: Heading of "Other" and has the combined totals of any other colors


But....no matter what I try in the properties section, it does not seem to
want to display those headings.

(note: "Other" is not the name of any field in the table....the table only
has 2 fields named: "First Name" and "Item Color"

Thanks very much for your help.

 

[Duane Hookom]

Just post the SQL view of your crosstab query. The Item Color-Crosstab:
Column Heading wasn't supposed to be removed or replaced.

--
Duane Hookom
MS Access MVP
--

You would want to set your column headings property to
Column Headings: "Blue","White"
since those are the columns you want to see individually.

Thanks Very Much...that is beginning to show results.

What I did was change :

Field: Expr1: Abs(InStr("Blue, White",[Item Color])=0)
Table: Customers
Total: Group By
Crosstab: Column Heading (nothing in properties section)

from what was:

Field: Item Color
Table: Customers
Total: Group By
Crosstab: Column Heading (nothing in properties section)

The query runs fine. However, what it outputs is.....

3 columns.

The 1st is "First Name" and lists the names correctly
The 2nd is "0" and displays the Combined totals of all Blue and White
orders.
The 3rd is "1" and displays the combined totals of all other colors.

When I try to put "Blue", "White" in the properties section of "column
heading" (in the "column headings" field) error will occur when running
which says:

"Data type mismatch in critera expression"

What I'm hoping is that the query output will display:

4 Columns.

The 1st: Heading of "First Name" and lists the name on each order
The 2nd: Heading of "Blue" and has the total of all Blue orders
The 3rd: Heading of "White" and has the total of all White orders
The 4th: Heading of "Other" and has the combined totals of any other
colors


But....no matter what I try in the properties section, it does not seem to
want to display those headings.

(note: "Other" is not the name of any field in the table....the table only
has 2 fields named: "First Name" and "Item Color"

Thanks very much for your help.

 

[kev100 via AccessMonster.com]

Just post the SQL view of your crosstab query. The Item Color-Crosstab:
Column Heading wasn't supposed to be removed or replaced.

Thanks......

Here's the SQL for the First Query try.....

It displays a line for every order, and has a colomn for the customers first
name and one for every color, containing the correct totals for each.

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT customers.itemcolor;


This the the SQL for those changes which did consolodate some of the column
totals...but I could not get it to display correctly...

(note...the all customers have a country code field that is simply
automatically there....it is not used for anything else....I'm not sure why
it appears here...but it just automatically does).

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT Abs(InStr("Blue, White",[itemcolor])=0);

This displays the columns:

The 1st is "First Name" and lists the names correctly
The 2nd is "0" and displays the Combined totals of all Blue and White orders.
The 3rd is "1" and displays the combined totals of all other colors.


Thanks very much for looking this over.

 

[kev100 via AccessMonster.com]

(this is a repost of the above.....after reading through it....it could be
formatted to be a bit more clear)

Here's the SQL for the First Query try.....

It displays a line for every order, and has a colomn for the customers first
name and one for every color, containing the correct totals for each.

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT customers.itemcolor;

(note...the all customers have a country code field that is simply
automatically there....it is not used for anything else....I'm not sure why
it appears here...but it just automatically does).




------------------------------------------------------------------------------


This the the SQL for those changes which did consolodate some of the column
totals...but I could not get it to display correctly...


TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT Abs(InStr("Blue, White",[itemcolor])=0);

This displays the columns:

The 1st is "First Name" and lists the names correctly
The 2nd is "0" and displays the Combined totals of all Blue and White orders.
The 3rd is "1" and displays the combined totals of all other colors.

 

[Duane Hookom]

This is what I expected to see:

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as
OtherColors
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT customers.itemcolor IN ("Blue","White");

 

[strive4peace]

Hi Duane,

Thanks for your help on this -- I would have approached it
differently (VBA function) but your way is much better as it
leaves the crunching in the query! I am learning so much
from you, thank you

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[kev100 via AccessMonster.com]

This is what I expected to see:

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as
OtherColors
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT customers.itemcolor IN ("Blue","White");

Duane,

I think this is working....will run it with a few more tables tomorrow.

I really....REALLY appreciate your help.

I hope I can return the favor someday with an equally valuable solution.

Thanks VERY much !

Kev

 

[kev100 via AccessMonster.com]

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as
OtherColors
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT customers.itemcolor IN ("Blue","White");

Duane,

That is indeed working Quite well.

I came across one additional item this am, though.

This displays 3 columns just as needed. Blue, White, and Other Colors...all
with the correct totals.

But....I found out that one of the colors that often appears is "Light Blue"..
..
.....BUT......the Light Blue totals need to be combined with the Blue totals...
and simply appear in the Blue column.

I guess it is sort of like the Blue is a type of "other" column as well.
I'm still hoping for just 3 Columns:

1st Column: Blue --- with the combined totals of any Blue or Light Blue
orders.
2nd Column: White --- with the total of any White orders
3rd Column: Other Colors --- with the combined totals of any other colors.

Should I simply add a second version of the line....

SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as
OtherColors....

....but alter it in such a way that it only deals with Blue and Light Blue
values?

(If so.....would also need to remove the references to "Blue" in the first
line?)

Again...thanks very much.

 

[Duane Hookom]

The more specifications you add the more I think you need a category field
added to a table of colors. You should be managing records/data, not
expressions:

tblColors
Color ColorGroup
=========== ===========
Blue Blue
Light Blue Blue
White White
Ivory White
Off White White

Then try something like this:

TRANSFORM Count(Customers.Country) AS CountOfCountry
SELECT Customers.FirstName
FROM tblColors RIGHT JOIN Customers ON tblColors.Color = Customers.ItemColor
GROUP BY Customers.FirstName
PIVOT Nz([ColorGroup],"Other");

--
Duane Hookom
MS Access MVP
--

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as
OtherColors
FROM customers
GROUP BY customers.firstname
ORDER BY customers.firstname
PIVOT customers.itemcolor IN ("Blue","White");

Duane,

That is indeed working Quite well.

I came across one additional item this am, though.

This displays 3 columns just as needed. Blue, White, and Other
Colors...all
with the correct totals.

But....I found out that one of the colors that often appears is "Light
Blue"..
.
....BUT......the Light Blue totals need to be combined with the Blue
totals...
and simply appear in the Blue column.

I guess it is sort of like the Blue is a type of "other" column as well.
I'm still hoping for just 3 Columns:

1st Column: Blue --- with the combined totals of any Blue or Light Blue
orders.
2nd Column: White --- with the total of any White orders
3rd Column: Other Colors --- with the combined totals of any other colors.

Should I simply add a second version of the line....

SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as
OtherColors....

...but alter it in such a way that it only deals with Blue and Light Blue
values?

(If so.....would also need to remove the references to "Blue" in the first
line?)

Again...thanks very much.

 

[kev100 via AccessMonster.com]

The more specifications you add the more I think you need a category field
added to a table of colors. You should be managing records/data, not
expressions:

Dividing this up initially would make these reports easier.

But...I don't have any control over the tables. They actually come from
another source and are used in a type of customer contact / crm application.

Just having those color totals in columns....even with the Light Blue
seperated...(I just added a Light Blue entry to your code and it creates a
4th column) is Fantastic.

Using that output, I may be able to combine those 2 totals into one column
using a report. If so, just being able to display the numbers in the manner
will do the trick.

Thanks

 

[Duane Hookom]

Are you suggesting that you can create reports and queries but not tables?

 

[kev100 via AccessMonster.com]

Are you suggesting that you can create reports and queries but not tables?
[quoted text clipped - 16 lines]

Thanks

Yes....as far as that core table goes. It's coming from a type of call
center app that is really set in stone, so to speak. I can get an up-to-the-
minute snap-shot copy of the table pretty easily....and make reports, etc
from that (Access is actually Linking to the table).

This copy-table / generate query report is down to 1 click. (a copy of the
table is made almost every time this query is run....in order to get the most
recent data).

The copy is made because this call center app is so touchy that any other
external program doing anything to its tables (other than copying them)
causes problems.

This final item about combining the Blue and Light-Blue totals into one
column jumped up at the last minute. I wish the 'Light Blue" orders could
just be logged as "Blue" to begin with....but, at least its the last thing.

The way it is currently is much better than otherwise. The Blue and Light
Blue columns may have to just remain seperate.....it may not be that big a
deal for user to just combine the totals themselves, as needed.

 

[Duane Hookom]

You don't need to create a table in the call center app. Just create a table
in your reporting mdb (or another mdb and attach the table).

If this isn't possible, you can create a simple function like:

Function GetColorGroup(pstrColor As String) As String
Select Case pstrColor
Case "Blue", "Light Blue"
GetColorGroup = "Blue"
Case "White", "Ivory", "Off White"
GetColorGroup = "White"
Case Else
GetColorGroup = "Other"
End Select
End Function


TRANSFORM Count(Customers.Country) AS CountOfCountry
SELECT Customers.FirstName
FROM Customers
GROUP BY Customers.FirstName
PIVOT GetColorGroup([ItemColor]);

--
Duane Hookom
MS Access MVP
--

Are you suggesting that you can create reports and queries but not tables?

The more specifications you add the more I think you need a category
field
added to a table of colors. You should be managing records/data, not

[quoted text clipped - 16 lines]

Thanks

Yes....as far as that core table goes. It's coming from a type of call
center app that is really set in stone, so to speak. I can get an
up-to-the-
minute snap-shot copy of the table pretty easily....and make reports, etc
from that (Access is actually Linking to the table).

This copy-table / generate query report is down to 1 click. (a copy of
the
table is made almost every time this query is run....in order to get the
most
recent data).

The copy is made because this call center app is so touchy that any other
external program doing anything to its tables (other than copying them)
causes problems.

This final item about combining the Blue and Light-Blue totals into one
column jumped up at the last minute. I wish the 'Light Blue" orders could
just be logged as "Blue" to begin with....but, at least its the last
thing.

The way it is currently is much better than otherwise. The Blue and Light
Blue columns may have to just remain seperate.....it may not be that big a
deal for user to just combine the totals themselves, as needed.