query question

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I'm having a lot of trouble getting my question answered,
so I'll start by asking this:

I have a table that has 3 fields, RecordID, UtilityType,
and Consumption. Normally I would have 3 different
utility types, gas, water, and electric. Can I run a
query that will show per RecordID, each utility type in
their own column? I don't want to use a crosstab query,
because I want to include other values later on and not
just consumption(actual cost of that consumption, for
example).

For example, a table may have this
RecordID UtilityType Consumption
1 Gas 1000
1 Water 500
2 Gas 950
2 Electric 300
2 Water 525

And get a result of

RecordID Gas Water Electric
1 1000 500 0
2 950 525 300

Thanks to anyone who can help me. I've been struggling
with this for a few days now.
 
Try this SQL statement:

SELECT [RecordID], DSum("Consumption", "TableName", "[RecordID]=" &
[RecordID] & " And [UtilityType]='Gas'") AS Gas,
DSum("Consumption", "TableName", "[RecordID]=" & [RecordID] & " And
[UtilityType]='Water'") AS Water,
DSum("Consumption", "TableName", "[RecordID]=" & [RecordID] & " And
[UtilityType]='Electric'") AS Electric
FROM TableName
GROUP BY [RecordID]
ORDER BY [RecordID];

TableName should be replaced by the actual name of the table (throughout
this SQL).
 
You are trying to represent N dimensions in a two dimensional space.
Not easy.

You might try running a query on your base table to concatenate the
fields you are interested into one superfield, then run a cross tab on
that
eg
select RecordID, UtilityType, ([Consumption] & vbcrLF & [Cost]) as
Xtab from tblUtilityUse;

You may need to explicitly convert the fields to String, or even use a
function to get the vbCrLf in there. Not sure. Never tried.

Use the output of that for the Pivot table.

HTH
 
Alternatively, you can try this:

SELECT [RecordID], DLookup("Consumption", "TableName", "[RecordID]=" &
[RecordID] & " And [UtilityType]='Gas'") AS Gas,
DLookup("Consumption", "TableName", "[RecordID]=" & [RecordID] & " And
[UtilityType]='Water'") AS Water,
DLookup("Consumption", "TableName", "[RecordID]=" & [RecordID] & " And
[UtilityType]='Electric'") AS Electric
FROM TableName
GROUP BY [RecordID]
ORDER BY [RecordID];

--
Ken Snell
<MS ACCESS MVP>

Ken Snell said:
Try this SQL statement:

SELECT [RecordID], DSum("Consumption", "TableName", "[RecordID]=" &
[RecordID] & " And [UtilityType]='Gas'") AS Gas,
DSum("Consumption", "TableName", "[RecordID]=" & [RecordID] & " And
[UtilityType]='Water'") AS Water,
DSum("Consumption", "TableName", "[RecordID]=" & [RecordID] & " And
[UtilityType]='Electric'") AS Electric
FROM TableName
GROUP BY [RecordID]
ORDER BY [RecordID];

TableName should be replaced by the actual name of the table (throughout
this SQL).
--
Ken Snell
<MS ACCESS MVP>


Rob said:
I'm having a lot of trouble getting my question answered,
so I'll start by asking this:

I have a table that has 3 fields, RecordID, UtilityType,
and Consumption. Normally I would have 3 different
utility types, gas, water, and electric. Can I run a
query that will show per RecordID, each utility type in
their own column? I don't want to use a crosstab query,
because I want to include other values later on and not
just consumption(actual cost of that consumption, for
example).

For example, a table may have this
RecordID UtilityType Consumption
1 Gas 1000
1 Water 500
2 Gas 950
2 Electric 300
2 Water 525

And get a result of

RecordID Gas Water Electric
1 1000 500 0
2 950 525 300

Thanks to anyone who can help me. I've been struggling
with this for a few days now.
Click to expand...
Click to expand...
 
Back
Top