Calculate Totals for Each Person?

[kev100 via AccessMonster.com]

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

Oh....yes...I see what you mean. Those tables will simply be referenced in
order to properly group the working data. That may be possible.

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

If the Function is all that is possible, should it be entered into the same
area as the SQL code?

The previous items have just been entered into the SQL View section in the
Design mode of a query...and work fine.

Does the function go in the same area....to be executed with the query....or
somewhere more general to be triggered as soon as the MDB is opened?

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

(starting here definately goes to the SQL view area....just not sure about
the above function)

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

Again...thanks very much for your help.

 

[Duane Hookom]

You would need to open a new, blank module and paste the code into the
module window. Then save the module as "modGetFunctions". You can then use
the function in queries, control sources, or any other place a function can
be used.

--
Duane Hookom
MS Access MVP
--

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

Oh....yes...I see what you mean. Those tables will simply be referenced
in
order to properly group the working data. That may be possible.

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

If the Function is all that is possible, should it be entered into the
same
area as the SQL code?

The previous items have just been entered into the SQL View section in the
Design mode of a query...and work fine.

Does the function go in the same area....to be executed with the
query....or
somewhere more general to be triggered as soon as the MDB is opened?

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

(starting here definately goes to the SQL view area....just not sure about
the above function)

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

Again...thanks very much for your help.

 

[kev100 via AccessMonster.com]

Duane,

I'm giving the extra table method a try. If I am understanding it...it
appears like it would also be customizeable (if ever needed) simply by
changing a color's "color group."

Will I need to include an entry for every color-type, or just the ones to be
combined into 1 column?

Thanks very much.

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

TRANSFORM Count(customers.country) AS CountOfcountry
SELECT customers.firstname, Sum(Abs(Instr("Blue,White",[ItemColor])=0)) as

 

[kev100 via AccessMonster.com]

I went ahead and gave the additional table method a try.

I only made entries in the Colors table (tblColors) for the Color types to be
combined into 1 column (the Blue and Light Blue.....both having a ColorGroup
entry of Blue).

I made sure to add the table to the query's set of tables (shows in the gray
area in Design View)

When the requery is run...I get the error:

"The Microsoft Jet database engine cannot find the input table or query
'tblColors'. Make sure it exits and that its name is spelled correctly."

I double-checked the spelling...it seems okay.

Thanks

 

[Duane Hookom]

Please provide the SQL view of your query.