returning 2 summed fields, each one different WHERE criteria

[MontyBurns]

Hi,

tblTopics (simplified)
----------
TopicID autonum
MainGroup text(50)
InReview Yes/No
Completed Yes/No
Weighting Integer

I need to have a report which will display 3 columns: distinct MainGroup
Sum(Weighting) WHERE InReview=True
Sum(Weighting) WHERE Completed=True

Is this possible? I've tried doing a query for each and then UNION these two together etc, but can't seem to get it right.

Any ideas anyone? I'm really stumped with this one!

Thanks,
Burns

 

[gol4]

I think should be able to do this using alias.

ROUGH example below will need to clean up
considerably but

select distinct maingroup,( select Sum(Weighting)from tbltopics as a WHERE InReview=True and a.main group = tbltopics.maingroup)as revsum,
(select Sum(Weighting) from tbltopics as b WHERE Completed=True and b.main group = tbltopics.maingroup)as compsum,
) from tbltopics

 

[MontyBurns]

thanks gol4, you've put me on the right track it seems.

Unfortunately this query is throwing the MS Access error:

Malformed GUID in query expression '{SELECT SUM(Weighting)'.

After reading an article on this, via Google, it seems it may have something to do with Access thinking it's an ODBC call to one of it's native functions.

Anyone any idea why this is happening?

Thanks,
Burns

 

[MontyBurns]

apologies - I had somehow stuck in a { rather than a ( which was causing the problem.

All working now, so thanks gol4, very much appreciated.

Burns