Using query results as criteria in another query

[squallleonhart]

Hello,
I'm a fairly inexperienced Access user and am attempting to sum up a column from a table using the results of another query as criteria.
More specifically I created a query that returns two dates for each group ID from a certain block of group ID's. I want to create a query that sums up a column for these same group ID's from a different table between the two dates returned by the previous query. I'm not all that comfortable with writing SQL so I've been trying to do this in design view in Access, which may be part of the problem. I feel like I may need a nested sub-query.

The attempts I've made to create this have come up empty.

Any help would be greatly appreciated!!

Thank you

 

[Joe4]

I would recommend joining the Table and Query on the Group ID field.

Then just add criteria under your Date field from your Table Date, i.e.

(>=[Query].[Date1]) And (<=[Query].[Date2])

 

[squallleonhart]

Thanks for your quick response!
This is one of the things I had tried. I get an error when I try to run the new query that states "Your query does not include the specified expression [criteria code] as part of an aggregate function."

 

[Joe4]

Please post the SQL code for your aggregate query.
Then post the SQL code for the query that you attempted, but got errors.
(You can get the SQL code for any query by switching from Design View to SQL View and copying and pasting the code here).

 

[squallleonhart]

Here is the query used to get the needed dates:
SELECT dbo_Block.blockID, dbo_BCBSKC_GroupData.groupID, dbo_Block.low_paid, dbo_Block.high_paid, [dbo_Block]![low_paid]-100 AS [Lower Bound 1 Year Ago], [dbo_Block]![high_paid]-100 AS [Upper Bound 1 Year Ago], [dbo_Block]![low_paid]-200 AS [Lower Bound 2 Years Ago], [dbo_Block]![high_paid]-200 AS [Upper Bound 2 Years Ago]
FROM dbo_Block INNER JOIN dbo_BCBSKC_GroupData ON dbo_Block.blockID = dbo_BCBSKC_GroupData.blockID
GROUP BY dbo_Block.blockID, dbo_BCBSKC_GroupData.groupID, dbo_Block.low_paid, dbo_Block.high_paid, [dbo_Block]![low_paid]-100, [dbo_Block]![high_paid]-100, [dbo_Block]![low_paid]-200, [dbo_Block]![high_paid]-200
HAVING (((dbo_Block.blockID)=[]));

For the query where I'm attempting to sum a column from another table I'm going to be using the low_paid and high_paid fields as criteria. Both of these fields are dates in short text format.

Here is the SQL for the attempted query:
SELECT [Date Ranges for Crystal GHRP Loss Ratios].blockID, [Date Ranges for Crystal GHRP Loss Ratios].groupID, PROD_ERN_INCM_F.ERN_INCM_YR_MO_SK, Sum(PROD_ERN_INCM_F.ERN_INCM_AMT) AS SumOfERN_INCM_AMT
FROM [Date Ranges for Crystal GHRP Loss Ratios] INNER JOIN PROD_ERN_INCM_F ON [Date Ranges for Crystal GHRP Loss Ratios].groupID = PROD_ERN_INCM_F.GRP_ID
GROUP BY [Date Ranges for Crystal GHRP Loss Ratios].blockID, [Date Ranges for Crystal GHRP Loss Ratios].groupID, PROD_ERN_INCM_F.ERN_INCM_YR_MO_SK
HAVING ((((PROD_ERN_INCM_F.ERN_INCM_YR_MO_SK)>=[Date Ranges for Crystal GHRP Loss Ratios].[low_paid]) And ((PROD_ERN_INCM_F.ERN_INCM_YR_MO_SK)<=[Date Ranges for Crystal GHRP Loss Ratios].[high_paid])));

 

[Joe4]

Since you are not including the "low_paid" and "high_paid" fields in the SELECT section of the aggregate query, move your criteria from the HAVING clause to the WHERE clause.

 

[squallleonhart]

Yes, perfect, this led me to look up the difference between the HAVING and WHERE clauses.
Thanks so much for your help!

 

[Joe4]

You are welcome!

Yes, WHERE applies to the data before grouping, and you can include fields in your tables that are not actually in the SELECT portion.
HAVING is after the fact, applying to the grouped data being returned only.