423,851 Members | 1,356 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Audit sampling with multiple criterias- Access Query

P: 17
Hi Guys, I have stuck with this problem for a while now.

I have 2 tables,
Table 1 with data which contain filed like name, doc_No, Amount, entity.

second , Table 2, I have with criteria's defined in column-wise for each entity as mentioned below.
my query should refer criteria table and slect the samples from table where Entity field is matching

1) I need to select TOP 6%(or as specified in the criteria table field "Criteria1_Per") samples from table where amount field is > 6000 (or as specified in the criteria table field "Criteria1_Range")
2) next I need to select 3 % (or as specified in the criteria table field "Criteria2_Per") samples from table where amount filed is between 3000 to 6000 (or as specified in the criteria table field "Criteria1_Range") and
3) finally I need to select 1% (or as specified in the criteria table field "Criteria3_Per") sample where amount field is < 3000 (or as specified in the criteria table field "Criteria1_Range").
The catch point is here total sample selected in point 1,2,3 should be 10% (or as specified in the criteria table field "Criteria4_Per") of the overall count in the table.

please help me on this
Sep 28 '18 #1
Share this Question
Share on Google+
34 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,705
nnlewis,

Welcome to Bytes!

First, it is very difficult for us to hlep you when we can't see any of your table information. We are not asking you to post your DB to this site, but we do ask that you provide a bit more about your tables.

Concerning the task at hand, it is also helpful if you provide us some sample data and what the expected results should be. If you are just performing calculations on one record once and displaying it in a form, that is one thing, but to expect multiple iterations, it is another. Basically, we can't help you unless we know more about what you are trying to accomplish.

Finally,
The catch point is here total sample selected in point 1,2,3 should be 10% (or as specified in the criteria table field "Criteria4_Per") of the overall count in the table.
Part of your concern is a basic math problem that doesn't add up based on any means of arithmetic. For example, let's say your first sample is 9000. 10% of that is 900. This means that you want all of your samples (6%, 3% and 1%) to add up to 10%? This will never happen, as a 3% sample of 6000 is 180 and a 1% sample of 3000 is 30. When you add a 6% sample of your 9000 (which is 540), your sum total is only 750. So, I'm not sure where the 10% requirement comes from.

We'll be glad to try and hepp if we get some more information from you. But at this point, we would only be guessing at how to approach this.
Sep 28 '18 #2

P: 17
Hi twinnyfo

sorry for that, I couldn't see attachment option earlier, but with some explore, I could find now. please have a look
Attached Files
File Type: zip Test_Database_1.zip (92.8 KB, 13 views)
Sep 28 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 2,705
Please don't post your DB on the forum unless we specifically ask you to.

Please explain your concern in greater detail and describe what it is you want to accomplish.
Sep 28 '18 #4

P: 17
It's just test DB. Not posted the actual one
Sep 28 '18 #5

P: 17
Yes, I agree, hence I need to fill the gap by changing percentage or using dynamic value for third criteria that will match the final count.
Sep 28 '18 #6

NeoPa
Expert Mod 15k+
P: 31,121
NNLewis:
It's just test DB. Not posted the actual one
You might be missing the point here I suspect.

Until someone asks you to post your database it's your responsibility to express you question clearly.

IE. It's not acceptable to expect others to look into your database to try and determine what it is that you should have included in the original question.

I hope I've made the position of the site clear enough. I'm sure you don't need an explanation as to why this makes good sense for everyone.
Sep 29 '18 #7

P: 17
Hi NeoPA,
I have tried to explain the requirement as much I could in my first post, subsequently based on request I have posted the Test DB. As I am new to the forum there would some slippages which I will take up positively on forums feedback. Thanks for your feedback
Oct 1 '18 #8

twinnyfo
Expert Mod 2.5K+
P: 2,705
See Post #2
twinnyfo:
We are not asking you to post your DB to this site, but we do ask that you provide a bit more about your tables.
See Post #4
twinnyfo:
Please don't post your DB on the forum unless we specifically ask you to.

Please explain your concern in greater detail and describe what it is you want to accomplish.
On this forum, you cannot expect to receive any help if you don't pur forth some form of effort into your problem at hand. You have not shown us any of your work or any attempt at resolution at your level--and we don't even know what it is you are trying to do.

Believe it or not, the experts and moderators on this forum really do want to help people. But, we are not in the habit of doing another person's work. We all have jobs of our own and have enough work of our own to keep us busy.

If you do not provide additional informaiton, you will not receive any assistance on this issue.

We are not being harsh--we are being realistic.
Oct 1 '18 #9

P: 17
Hi,

yes, I am not expert on this, but I have build query for each sample selection like 6%, 3% and 1% . only area I could not match was total sample selected tallies overall samples at 10%

