Random Numbering

  • Thread starter Thread starter Julie B
  • Start date Start date
J

Julie B

.. have several construction projects with various mile posts (locations). I
would like to pull up a construction project number and have 4 points (within
the mile posts) given. I have one table with the construction project
information. Then I made another table with all the mile posts to be picked
from. However, one project may begin at Mile Post 2 and another may begin at
Mile Post 0. How would I set this up. Thanks for all your time and help.
 
Julie B said:
. have several construction projects with various mile posts (locations).
I
would like to pull up a construction project number and have 4 points
(within
the mile posts) given. I have one table with the construction project
information. Then I made another table with all the mile posts to be
picked
from. However, one project may begin at Mile Post 2 and another may begin
at
Mile Post 0. How would I set this up. Thanks for all your time and help.
Click to expand...

Access VBA has a Rnd function that will return a random value from 0 to (but
not including) 1.

Here is an example that will generate random values between 1 and 1000

Public Function getReportCode()
Dim a As Long a = Int(1000 * Rnd) + 1
getReportCode = a
End Function

You can adapt this to generate pseudorandom integers in whatever range you
need.
 
. have several construction projects with various mile posts (locations). I
would like to pull up a construction project number and have 4 points (within
the mile posts) given. I have one table with the construction project
information. Then I made another table with all the mile posts to be picked
from. However, one project may begin at Mile Post 2 and another may begin at
Mile Post 0. How would I set this up. Thanks for all your time and help.
Click to expand...

Hi Julie,

We might need some more clarification.

Does each project have multiple related mileposts? If just from and
to, then you could store them in the project table. If more than two,
then you should use a child table to store them.

What is the significance of the "4 points" you mentioned?

What do you mean by "random numbering" in your subject line? Do you
really need some kind of random location generation?

Give us some more info and I'm sure we can help.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
I have 5 projects. I need to go out and inspect the projects. To be fair to
the contractor, I want something to randomly give me the mile posts (4
points) to go check.

So if

Project A begins at MP 2.0 and the project ends at MP 4.0
Project B begins at MP 8.35 and ends at MP 15.23
Project C begins at MP 0 and ends at MP 3.24
Project D begins at MP 22.31 and ends at MP 25.09
Project E begins at MP 15.87 and ends at MP 16.3

So lets say I am scheduled to be out of the office tomorrow for inspections
and I want to go to project A, I want to just plug in the project number and
have 4 random mile posts pop up.

I have one table with the project information.

I have another table with all mileposts from .1 to 30.9

I hope this helps and thanks for your time!
 
I have 5 projects. I need to go out and inspect the projects. To be fair to
the contractor, I want something to randomly give me the mile posts (4
points) to go check.

So if

Project A begins at MP 2.0 and the project ends at MP 4.0
Project B begins at MP 8.35 and ends at MP 15.23
Project C begins at MP 0 and ends at MP 3.24
Project D begins at MP 22.31 and ends at MP 25.09
Project E begins at MP 15.87 and ends at MP 16.3

So lets say I am scheduled to be out of the office tomorrow for inspections
and I want to go to project A, I want to just plug in the project number and
have 4 random mile posts pop up.

I have one table with the project information.

I have another table with all mileposts from .1 to 30.9
Click to expand...

Hi Julie, thanks for the clarification. That helps a lot!

First- you don't really need the extra "all mileposts" table to do
what you want to do.

The random generator function in Access -- Rnd() -- will generate a
random number from 0 to 1. That number can represent "how far along"
the project length you are going to inspect.

In your query, you can define the random inspection point field like
this:

InspectionMP: BeginMP + (Rnd(MyProjectID) * (EndMP - BeginMP))

The BeginMP and EndMP fields names need to match your field names.
Also, the Rnd function needs some kind of number input in order to
work correctly in a query. Just replace MyProjectID with some integer
from your project table.

You can either run this query 4 times and write down the numbers, or
you could create four of these fields (InspectionMP1, InspectionMP2,
etc.) and get all four of them at once.

Hope this helps to get you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back again.... Holidays over and got caught back up again....

Apparently I am not the brightest crayon in the box.

When I type the "InspectionMP: BeginMP=(Rnd(myprojectid)*(EndMP-BeginMP))....

I have no idea what the "integer" a/k/a myprojectid is referred to in the
help information.

I have colums with Contract Number, StreetName, Contractor, BMP, EMP.

So I still need some clarification, PLEASE!
 
When I type the "InspectionMP: BeginMP=(Rnd(myprojectid)*(EndMP-BeginMP))....

I have no idea what the "integer" a/k/a myprojectid is referred to in the
help information.

I have colums with Contract Number, StreetName, Contractor, BMP, EMP.

So I still need some clarification, PLEASE!
Click to expand...

Hi Julie,

When you call a function in a query, normally Access will save time by
running it only *once* and supplying that *one* result for every row.
Of course, that won't work for the Rnd function because you want it to
run for *every* row, giving you a different random number for each
one, not the same number over and over again.

So, you need to "trick" Access into running it for every row. The way
to do that is to send the Rnd function a number value as a parameter -
ANY number value - from your table.

You can choose any of the fields you mentioned, as long as they are an
integer number. The actual field or value doesn't matter - it's just
the trick mentioned above.

By the way - the convention we use is that when you see "My" in any
name, it means you should replace that name with the name that fits
your situation.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top