Multiple Queries on one field

M

Michael

I want to return different values from a field in a table
to multiple fields in a query. ie. the table field
has "Incoming", "Outgoing"... in its data and what I want
to do is run one query to split this data into seperate
tables.
 
K

Ken Snell

You don't tell us much about your table structure, but guessing, you might
use IIf statements in calculated fields to do what you seek:

IncomingValues: IIf([TableField]="Incoming", [TableField], "")

OutgoingValues: IIf([TableField]="Outgoing", [TableField], "")
 
G

Guest

Ken,

The Table has these fields - Name, Call_Tpye, Duration.
Call Tpe has entries like Incoming, Outgoing,
Investigation plus a few others. What I want to do is
query this table and get totals for each Name for each
call type ie, If Name: George, Sum of Duration of
IncomingCalls, Sum of Duration of Outgoing Calls etc.
Query Fields will be Name, Sum_Incoming, Sum_Outgoing,
Sum_Investigation etc. Hope that makes sense.
 
J

John Spencer (MVP)

SELECT [Name], Call_Type, Sum(Duration) as TotalTime
FROM YourTable
GROUP BY [Name], Call_Type

Will give you a vertical query with the data. Is that what you want?

Results:
George Incoming 242
George Outgoing 121
George Investigating 22
Joshua ...

If you want a grid with Names Down the left, Call types across the top and the
totals displayed in the intersection, then take a look at a crosstab query.

Ken,

The Table has these fields - Name, Call_Tpye, Duration.
Call Tpe has entries like Incoming, Outgoing,
Investigation plus a few others. What I want to do is
query this table and get totals for each Name for each
call type ie, If Name: George, Sum of Duration of
IncomingCalls, Sum of Duration of Outgoing Calls etc.
Query Fields will be Name, Sum_Incoming, Sum_Outgoing,
Sum_Investigation etc. Hope that makes sense.
-----Original Message-----
You don't tell us much about your table structure, but guessing, you might
use IIf statements in calculated fields to do what you seek:

IncomingValues: IIf([TableField]="Incoming", [TableField], "")

OutgoingValues: IIf([TableField]="Outgoing", [TableField], "")
Click to expand...
Click to expand...
 
M

Michael

John,

Thanks for that. Looks like it should work for me. I'll
check out crosstabs and try to figure them out myself
before I start asking.

Michael
-----Original Message-----

SELECT [Name], Call_Type, Sum(Duration) as TotalTime
FROM YourTable
GROUP BY [Name], Call_Type

Will give you a vertical query with the data. Is that what you want?

Results:
George Incoming 242
George Outgoing 121
George Investigating 22
Joshua ...

If you want a grid with Names Down the left, Call types across the top and the
totals displayed in the intersection, then take a look at a crosstab query.

[email protected] said:
Ken,

The Table has these fields - Name, Call_Tpye, Duration.
Call Tpe has entries like Incoming, Outgoing,
Investigation plus a few others. What I want to do is
query this table and get totals for each Name for each
call type ie, If Name: George, Sum of Duration of
IncomingCalls, Sum of Duration of Outgoing Calls etc.
Query Fields will be Name, Sum_Incoming, Sum_Outgoing,
Sum_Investigation etc. Hope that makes sense.
-----Original Message-----
You don't tell us much about your table structure, but guessing, you might
use IIf statements in calculated fields to do what you seek:

IncomingValues: IIf([TableField]="Incoming", [TableField], "")

OutgoingValues: IIf([TableField]="Outgoing", [TableField], "")
Click to expand...
Click to expand...
.
Click to expand...
 
Top