Sub Query question

[ice_531]

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.

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.

[szlamany]

Is this MS SQL server?

Can you use STORED PROCEDURES??

[ice_531]

Front end and Back end databases involved in this are MS ACCESS.

(the actual data comes from ORACLE though, as its the "true backend" company wide)

[szlamany]

Sorry - don't do access...

If it was MS SQL I was going to recommend using temp tables or views with ROW_COUNT formula - which basically assigns a unique number to each row "when the query" is produced - then you could put a WHERE clause in for "adjusting" the TOP #...

[abhijit]

According to this article, access lets you use parameters with queries.

/*Formatted*/
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; 

[ice_531]

Unfortunately parameters will not work within a subquery, I don't believe. Also parameters require manual interaction.

This back end is fully automated. (Via task scheduling + macros).

I was able to come up with a work around for this problem. It's dirty, but seems to work from brief testing i've been able to do. It isn't ideal by any means, and has to be ran on the front end anytime an audit gets submitted.

I will keep researching further about this though. (I'm leaning towards this may just be a shortcoming of Access as opposed to mysql or oracle)