the code applied by me as below

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPDA_CurrentItems.Company_Code, 
  2.        tblPDA_CurrentItems.ABS_Amount, 
  3.        tblPDA_CurrentItems.Exceptions, 
  4.        tblPDA_CurrentItems.Supplier_Code, 
  5.        tblPDA_CurrentItems.Supplier_Name, 
  6.        tblPDA_CurrentItems.Doc_No, 
  7.        tblPDA_CurrentItems.Doc_Type, 
  8.        tblPDA_CurrentItems.Doc_Date, 
  9.        tblPDA_CurrentItems.BaseLine_Date, 
  10.        tblPDA_CurrentItems.Posting_Key, 
  11.        tblPDA_CurrentItems.Invoice_Currency, 
  12.        tblPDA_CurrentItems.Invoice_Amount, 
  13.        tblPDA_CurrentItems.Invoice_Number, 
  14.        tblPDA_CurrentItems.Bank_Account_Number, 
  15.        tblPDA_CurrentItems.Local_Curr, 
  16.        tblPDA_CurrentItems.Local_Amount, 
  17.        tblPDA_CurrentItems.eFX_Conv_Rate, 
  18.        tblPDA_CurrentItems.Load_Date, 
  19.        tblPDA_CurrentItems.Loaded_By, 
  20.        Rnd([ABS_Amount]) AS Random
  21. FROM tblPDA_CurrentItems
  22. WHERE Exists
  23. (
  24. Select * from tblPDA_AuditCriteria 
  25. WHERE  (((tblPDA_CurrentItems.ABS_Amount ) In  
  26.  
  27. (SELECT DISTINCT TOP 6 PERCENT (tblPDA_CurrentItems.ABS_Amount) from tblPDA_CurrentItems AS temp 
  28.  
  29. WHERE tblPDA_CurrentItems.Company_Code=tblPDA_AuditCriteria.Company_Code 
  30.        AND tblPDA_CurrentItems.[ABS_Amount] > tblPDA_AuditCriteria.Criteria1_Greater_Than_Desc )
  31.  
  32.        OR
  33.  
  34. (SELECT DISTINCT TOP 3 PERCENT (tblPDA_CurrentItems.ABS_Amount) from tblPDA_CurrentItems AS temp2 
  35.  
  36. WHERE ( tblPDA_CurrentItems.Company_Code=tblPDA_AuditCriteria.Company_Code) 
  37.        AND (tblPDA_CurrentItems.[ABS_Amount] < tblPDA_AuditCriteria.Criteria1_Greater_Than_Desc 
  38.        AND (tblPDA_CurrentItems.[ABS_Amount] > tblPDA_AuditCriteria.Criteria3_Less_Than )))))
  39.  
  40.        OR 
  41.  
  42. (SELECT  DISTINCT TOP 1 PERCENT (ABS_Amount) from tblPDA_CurrentItems AS temp3
  43.  
  44. WHERE tblPDA_CurrentItems.Company_Code=tblPDA_AuditCriteria.Company_Code 
  45.        AND tblPDA_CurrentItems.ABS_Amount < tblPDA_AuditCriteria.Criteria3_Less_Than))))
  46. ORDER BY Rnd([ABS_Amount]) DESC
  47.  
  48. );
This code takes lot more time to Run, hence I have created 3 queries directly in access and used UNION to join them for faster execution.

when I run this through individual query, I get total 81 records, but I have 2240 records in table and 10% of the volume is 224. is there any way I can subtract 224-81 and run one more query for TOP Differential Value.
Oct 1 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 2,705
You still haven't explained exactly what you are trying to do and what it is about your current query that does not work.

As mentioned before 6% of 6000 + 3% of 3000 + 1% of 1000 will never equal 10% of 10,000. So, I still don't know how to address your overall concern.
Oct 1 '18 #11

twinnyfo
Expert Mod 2.5K+
P: 2,705
However, it does look like you are highly overcomplexifying things:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 6 PERCENT tblPDA_CurrentItems.*, 
  2.        Rnd([ABS_Amount]) AS Random
  3. FROM tblPDA_CurrentItems
  4. INNER JOIN (SELECT tblPDA_AuditCriteria.Company_Code, 
  5.                    tblPDA_AuditCriteria.Criteria1_Greater_Than_Desc 
  6.             FROM tblPDA_AuditCriteria) AS tblAuditCrit 
  7.             ON tblPDA_CurrentItems.Company_Code=tblAuditCrit.Company_Code 
  8. WHERE tblPDA_CurrentItems.[ABS_Amount] > tblAuditCrit.Criteria1_Greater_Than_Desc 
  9. ORDER BY Rnd([ABS_Amount]) DESC;
Then, just UNION ALL that query to two other queries that assess the 3% and 1% items. It is much easier (and quicker) using a JOIN to match your records.

I'm still not sure what all these values are supposed to represent, so I am shooting in the dark.

It also makes things a bit more difficult when your table and field names are so long. That is a lot of typing.
Oct 1 '18 #12

P: 17
Thanks Twinnyfo, Let me put across this.
my overall audit samples must be 10% of the total volume in tblPDA_CurrentItems. when I select 6%,3% and 1%, I will get only 81 records in my current DB. As data that we receive daily is very dynamic every time I need to select 10% samples from the daily pool for Random audit. hence how do I change the percentage dynamically that will return overall 10% samples?. the percentage of samples shown in each criteria is min requirement in each group (considering amount range as group). whenever records are not met the overall percentage, then I need to change the percentage of sample selection accordingly to meet required number.

for example, I have 1000 records that contains
* 100 records with amount field > 6000
* 300 records with amount field Between 3000 to 6000 and
* 600 records with amount field < 3000

now I need to select 100 samples i.e. 10% from 1000 records which should be as follows

* min 6% from 100 records i.e. 6 records
* Min 3% from 300 records i.e. 9 records
* Min 1% from 700 records I,e, 7 Records

