Forum Discussion
LBaumgart
Apr 21, 2025Copper Contributor
Select Query values not showing in report - MS Access
I'm probably missing something simple, but.. I have a Table that has 4 field (plus key), Company ID, Join Date, Quit Date, Notes. This tracks agency membership I made a select query, with 2 IIF st...
Kidd_Ip
Apr 22, 2025MVP
Any chance to use a subquery:
SELECT CompanyID, JoinDate, QuitDate, Notes,
NumOfMonthsCalc, NumOfMonthsCurr,
NumOfMonthsCalc / 12 AS YRSCALC,
NumOfMonthsCurr / 12 AS YRSCURR
FROM
(SELECT tblJOINQUIT.[Company ID#] AS CompanyID, tblJOINQUIT.[Join Date] AS JoinDate,
tblJOINQUIT.[Quit Date] AS QuitDate, tblJOINQUIT.Notes AS Notes,
Sum(IIf([Quit Date] Is Null, 0, DateDiff("m", [Join Date], [Quit Date]))) AS NumOfMonthsCalc,
Sum(IIf([Quit Date] Is Not Null, 0, DateDiff("m", [Join Date], Now()))) AS NumOfMonthsCurr
FROM tblJOINQUIT
GROUP BY tblJOINQUIT.[Company ID#], tblJOINQUIT.[Join Date], tblJOINQUIT.[Quit Date], tblJOINQUIT.Notes) AS SubQuery;
- LBaumgartApr 23, 2025Copper Contributor
Thank You! worked great