• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

Update based on criteria

I have  a record set with 500 records, if the user enters 10 on my application I want 10% of the record set to be updated.

Struggling to find a starting point but am looking at this

select 10% of record set
update the selectedrecords to value 2

Any ideas how I would do this? the record set is named "qryRenewals2e" and the control for the % value is "Forms![frmRenewalCreation]![txt%] "
0
Brogrim
Asked:
Brogrim
  • 4
  • 4
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Issues:
1. How are these 10% of records to be selected?
(Random?, based on some Sort Order?, ...etc)

2. 10% 0f 500 is 50
10% of 501 is 50.1
Since you cannot update 50.1 records, do you have a specific plan for "rounding"

In other words, you really need to be specific here...

Finally this would be much easier to do with two queries (or one query with a subquery), than with a recordset.

For example., this is a query of the first 10% of products by the product ID.
This query is named "Query1":
SELECT TOP 10 PERCENT Products.ProductID, Products.ReorderLevel
FROM Products
ORDER BY Products.ProductID;

This query, (Query2)  then updates the ReorderLevel to 2 for those 10% of the records:
UPDATE Query1 SET Query1.ReorderLevel = 2;

JeffCoachman
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
Thank you very much for such a detailed solution

I have followed your instructions and it has worked perfectly, the only issue I have is passing the % value to the select statement.

The 1st query:
SELECT TOP 10 PERCENT tblRenewalPermit_Member.PermitApplicationTypeID
FROM tblRenewalPermit_Member
WHERE (((tblRenewalPermit_Member.PermitApplicationTypeID) Is Null));


the 2nd query is used in code to update the 10%
 DoCmd.RunSQL "UPDATE [qryRenewals%1] SET [qryRenewals%1].PermitApplicationTypeID = 2;"

Is there anyway I can put  a paremeter in where the "10" is

SELECT TOP [Forms]![frmRenewalCreation]![txt%] PERCENT tblRenewalPermit_Member.PermitApplicationTypeID
FROM tblRenewalPermit_Member
WHERE (((tblRenewalPermit_Member.PermitApplicationTypeID) Is Null));

I am getting a reserved word error
0
 
Jeffrey CoachmanMIS LiasonCommented:
Your original question was for 10%, so I hardcoded it.

Making the "10" a parameter is tricky because you would have to build and run the Query in code...
...then obviously run the update query in code as well
...and provide a textbox for the "updateTo" value...

This sample works, ...you will have to examine it fully, test it and then modify it to work in your database

JeffCoachman
EEQ-27844901SelectTop10TenPerenc.mdb
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
BrogrimInformation Systems Development ManagerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Brogrim's comment #a38344838

for the following reason:

excellent example
0
 
Jeffrey CoachmanMIS LiasonCommented:
You accepted your own post of: http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27844901.html#a38344838
...yet you clearly stated that it was based on my post.

IMO, you should have selected my post with the sample database:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27844901.html#a38347559

So I am not really "objecting" here, just trying to make sure the the appropriate solution is flagged as the "answer"
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
my apoligies, how do I rectify it?
0
 
BrogrimInformation Systems Development ManagerAuthor Commented:
Could not be any clearer, thanks Jeff
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks.

This was actually a fun little project....
;-)

Jeff
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now