in the above query I will get only 23 records instead 100 records. how do I change the Percentage dynamically so that I will select 100 samples in total. Note: I can increase the percentage to meet this requirement but there should not be any samples less than the min or base percentage mentioned above.

let me know if you need any further inputs on this.

One more challenge is I have 19 entities for which I need to select similar samples and each entity has different sample percentage, this is provided in Criteria table. Hence rather defining percentage, can I refer table from where query can pic relevant percentage for each entity ?
Oct 1 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 2,705
I think this still comes down to a math problem first.

If I use your example:

* 100 records with amount field > 6000
* 300 records with amount field Between 3000 to 6000 and
* 600 records with amount field < 3000

There is never a combination of a number of records in which we add those smaller percentages and it will equal 10%, because you are always selecting amounts that are less than 10% from portions of the whole.

I can't set your own business rules, but if they want a 10% from the whole, but still ensuring they get a proper sample from each group, the sample size must be 10%--from each group. In your example about you would yield:

* 10 records with amount field > 6000
* 30 records with amount field Between 3000 to 6000 and
* 60 records with amount field < 3000

TOTAL: 100 Records.

However, if they want a set ratio of records, then the math looks a little different:

* For each record sampled with amount field < 3000 you sample
* 3 records with amount field Between 3000 to 6000 and
* 6 records with amount field > 6000

In this case, you would simply find out how many total records you have, and divide by 10 (the number of records sampled). In your sample, again, you then get a multiple of 10. Multiply the number of records per group, then your queries can look like:

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 10 ...
  2.  
  3. SELECT TOP 30 ...
  4.  
  5. SELECT TOP 60 ...
There are HUGE limitations to this, as it assumes an equal ratios of records in each group every time you run a sample (which may not be realistic).

However, my recommendatoin is simply running a 10% sample on all records in each group--which will yield the desired 10%.

This is simple math which prevents this from being straightforward.
Oct 1 '18 #14

Rabbit
Expert Mod 10K+
P: 12,279
It sounds to me like there are 2 potential problems.

First, your percentages can change from run to run, is that correct? You can handle this in SQL by introducing rng, ranking by that, and using the WHERE clause to filter on the variable percentage. Or you can handle it using VBA to modify the query def.

Second, your percentage can fail to meet a global minimum percent so you need to go back and modify all the previous percentages. Is that correct? The question then becomes, how should those percentages be modified?

For example:
Sample A with 100 records @ 5% = 5 records
Sample B with 850 records @ 2% = 17 records
Sample C with 50 records @ 20% = 10 records
Population with 1000 records @ 10% = 100 records

You're short 68 records. But you haven't given a reliable method you want to use to modify the percentages of the samples. You said in one of your posts that you should just increase the percentage of the last sample. But in this scenario, you'll still be short.

Either way, the answer is a modification to whatever method you choose for the first question. Either in SQL, use a subquery to get the counts and modify the percentage. Or in VBA using code to get the counts and modify the percentage.
Oct 1 '18 #15

P: 17
Yes Rabbit, you are almost close now. I have a sample percentage selection table which specifies what should be the percentage for each company code.

challenge 1:
I need to count overall records minus samples selected in first two queries and limit the 3rd query to the balance numbers. this may help in tallied the overall records

Challenge 2:

I have 19 company codes where percentage will differ for every company code. hence is there any possibility where I can declare TOP N as variable and refer to table field?

sorry to trouble you, actually I am lost here and need some push.
Oct 3 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 2,705
Right now, I think we are trying to bite off more than we can chew with trying to come up with the "total" solution.

You've just introduced a considerably more convoluted scenario than what was initial described (which was already convoluted)--not to mention the math problems that would never work.

Your Challenge 1 introduces "moving target" math. As Scotty would say to Spock: "That's like trying to hit a bullet with a smaller bullet whilst wearing a blindfold, riding a horse!" However, I think we can manage this.

Your Challenge 2 introduces an additional layer of complexity that requires (in my opinion) a completely different approach to this solution.

This is why we ask for very detailed descriptions from our OPs. Tell us about all the moving parts that affect your problem.

If I undertsand Challenge 2 properly (I think this is the first place we need to start), for your 19 companies, there are different percentages (for each company--although several different companies could have the same percentage)? Each company can have multiple items in tblPDA_CurrentItems? Based upon tblPDA_CurrentItems.ABS_Amount is there a different percentage applied for the same company? Or are these 6%, 3% and 1% values merely arbitrarily stated values used for example. This is where I am confused. If the company have a set percentage, then how does this related to having a different ABS amount?

Again, we need much more information before we will ever resolve this completely.
Oct 3 '18 #17

Rabbit
Expert Mod 10K+
P: 12,279
challenge 1:
I need to count overall records minus samples selected in first two queries and limit the 3rd query to the balance numbers. this may help in tallied the overall records
I've already explained that this approach is not reliable. You can't always count on being able to increase the volume of the last query to hit the global minimum. I've presented such a situation in my original response. Please refer back to that and come up with an alternative approach.

Challenge 2:
I have 19 company codes where percentage will differ for every company code. hence is there any possibility where I can declare TOP N as variable and refer to table field?
No, you can not put a table field reference in place of the N in the TOP N predicate. You will need to pick one of the other methods I outlined in my original response.

