Combining two queries

[Bruce MacDonald]

I've got a question/request for the SQL gurus.
I'm building a model of bandwidth demand in MS Access and want to get
aggregated results for demand at each PCP in each time period.

The two queries below are used to count the number of households of each
Socio-Economic Type (each postcode has been allocated an SE-Type), and use
that to count the number of connections requiring a bandwidth less than 512
that will be required at the PCP in each month, and to put the results into
a table called PCP-Demands.

Unfortunately, my SQLing skills are somewhat limited, and I couldn't figure
out how to do this as a single query, so have had to put results into a temp
table, then pull them out again into my results table. It works, but
obviously it takes longer to run - I currently have a machine beside me that
has been grinding away since yesterday afternoon, and I don't expect to
finish today.

So, what I'm looking for is a way to combine the two queries below into one
query.
I'm sure it should be easy, but it's got me beat. If anyone could see their
way clear to combine these two queries for me, I'd be most appreciative.

Cheers,
Bruce.

Query to make temp table:
====================
SELECT SED.Date, PC.PCP, SUM(pc.HouseHolds * SED.[% <512]) AS Connections
INTO TempConnectionCount
FROM ([SE-Demands] AS SED INNER JOIN [PostCodes Test] AS PC
ON PC.[SE-Type]=SED.[SE-Type])
GROUP BY [SED].Date, PC.PCP;
====================

Query to put temp results into final table
====================
UPDATE [PCP-Demands], TempConnectionCount
SET [PCP-Demands].[Count <512] = TempConnectionCount.Connections
WHERE TempConnectionCount.[Date] = [PCP-Demands].[Date]
AND TempConnectionCount.[PCP] = [PCP-Demands].[PCP];
====================

As a follow up, the closest I've managed to get so far is:

Attempt to combine the two queries
====================
UPDATE [PCP-Demands], [SE-Demands], [PostCodes Test] SET
[PCP-Demands].[Count <512] = (
SELECT SUM([PostCodes Test].HouseHolds*[SE-Demands].[% <512])

FROM ([SE-Demands] INNER JOIN [PostCodes Test] ON [PostCodes
Test].[SE-Type]=[SE-Demands].[SE-Type])
)
WHERE [SE-Demands].Date=[PCP-Demands].[Date]
And [PostCodes Test].PCP=[PCP-Demands].[PCP];

=========================
which comes up with the error "Operation must use an updateable query"
Suggestions really would be appreciated!

[Tom van Stiphout]

On Thu, 6 Nov 2003 11:32:36 +0000 (UTC), "Bruce MacDonald"
<no***@noone.com> wrote:

I don't think there is a better solution. I use the same method you
are. I agree Access is a bit stupid that it can't see I don't want to
modify data on the Group By side of the query (which legitimally is
not-updateable).
To optimize for speed, you may need an index on
TempConnectionCount.[Date] and TempConnectionCount.PCP.

-Tom.

I've got a question/request for the SQL gurus.
I'm building a model of bandwidth demand in MS Access and want to get
aggregated results for demand at each PCP in each time period.

The two queries below are used to count the number of households of each
Socio-Economic Type (each postcode has been allocated an SE-Type), and use
that to count the number of connections requiring a bandwidth less than 512
that will be required at the PCP in each month, and to put the results into
a table called PCP-Demands.

Unfortunately, my SQLing skills are somewhat limited, and I couldn't figure
out how to do this as a single query, so have had to put results into a temp
table, then pull them out again into my results table. It works, but
obviously it takes longer to run - I currently have a machine beside me that
has been grinding away since yesterday afternoon, and I don't expect to
finish today.

So, what I'm looking for is a way to combine the two queries below into one
query.
I'm sure it should be easy, but it's got me beat. If anyone could see their
way clear to combine these two queries for me, I'd be most appreciative.

Cheers,
Bruce.

Query to make temp table:
====================
SELECT SED.Date, PC.PCP, SUM(pc.HouseHolds * SED.[% <512]) AS Connections
INTO TempConnectionCount
FROM ([SE-Demands] AS SED INNER JOIN [PostCodes Test] AS PC
ON PC.[SE-Type]=SED.[SE-Type])
GROUP BY [SED].Date, PC.PCP;
====================

Query to put temp results into final table
====================
UPDATE [PCP-Demands], TempConnectionCount
SET [PCP-Demands].[Count <512] = TempConnectionCount.Connections
WHERE TempConnectionCount.[Date] = [PCP-Demands].[Date]
AND TempConnectionCount.[PCP] = [PCP-Demands].[PCP];
====================

As a follow up, the closest I've managed to get so far is:

Attempt to combine the two queries
====================
UPDATE [PCP-Demands], [SE-Demands], [PostCodes Test] SET
[PCP-Demands].[Count <512] = (
SELECT SUM([PostCodes Test].HouseHolds*[SE-Demands].[% <512])

FROM ([SE-Demands] INNER JOIN [PostCodes Test] ON [PostCodes
Test].[SE-Type]=[SE-Demands].[SE-Type])
)
WHERE [SE-Demands].Date=[PCP-Demands].[Date]
And [PostCodes Test].PCP=[PCP-Demands].[PCP];

=========================
which comes up with the error "Operation must use an updateable query"
Suggestions really would be appreciated!