Hey all,

So I've been working on an auditing system at work, and am not that familiar with subqueries. So basically I have several append queries in a backend database that get run that pull specific "Types" of a call to audit. (This backend runs every monday).

I will give you an example of just 1 type for this issue. So this append query should pull 3 of this particular type each week. (end up with 9 after 3 weeks)

However, on the front end auditors CAN add in types to audit manually, and this throws my count off. I can end up going over the final count of 9 needed. Here is the code for the append query in question.

Code:
INSERT INTO tbl_final_to_send ( MSR_OPEN_DATE_TIME, MSR_SR_NUM, MSRA_CALL_TYPE, MSR_SITE_ID, MSR_FIRSTSAVE_USER, RandomNum, MSR_SR_NUM )
SELECT [Get PRF Calls].MSR_OPEN_DATE_TIME, [Get PRF Calls].MSR_SR_NUM, [Get PRF Calls].MSRA_CALL_TYPE, [Get PRF Calls].MSR_SITE_ID, [Get PRF Calls].MSR_FIRSTSAVE_USER, [Get PRF Calls].RandomNum, [Get PRF Calls].MSR_SR_NUM
FROM [02_PRF Month COUNT] RIGHT JOIN [Get PRF Calls] ON [02_PRF Month COUNT].[HD Agent] = [Get PRF Calls].MSR_FIRSTSAVE_USER
WHERE ((([Get PRF Calls].MSR_SR_NUM) In (SELECT TOP 3 MSR_SR_NUM
FROM [Get PRF Calls] AS Dupe
WHERE Dupe.MSR_FIRSTSAVE_USER = [Get PRF Calls].MSR_FIRSTSAVE_USER)) AND (([02_PRF Month COUNT].[CountOfCall Type])<9))
GROUP BY [Get PRF Calls].MSR_OPEN_DATE_TIME, [Get PRF Calls].MSRA_CALL_TYPE, [Get PRF Calls].MSR_SITE_ID, [Get PRF Calls].MSR_FIRSTSAVE_USER, [Get PRF Calls].RandomNum, [Get PRF Calls].MSR_SR_NUM;

I could solve this problem easily If in the subquery where it says "SELECT TOP 3", if i could insert a variable/expression in place of the number 3 instead. e.g. SELECT TOP (9 - [CountOfCall Type])

That would subtract how many of that type that user currently has already in the front end database. However this doesnt seem possible....so I'm guessing I'll need to get all of these append queries into pure VBA to be able to do this....

Just wanted to check here to see if anyone had any better ideas first!

Thanks.