Please read my original response more thoroughly as I have specified 2 different approaches you can use to solve your problem. In it, I've also pointed out the problem with your approach to the first challenge.

You will need to pick which approach you're most comfortable with and try to proceed with that. We can answer any questions you might have regarding which of the two methods you decide to go with, either the ranking query or VBA.
Oct 3 '18 #18

P: 17
Hi Rabbit/Twinnyfo,

I am fine with VBA approach, I feel its more flexible than access query. the reason why I said to increase the sample percentage in 3rd query is just coz it contains 80% of data.

if you refer the test database I attached, I have provided sample selection percentage for all 19 company codes in "tblPDA_AuditCriteria".

For example percentage sample selection of "0522" company code is as follows. I have already put this above , let me further explain on this in detail for better approach.

- min 6% of samples must be selected from > 6000 value (Descending order)
- min 3% samples must be selected from values between 3000 to 6000 (Randomly)
- min 1% samples must be selected from < 3000 value (Randomly)

Note* overall sample selection for this company code must be 5% of total volume.

In criteria table, I have Criteria1_Value, Criteria1_Percent, Criteria2_Value, Criteria2_Percent.... upto criteria 5. Criteria5 does not have value group as its overall percentage of samples that must be covered in criteria1 to criteria4 and can be used to calculate the revised percentage for each category if necessary.

I have mentioned sample selection and value grouping for every company code upto Criteria4 in the table. if percentage is not available then we can skip that criteria and move to next criteria.

Now I have the data in "tblPDA_CurrentItems" this table data is refreshed daily by users by importing raw txt file. table might carry single company code data or multiple company code data. All these company codes are already captured in criteria table.

once data is loaded into tblPDA_CurrentItems, I need to run code that selects the samples as per the predefined percentage and value group in criteria table upto criteria 4. if total sample selected from 1 to 4 criteria is less that the overall samples, then we need to adjust accordingly but ensure that no percentage is reduced than minimum prescribed in table .

