asked on

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%] "

Jeffrey Coachman

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

Brogrim

ASKER

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

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brogrim

ASKER

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

Jeffrey Coachman

You accepted your own post of: https://www.experts-exchange.com/questions/27844901/Update-based-on-criteria.html?anchorAnswerId=38344838#a38344838
...yet you clearly stated that it was based on my post.

IMO, you should have selected my post with the sample database:
https://www.experts-exchange.com/questions/27844901/Update-based-on-criteria.html?anchorAnswerId=38347559#a38347559

So I am not really "objecting" here, just trying to make sure the the appropriate solution is flagged as the "answer"

Brogrim

ASKER

my apoligies, how do I rectify it?

Brogrim

ASKER

Could not be any clearer, thanks Jeff

Jeffrey Coachman

Thanks.

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

Jeff