Multiple Queries on one field

  • Thread starter Thread starter Michael
  • Start date Start date
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.
 
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], "")
 
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.
 
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...
 
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...
 
Back
Top