Hope this will further help you to understand the problem, I know that you guys are already troubled enough by me:( . just hoping some good support from you guys to build this logic. - thanks
Oct 4 '18 #19

twinnyfo
Expert Mod 2.5K+
P: 2,705
I am unable to get your DB from my work network (which is another reason we ask folks not to post their DBs, as many of us can't get to them anyway).

I still think you are approaching this problem (and asking us to do the same) as "eating an elephant". I still think you need to approach the solution--whether in SQL or VBA--as one bite at a time.

Just slow down, decompress, and think about the problem, rather than just attacking it blindly.

Right now, you have all this data (which apparently continues to grow as people add to it with daily uploads) and you want to get this list of records. Your solution, in your mind, looks like this:

Gobs of Data =====> End Product

But, what your solution should look like is more like this:

Data => Step 1 => Step 2 => Step 3 => Step 4 => End Product

Even Rabbit would (probably) agree that approaching any problem in this world consists of breaking down the problem into smaller parts, working those smaller parts and building them together into a whole.

A lot of the experts here have many years experience, so these are principles we practice all the time.

So, my first question is, "Where do you start?" But, keeping in mind the old adage, "Always start with the end in mind" we now, after 19 posts, kinda sorta have a good idea what your end result is "supposed" to look like. So, before we get too deep, is this assessment correct:

You want:
For each company code:
  1. A number of randomly selected records
  2. That number of records (must be at least/cannot exceed?) a total percentage set for that company
  3. There are three sets of random samplings:
  • The first sampling is a first percentage set for this company based on having an ABS_Amount greater than or equal to a value set for this company
  • The second sampling is a second percentage set for this company based on having an ABS_Amount greater than or equal to a value set for this company but less than the previous value
  • The third sampling is a third percentage set for this company based on having an ABS_Amount less than the previous value in the second sampling
  • Caveat: if the total number of records sampled does not meet the total percentage set for this company described in 2 above, then continue to draw samples from the third sampling until that threshhold is met


Is this correct? I can't see us moving forward until we know what it is you are after.
Oct 4 '18 #20

P: 17
Bingo, there you. this is what exactly I am looking for.
Oct 4 '18 #21

twinnyfo
Expert Mod 2.5K+
P: 2,705
Now then, where do we start? And, how do you want to approach it? SQL or VBA?

There are advantages to using VBA, because you can build your query over time and view the results. Rabbit may also have some advice on how to build this, but I'm willing to work along with you.
Oct 4 '18 #22

P: 17
I would like to go with VBA as its easy to workaround for any changes.
one more update I wanted to give you on your earlier query, there wont be any additional data added to "tblPDA_currentItems" table on a daily basis, once I select the samples, I will appened those samples to different table and delete data from "tblPDA_currentItems", so every time new data will be used for audit samples.
Oct 4 '18 #23

twinnyfo
Expert Mod 2.5K+
P: 2,705
Sorry - I've been a bit busy at work.

So, to begin.... Do you know how to create a QueryDef using VBA?

This is the first place to start. It is very easy to do. Here is a sample. Before we move on, be sure you ask any questions about what this does. You will have to add the Microsoft DAO library to your references if you have not already done so.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CreateMyQuery()
  2. On Error GoTo EH
  3.     Dim db          As DAO.Database
  4.     Dim qdf         As DAO.QueryDef
  5.     Dim strQryName  As String
  6.     Dim strSQL      As String
  7.  
  8.     strQryName = "qryMyQuery"
  9.     strSQL = "SELECT * FROM tblYourTable;"
  10.     Set db = CurrentDb()
  11.     With db
  12.         On Error Resume Next
  13.         Call .QueryDefs.Delete(Name:=strQryName)
  14.         On Error GoTo EH
  15.         Set qdf = .CreateQueryDef(Name:=strQryName, _
  16.                                   SQLText:=strSQL)
  17.         .QueryDefs.Refresh
  18.     End With
  19.  
  20.     Set qdf = Nothing
  21.     Set db = Nothing
  22.  
  23.     Exit Sub
  24. EH:
  25.     MsgBox "There was an error creating your query!" & vbCrLf & vbCrLf & _
  26.         "Error: " & Err.Number & vbCrLf & _
  27.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  28.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  29.     Exit Sub
  30. End Sub
Oct 4 '18 #24

Rabbit
Expert Mod 10K+
P: 12,279
the reason why I said to increase the sample percentage in 3rd query is just coz it contains 80% of data.
But are you certain every company will always have enough data in the 3rd query? I think it's just safer if you have a backup plan for the edge case where there's an overflow.
Oct 4 '18 #25

P: 17
Hi Tinnyfo, I use access 2016 and do not have DAO reference . I tried to download the same from internet and select, but while selecting it shows "Error in Loading DLL"

I also observed that I could run the QeryDef without the need of DAO reference file, with additional code after procedure "DoCmd.OpenQuery ("qryMyAuditSamples")" I could able to View the report. means we can proceed further.
Oct 5 '18 #26

twinnyfo
Expert Mod 2.5K+
P: 2,705
Very strange that reference is not available to you. You should not have to download anything, as it is a standard reference available. Anyways, let’s move on.

First, I might not be able to focus on this over the next few days, but I will try to check in when I can.

Second, folks on this site typically request a specific response to specific questions—both the OPs and the experts. This is often helpful. If we ask you a question, it is because we want to know more about something so we know how we can help you better. Based upon the query you have tried to develop already, it is clear to me that you already know a little something about Access. However, building a QueryDef in VBA is a lot different than building a Query in the Query Builder. So, in my previous post, my question, along with the associated code was very specific: Do you know how to create a QueryDef using VBA? This is why you need the reference to DAO! In response, you said you can run DoCmd.OpenQuery without it. These are two completely different concepts.

My point here is that I don’t want to put the cart before the horse and assume that you know and understand certain concepts that are really leaving you clueless. Please review my previous post and let me know if you have any questions. We will be building your QueryDef using VBA. This is not terribly different than writing a SQL Query just using a text editor, but there are some programmatic things you should understand first.

Thanks!
Oct 5 '18 #27

P: 17
Frankly speaking, I have no idea how the query definition works in system, I have referred your code and made changes as required, when I could not find the result, I wrote open query code to see the result to confirm whether code is really producing necessary result or not for my satisfaction.

this is my Code which is still under exploration , you can just review once
Expand|Select|Wrap|Line Numbers
  1. Sub SampleSelection()
  2.  
  3.   Dim db As Database
  4.   Dim rst As Recordset
  5.   Dim rst1 As Recordset
  6.   Dim strSQL As String
  7.   Dim strSQL1, strSQL2, strSQL3, strSQL4, strSQL5 As String ''''' declared for running multiple sql statements'''''
  8.   Set db = CurrentDb()
  9.   Dim Gefcocomp, CC, CurCC As String
  10.   Dim Gefcocount As Long
  11.   Dim CAbove, CBelow, CBetween, CTotal, CrAbove, CrRand, CrBelow As Double ''' declaring for counting volumes''''
  12.   Dim Gefco As Long
  13.   Dim i, x, a, b, c, d As Integer
  14.  
  15. '''''' declaring objeccts for query definition''''''
  16.   'Dim db          As DAO.Database
  17.   Dim qdf         As DAO.QueryDef
  18.   Dim strQryName1, strQryName2, strQryName3, strQryName4  As String
  19.  
  20.     strSQL = "SELECT * FROM tblPDA_AuditCriteria ;"
  21.     strSQL1 = "SELECT Company_Code FROM tblPDA_CurrentItems Group By Company_Code;"
  22.  
  23.  
  24.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  25.     Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
  26.  
  27.  
  28.  
  29.     If rst.RecordCount <> 0 Then
  30.         rst.MoveLast
  31.         rst.MoveFirst
  32.         With rst
  33.         i = rst.RecordCount
  34.         Do Until (i = 0)
  35.         CC = rst!Company_Code
  36.  
  37.         If rst1.RecordCount <> 0 Then
  38.             rst1.MoveLast
  39.             rst1.MoveFirst
  40.             With rst1
  41.             x = rst1.RecordCount
  42.             Do Until (x = 0)
  43.             CurCC = rst1!Company_Code
  44.  
  45.         If CC = CurCC Then
  46.  
  47. '''''' Count GEFCO invoices for recalculating the percentages''''''''''''''''
  48.  
  49.             Gefcocount = DCount("[Doc_Type]", "tblPDA_CurrentItems", "[Doc_Type] = 'FD ' Or [Doc_Type] = 'FM' Or [Doc_Type] = 'FP' Or [Doc_Type] = 'FV'")
  50.  
  51.             Gefco = Round((Gefcocount) * 0.1, 0)
  52.  
  53. ''''''''''' Count > 6k invoices, Between 3K to 6K invoices and <3k invoices for recalculating the percentages''''''''''''
  54.  
  55.             CAbove = DCount("[Doc_No]", "tblPDA_CurrentItems", "[ABS_Amount] > " & rst![Cr_Above] & " ")
  56.             CBelow = DCount("[Doc_No]", "tblPDA_CurrentItems", "[ABS_Amount] < " & rst![CrBelow] & " ")
  57.             CBetween = DCount("[Doc_No]", "tblPDA_CurrentItems", "[ABS_Amount] >= " & rst![CrBelow] & "  And [ABS_Amount] <= " & rst![Cr_Above] & " ")
  58.             CTotal = (CAbove + CBelow + CBetween) - Gefco
  59.  
  60.  
  61. ''''''''''''' Running query for audit samples '''''''''''''''''''
  62.  
  63.             On Error GoTo EH
  64.  
  65.             strQryName1 = "qryMyAuditSamples1"
  66.             strQryName2 = "qryMyAuditSamples2"
  67.             strQryName3 = "qryMyAuditSamples3"
  68.             strQryName4 = "qryMyAuditSamples4"
  69.             strQryName5 = "qryMyAuditSamples5"
  70.  
  71. '''''' n refered as percentge criteria One field from criteria table'''''''Query 1'''''
  72.  
  73.             a = rst!Criteria1_Percent
  74.             If a <> "" Then
  75.                CrAbove = rst!Cr_Above
  76.                strSQL2 = "SELECT TOP " & a & " Percent * FROM tblPDA_CurrentItems WHERE [ABS_Amount] >  " & CrAbove & "  ORDER BY [ABS_Amount] DESC;"
  77.                Set db = CurrentDb()
  78.                With db
  79.                On Error Resume Next
  80.                Call .QueryDefs.Delete(Name:=strQryName1)
  81.                On Error GoTo EH
  82.                Set qdf = .CreateQueryDef(Name:=strQryName1, _
  83.                                       SQLText:=strSQL2)
  84.                .QueryDefs.Refresh
  85.                DoCmd.OpenQuery ("qryMyAuditSamples1")
  86.                 End With
  87.                 Set qdf = Nothing
  88.                 Set db = Nothing
  89.             End If
  90.  
  91. ''''''''''''' Query 2''''''''''''''''' Below values.. criteria 3''''''
  92.  
  93.             b = rst!Criteria3_Percent
  94.  
  95.             If b <> "" Then
  96.             CrBelow = rst!CrBelow
  97.             strSQL3 = "SELECT TOP " & b & " Percent * FROM tblPDA_CurrentItems WHERE [ABS_Amount] <  " & CrBelow & "  ORDER BY Rnd([ABS_Amount]) DESC;"
  98.             Set db = CurrentDb()
  99.             With db
  100.             On Error Resume Next
  101.             Call .QueryDefs.Delete(Name:=strQryName2)
  102.             On Error GoTo EH
  103.             Set qdf = .CreateQueryDef(Name:=strQryName2, _
  104.                                    SQLText:=strSQL3)
  105.  
  106.             .QueryDefs.Refresh
  107.             DoCmd.OpenQuery ("qryMyAuditSamples2")
  108.  
  109.             End With
  110.             Set qdf = Nothing
  111.             Set db = Nothing
  112.             End If
  113.  
  114. ''''''''''''' Query 3''''''''''''''''' Between values.. critera2''''''
  115.             c = rst!Criteria2_Percent
  116.  
  117.             If c <> "" Then
  118.             strSQL4 = "SELECT TOP " & c & " Percent * FROM tblPDA_CurrentItems WHERE ([ABS_Amount] >  " & CrBelow & " And [ABS_Amount] <  " & CrAbove & ") ORDER BY Rnd([ABS_Amount]) DESC;"
  119.             Set db = CurrentDb()
  120.             With db
  121.             On Error Resume Next
  122.             Call .QueryDefs.Delete(Name:=strQryName3)
  123.             On Error GoTo EH
  124.             Set qdf = .CreateQueryDef(Name:=strQryName3, _
  125.                                    SQLText:=strSQL4)
  126.  
  127.             .QueryDefs.Refresh
  128.             DoCmd.OpenQuery ("qryMyAuditSamples3")
  129.  
  130.             End With
  131.             Set qdf = Nothing
  132.             Set db = Nothing
  133.             End If
  134.  
  135. ''''''''''''' Query 4''''''''''''''''' Random values for > criteria 1''''''
  136.  
  137.             d = rst!Criteria4_Percent_Random
  138.  
  139.             If d <> 0 Then
  140.             strSQL5 = "SELECT TOP " & d & " Percent * FROM tblPDA_CurrentItems WHERE [ABS_Amount] >   " & CrAbove & " ORDER BY Rnd([ABS_Amount]) ASC;"
  141.             Set db = CurrentDb()
  142.             With db
  143.             On Error Resume Next
  144.             Call .QueryDefs.Delete(Name:=strQryName4)
  145.             On Error GoTo EH
  146.             Set qdf = .CreateQueryDef(Name:=strQryName4, _
  147.                                    SQLText:=strSQL5)
  148.  
  149.             .QueryDefs.Refresh
  150.             DoCmd.OpenQuery ("qryMyAuditSamples4")
  151.  
  152.             End With
  153.             Set qdf = Nothing
  154.             Set db = Nothing
  155.             End If
  156.  
  157.  
  158.  
  159.             Exit Sub
  160. EH:
  161.              MsgBox "There was an error creating your query!" & vbCrLf & vbCrLf & _
  162.                  "Error: " & Err.Number & vbCrLf & _
  163.                  "Description: " & Err.Description & vbCrLf & vbCrLf & _
  164.                  "Please contact your Database Administrator.", vbCritical, "WARNING!"
  165.              Exit Sub
  166.  
  167.         End If
  168.         .MoveNext
  169.         x = x - 1
  170.         Loop
  171.         End With
  172.         End If
  173.  
  174.     .MoveNext
  175.     i = i - 1
  176.  
  177.     Loop
  178.     End With
  179.     End If
  180.  Exit Sub
  181.  
  182. End Sub
  183.  
  184.  
Oct 5 '18 #28

twinnyfo
Expert Mod 2.5K+
P: 2,705
First, just wanted to show you what you should see when selecting the DAO reference. It should look very similar. I did this from Access 2016...



Second, refer again to post #20. I very briefly skimmed your long code, and would not know where to start.

Clearly, you understand a bit about what you are doing, so that is good. But, again, rather than scarfing down the entire elephant, let's god piece by piece through what you want your query to do. Based upon post #20, which you said described your end result, we should start at the beginning.

One might think that our starting point is at #1:
[quote]A number of randomly selected records{/quote]

However, I would recommend we start even more basic than that. Again, this is not to insult your intelligence, because you clearly have a grasp of many things concerning this. However, when you break this down to more manageable chunks, it may also streamline your code (and I suspect that it will).

So, what is more basic than getting "A number of randomly selected records"?

How about this:
For each company code
I say this, because you don't really want to get records from 29 companies. What you REALLY want to get is a very specific set of records from one company and then duplicate that 29 times.

Now, remember, this task has two very distinct aspects to it that you have to keep in mind:

1 - You need to manage sets of records (and values) that you can cycle through to generate your lists of criteria.

AND

2 - Based upon those criteria, you want to generate your QueryDef, which will then be the record source for your final set of records.

Please tell me if this makes sense. If not, we can discuss it some more.

I am sure that this will be an easy one for you, but can you create a recordset listing your company codes?
Attached Images
File Type: png References.png (31.5 KB, 308 views)
Oct 5 '18 #29

P: 17
thanks Twinnyfo, I know you are trying to help me and I do not want to disappoint you. may be perception on coding might be different.

I tried loading the reference object, bit I am getting DLL error.


secondly my have adopted this process in VB

Step one - Check criteria table for total available company codes and if records are more than 0, then select first record compare then move next

Expand|Select|Wrap|Line Numbers
  1.  
  2.     strSQL = "SELECT * FROM tblPDA_AuditCriteria ;"
  3.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  4.  
  5.     If rst.RecordCount <> 0 Then
  6.         rst.MoveLast
  7.         rst.MoveFirst
  8.         With rst
  9.         i = rst.RecordCount
  10.         Do Until (i = 0)
  11.         CC = rst!Company_Code
  12.  
Step 2 :, Check availability of multiple company codes in data table, if more than 0 records, select first com code, then move next.. do this until last record. I used "GROUP By" filter.

Expand|Select|Wrap|Line Numbers
  1.  
  2.     strSQL1 = "SELECT Company_Code FROM tblPDA_CurrentItems Group By Company_Code;"  '''''' identify more than one cmp code in data table
  3.  
  4.       Set rst1 = db.OpenRecordset(strSQL1, dbOpenDynaset)
  5.         If rst1.RecordCount <> 0 Then
  6.             rst1.MoveLast
  7.             rst1.MoveFirst
  8.             With rst1
  9.             x = rst1.RecordCount
  10.             Do Until (x = 0)
  11.             CurCC = rst1!Company_Code
  12.  
  13.  
  14.  

Step 3:
: now compare the 2 company codes and wherever company code matches, filter the records for selected company code and run through sample selection queries.
Expand|Select|Wrap|Line Numbers
  1.         If CC = CurCC Then
  2.             a = rst!Criteria1_Percent
  3.             If a <> "" Then
  4.                CrAbove = rst!Cr_Above
  5.                strSQL2 = "SELECT TOP " & a & " Percent * FROM tblPDA_CurrentItems WHERE [Company_code]= '" & CC & "' And [ABS_Amount] >  " & CrAbove & "  ORDER BY [ABS_Amount] DESC;"
  6.                Set db = CurrentDb()
  7.                With db
  8.                On Error Resume Next
  9.                Call .QueryDefs.Delete(Name:=strQryName1)
  10.                On Error GoTo EH
  11.                Set qdf = .CreateQueryDef(Name:=strQryName1, _
  12.                                       SQLText:=strSQL2)
  13.                .QueryDefs.Refresh
  14.  
  15. ''' update records to new tblPDA_CurrentItems_Tmp''''''''''''''''''''''
  16.  
  17.                Set rs = db.OpenRecordset("tblPDA_CurrentItems_tmp")
  18.                qry1 = "INSERT INTO " & "tblPDA_CurrentItems_tmp" & "" & " SELECT *  FROM qryMyAuditSamples1 ;"
  19.                DoCmd.RunSQL qry1
  20.                Set db = Nothing
  21.                End With
  22.                Set qdf = Nothing
  23.                Set db = Nothing
  24.             End If
  25.  
  26.  

Step 4 : Finally count records in first 2 queries (i.e. 6% and 3%), and RUN 3rd Query for only differential records so that this will give me overall matching numbers, I have used TOP N option for this query.

Expand|Select|Wrap|Line Numbers
  1.             b = rst!Criteria3_Percent
  2.             If b <> "" Then
  3.             CrBelow = rst!CrBelow
  4. '''''''' checking samples selected in above 2 query and balance samples required in this query to match overall sample percentage'''''
  5.  
  6.             e = rst!Criteria5_Overall_Percent '''' identifying overall percent from criteria table'''
  7.             CTotSamples = DCount("[Doc_No]", "tblPDA_CurrentItems_Tmp", "[Company_Code] = '" & CC & "'") '' total samples selected in tmp table'''
  8.             CTotDocs = DCount("[Doc_No]", "tblPDA_CurrentItems", "[Company_Code] = '" & CC & "'")    '''' actual oveall samples as per criteria 5 in table audit criteria''''
  9.             CProjSamples1 = Round((CTotDocs) * (e / 100), 0)                                         '''' total overall sample percentage required as per the table'''
  10.  
  11.            If CTotSamples <= CProjSamples1 Then
  12.  
  13.             CBalSamples = CProjSamples1 - CTotSamples                             ''''''' checking for differential volume and running query for bal volume''''
  14.  
  15.             strSQL3 = "SELECT TOP " & CBalSamples & " * FROM tblPDA_CurrentItems WHERE [Company_code]= '" & CC & "' And [ABS_Amount] <  " & CrBelow & "  ORDER BY Rnd([ABS_Amount]) ASC;"
  16.             Set db = CurrentDb()
  17.             With db
  18.             On Error Resume Next
  19.             Call .QueryDefs.Delete(Name:=strQryName2)
  20.             On Error GoTo EH
  21.             Set qdf = .CreateQueryDef(Name:=strQryName2, _
  22.                                    SQLText:=strSQL3)
  23.             .QueryDefs.Refresh
  24.  
  25. ''' update records to new tblPDA_CurrentItems_Tmp''''''''''''''''''''''
  26.  
  27.                Set rs = db.OpenRecordset("tblPDA_CurrentItems_tmp")
  28.                qry2 = "INSERT INTO " & "tblPDA_CurrentItems_tmp" & "" & " SELECT *  FROM qryMyAuditSamples2 ;"
  29.                DoCmd.RunSQL qry2
  30.                Set db = Nothing
  31.  
  32.  '          DoCmd.OpenQuery ("qryMyAuditSamples2") ''''' to view the query''''''
  33.  
  34.             End With
  35.             Set qdf = Nothing
  36.             Set db = Nothing
  37.             End If
  38.             End If
  39.  
  40.  
  41.  
Now you can suggest me , how you want me to proceed your way, I am sure, being expert there would be lot to learn from you guys and I am open for this.
Attached Images
File Type: jpg erroCapture.JPG (14.3 KB, 5 views)
Oct 8 '18 #30

twinnyfo
Expert Mod 2.5K+
P: 2,705
Your image never came through. Use the advanced button when you are submitting a reply to upload a picture.

Does your code produce results anywhere near what you want them to be?

I’m going to go out on a limb here and say that it doen’t.

I’m not sure how many times I can say this, but slow down. You are working “the solution” but you are not looking at each piece individually as I said. You code is incomplete and incorrect.

You are not systematically creating the final query that you want—and why are you creating an INSERT query?

This is said trying to be very kind, but you are making this more difficult on me that it has to be.
Oct 8 '18 #31

NeoPa
Expert Mod 15k+
P: 31,121
NNLewis:
[IMG]E:\PSA\erroCapture[/IMG]
A web page is not able to interpret a reference to E: to find your picture. As Twinny says, you'll need to use the advanced page to upload the file onto the server before the server will be able to serve your picture up to those browsing on this site.

I hope that makes sense. Holler if it doesn't and you need more direct assistance.
Oct 8 '18 #32

P: 17
Hi Twinnyfo/ NeoPa,

I have attached error.. Also above code is giving me the desired result as expected.

only error which I am getting as of now is in reference to the below code. code is not accepting parameter 'Z8' supplied my me and I am getting "error 13, type mismatch",
Expand|Select|Wrap|Line Numbers
  1. strSQL7 = "SELECT TOP " & f & " Percent * FROM tblPDA_CurrentItems WHERE [Company_code]= '" & CC & "' And [Doc_Type] = 'Z8' ORDER BY [ABS_Amount] DESC;"
  2.  
Oct 9 '18 #33

P: 17
I found the problem, it was not with code, but it was with variable "f" that was declared incorrectly. thanks.
Oct 9 '18 #34

P: 17
Thanks Guys, this problem is solved. querydef from Twinnynfo was really useful and appreciate your patience and support.
Oct 10 '18 #35

Post your reply

Sign in to post your reply or Sign up for a free account.