469,909 Members | 2,041 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

Combining two queries

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!
Nov 12 '05 #1
1 5779
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!


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by SomeDude | last post: by
1 post views Thread by ferraro.joseph | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.