Microsoft Query - Field Names in Subquery

[bluebottle]

I know microsoft query doesnt do field names very well, but it has never been a problem before as I could add them in to the SQL code in excel and they would work. But now it is causing me a problem when I use aggregate functions in subqueries.

Trying to use a generic example, if you have a table of soccer player names and ids, along with a table of goal times and player ids, the following query:

SELECT a.name, b.goals_scored
FROM players a
LEFT JOIN
(SELECT c.id, count(*) AS goals_scored FROM goals c GROUP BY c.id) b
ON a.id = b.id

will return the error 'Column b.goals_scored does not exist'.

Is there any way around this issue? I just need to be able to identify the field created by the aggregate function in the main query. It is causing me major hassles!

(This is a postgresql database by the way)

 

[Fazza]

Hi

One can use the GUI in MS Query to create & edit joins. It is described in MS Query help - suggest searching for help on joins. When using the GUI, add the extra table/s either in the earlier wizard (en route to MS Query) or in MS Query. There is an add table button.

Or just write the SQL in directly. (Or via VBA.)

Try something like below (which I copied from MS Query). HTH, Fazza

SELECT a.name, count(b.id) AS [goals_scored]
FROM {oj players a LEFT OUTER JOIN goals b ON a.id = b.id}
GROUP BY b.id, a.name

 

[Fazza]

PS / caveat / nota bena / whatever !

I've done this entirely within Excel (source data). Not postgresl

F