VBA code to develop a function to create a dynamic counter in an Access query

I want to increment the "Check" field in my "iitdataalllotsRecordCountqry" by 1, starting from a value of 1, grouped by "SupPart" where [QtyRecd]>0 and [QtyInsp]=0 and [QtyRej]=0 for each "SupPart".  Otherwise "Check" field = 0.  The output should look something like this, as an example.  Hope this helps.  I have attached the sample database.  Do I need to write a custom counter using VBA code OR do I need to use a Dcount function?  I would prefer using VBA code to write a custom function to perform this task, but I will need help in writing this code as I am only a beginner at VBA.

SupPart  QtyRecd   QtyInsp   QtyRej  Check    RecordCount    

x                 100             100        5            0                   1                  

x                  100            0             0             1                  2                

x                  125            0             0             2                  3                  

x                  95              0              3            0                   4                

y                  22              0              0             1                  1

y                   22             0               0            2                   2

y                  22              0               0            3                  3

y                  22              10            0            0                  4

z                 65                10           0            0                  1

z                 25                12            0           0                  2

z                 25                 0             0           1                   3

z                 30                 0            0            2                   4

db2.mdb
sxxguptaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

josephwalshCommented:
I have created a report which should allow you to produce the above.

db2.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sxxguptaAuthor Commented:
Hi Joseph:
The report looks excellent.  Thank you.  However, ss there a way I can transfer to create a similar VBA code as a function that I can call or use within the query without having to create the report?  Or should I ask you this question in another post and award you all the points?  Let me know cause the points are all yours...........
Sanjay
0
josephwalshCommented:
I did the report because I could not think of a way to structure a query to produce the same results.
I will sleep on it.
I will post again if I can offer solution or not.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sxxguptaAuthor Commented:
Thanks Joseph.  I will wait for your thoughts...........
0
josephwalshCommented:
I do not as yet have a solution that can be done through a query.

Can you give me details as to why you are using counters ?
What are you trying to achieve in the business sense ?
0
sxxguptaAuthor Commented:
I am basically analyzing to see if the 7th lot of each unique SupPart was inspected in our QA department or not.  If not, then our firm is in violation of its own internal SOPs.  
For the 7th lot to be valid:  For each unique SupPart, the preceding 6 consecutive lots should show the following:  QtyRecd>0, QtyInsp=0, QtyAcpt=QtyRecd, QtyRej=0, and InvQty=QtyRecd.  In other words, the preceding 6 consecutive lots were not inspected (QtyInsp=0) and then the 7th consective lot is inspected where  QtyRecd>0, QtyInsp<=QtyRecd.  Each lot as a unique identifier referenced using IITReportNo.
0
josephwalshCommented:
I do not think that I can do a query for that, perhaps others might, and I would love to see it.

However, I believe that the report modified is what you really need.
Now, I need to understand the business rule a little better.
Am I correct if I state the rule as follows :
An inspection must occur on a receipted item if the are six consecutive existing receipts that comply as follows :
    QtyRecd>0 and
    QtyInsp=0 and
    QtyRej=0 and
    InvQty=QtyRecd ( this is a new field you have introduced ???)

Therefore, if an item receipted item has been inspected, the counter starts again, looking for six consecutive receipts that comply as above.

If I read things correctly, you are looking for an exception report to identify the above.
0
sxxguptaAuthor Commented:
Yes!  Awesome.
0
josephwalshCommented:
Is it that you just want to identify the items that have fallen outside the company's SOP ?
If so, then I would have to change the way the info is produced.
This is where it becomes a little difficult.
I do not fully understand the business model, therefore what I propose may not be an efficient way to do it.
I see two basic solutions :
Solution 1 :
Have a work table. Clear the data from the work table. Scan the data as per the report (button on a form) , and insert records to the temporary table where the inspection procedure has been violated (intCheck = 7), then produce a report based on the data in the temporary table.
Solution 2 :
Create a field in the receipts table blnCheck (bit), default value 0(False). Have a button on a form as above, scan the table as above and set blnCheck=true where the inspection procedure has been violated, then produce a report from the table where blnCheck is True.
0
sxxguptaAuthor Commented:
Which one will be easier for you?  I'll try either solution.
0
sxxguptaAuthor Commented:
Sorry I was a little late in responding.  It has been crazy at work here......
 
0
josephwalshCommented:
I believe that option two would be easier.
Do you need assistance ?
0
sxxguptaAuthor Commented:
Yes please.  Thank you for your help.
0
sxxguptaAuthor Commented:
Thank you very much.
0
josephwalshCommented:
Thank You,
I will be busy at work over the next few days, but I will try to post a follow up on my last post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.