Code for an Adverse Selection

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have some simple code to make a Random Selection of 'x' records (random
selection) .. that is currently just a Query. I'd like to create a Form
that does the same but also give me the ability to make an Adverse selection
(based on a certain field). Is this something I could do in a Form vs.
Queries?


Random Selection Code:
______
SELECT TOP 25 Original.* INTO New_selection
FROM Original
ORDER BY Rnd(IsNull(Original.ID)*0+1);
_______


Thanks,



Jeff
 
Jeff

I'm not familiar with that term ("adverse selection"). Can you describe
what you wish to end up with?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Sorry for not clarifying "adverse". I mean based on another field (ie age
between 20 and 25). In other words ... make an adverse selection of, for
example, 50 records where the age is between 20 and 25 yrs old or debt
ratios> 32%. Adverse meaning not truly random becuase its based on another
criterion.
 
Are you saying that you want to find records where [Age] is between 20 and
25?

If I had a field in a table with [Age], I could use a query with selection
criterion like:

Between 20 And 25

By the way, if you DO have a field named [Age], and if that field stores,
well, peoples' ages, you are making a lot of extra maintenance work for
someone. Age depends on DOB and on today's date. A less labor intensive
approach would be to store DOB and use a query to calculate "age" on the
fly.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The "Age" thing was just as a sample and I'm not actually using it. Back to
the original question regarding creating a Form vs a SQL query:

I have some simple code to make a Random Selection of 'x' records
(random selection) .. that is currently just a Query. I'd like to create a
Form
that does the same but also give me the ability to make an Adverse
selection (based on a certain field). Is this something I could do in a
Form vs. Queries?





Jeff Boyce said:
Are you saying that you want to find records where [Age] is between 20 and
25?

If I had a field in a table with [Age], I could use a query with selection
criterion like:

Between 20 And 25

By the way, if you DO have a field named [Age], and if that field stores,
well, peoples' ages, you are making a lot of extra maintenance work for
someone. Age depends on DOB and on today's date. A less labor intensive
approach would be to store DOB and use a query to calculate "age" on the
fly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff said:
Sorry for not clarifying "adverse". I mean based on another field (ie
age between 20 and 25). In other words ... make an adverse selection of,
for example, 50 records where the age is between 20 and 25 yrs old or
debt ratios> 32%. Adverse meaning not truly random becuase its based on
another criterion.
Click to expand...
Click to expand...
 
Forms are strictly windows to the data. You'll still need a query, and base
your form on that query.

Your query could be something like:

SELECT TOP 25 Original.* INTO New_selection
FROM Original
WHERE Field1 <> Nz(Forms!SomeFormName!SomeControlName, 0)
ORDER BY Rnd(IsNull(Original.ID)*0+1);

Assuming you've put something in the control SomeControlName on the form
SomeFormName, you'd then requery the form and you should get a random list
of all records excluding those where Field1 is equal to the value on the
form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff said:
The "Age" thing was just as a sample and I'm not actually using it. Back
to the original question regarding creating a Form vs a SQL query:

I have some simple code to make a Random Selection of 'x' records
(random selection) .. that is currently just a Query. I'd like to create
a Form
that does the same but also give me the ability to make an Adverse
selection (based on a certain field). Is this something I could do in a
Form vs. Queries?





Jeff Boyce said:
Are you saying that you want to find records where [Age] is between 20
and 25?

If I had a field in a table with [Age], I could use a query with
selection criterion like:

Between 20 And 25

By the way, if you DO have a field named [Age], and if that field stores,
well, peoples' ages, you are making a lot of extra maintenance work for
someone. Age depends on DOB and on today's date. A less labor intensive
approach would be to store DOB and use a query to calculate "age" on the
fly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff said:
Sorry for not clarifying "adverse". I mean based on another field (ie
age between 20 and 25). In other words ... make an adverse selection
of, for example, 50 records where the age is between 20 and 25 yrs old
or debt ratios> 32%. Adverse meaning not truly random becuase its based
on another criterion.



Jeff

I'm not familiar with that term ("adverse selection"). Can you
describe what you wish to end up with?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have some simple code to make a Random Selection of 'x' records
(random selection) .. that is currently just a Query. I'd like to
create a Form that does the same but also give me the ability to make
an Adverse selection (based on a certain field). Is this something I
could do in a Form vs. Queries?


Random Selection Code:
______
SELECT TOP 25 Original.* INTO New_selection
FROM Original
ORDER BY Rnd(IsNull(Original.ID)*0+1);
_______


Thanks,



Jeff
Click to expand...
Click to expand...
Click to expand...
 
Back
Top