Division By Zero Problem With Random Records :(

How To Select X Random Records
I need to select 200 random customers from my table, how can I do that?

Table: tblCustomers
PK: CustID

View Replies !   View Related

---

Random Duplicate Records
Hi Guys,

I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:


Const MyTable As String = "Blasthole Submission"
Const MyField As String = "Sample Name"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Double
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
For intCounter = Me.txtStartValue To Me.txtEndValue
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Fields("Submission #") = Me.SubNum
rs.Fields("Sample Type") = "Blasthole"
rs.Fields("XRF") = "True"
rs.Fields("LOI") = "True"
rs.Update
Next intCounter
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?

Thanks in advance for your help!

View Replies !   View Related

---

Update All Records With Random Value ?
This is my first post. Please help me.

Here's my DB :

Status : Number (Long Integer)
Random : Number (Double)

If Random value is around 0-0.9, the status value is 1
If Random value is around 0.9-1, the status value is 0

Here's my Query :

UPDATE DB Set Random = RND()

Rnd() give random value, but each records have the same value,
let's say 0.71212154

How can I update all records with random value,
with a single query ?

View Replies !   View Related

---

Extracting Random Records
If i have a table with several records let's say 1000 records, and each record has a field called cboCategory. Now from these 1000 records, lets say i have 200 records which has a value of CategoryA in the cboCategory field. Now is it possible that i extract "randomly" a number of records from that particular category chosen.

e.g.

Table -> 1000 records

records which belong to categoryA -> 200

extract 20 random records from the table which have a value of categoryA inthe cboCategory field.

Thanks for any suggestion.

View Replies !   View Related

---

Updating 10 Random Records In A Table.
Hi,

I have following query that takes 10 random records from one table (tblaccounts) and inserts that into another table (tblrandom).

SELECT TOP 10 tblaccount.*, Rnd([accnum]) AS Randnumber INTO tblRandom FROM tblaccount ORDER BY Rnd([accnum])

The above query works fine for that purpose.

However, I want to just update 10 Random records in tblaccount to set the following field:

SET status = "Assigned"

Does anyone know how to do this using an update statement please? Thanks!

View Replies !   View Related

---

Query To Select 20 Random Records
As part of my job, each month I have to select 20 problem report resolutions and grade them on quality. Typically we have about 100 problem report resolutions per month. Is there a way I can use a query to return a random selection of 20?

Thanks,
Jim

View Replies !   View Related

---

Select 20 Random Records In Query
I have to review 20 reports each month for quality check. I have a query that lists the reports completed within the past 30 days. Is there a way to filter this query to show only 20 random records?

If there is no way to do this, can you suggest some way of doing this so that it's impartial? For example I don't want to select the first 20 of the month because everyone will catch on and wait late in the month to post their report. Normally about 70 reports are completed in a month.

Thanks,
Jim

View Replies !   View Related

---

Selecting 4 Records At Random From A Table
Hi All,

Any ideas on how I do the following.

I have a table with 1500 records in it.

I want to select at random 4 of these records and append them into another table along with some other data that the query will not pick up, in this case Pilot and Month.

I guess I am having to go the VBA route but dont have a clue on how to do it.

Cheers in anticipation.

Andy.

INSERT INTO Assignments ( flightcode, aircraft, depart, destin, pilotcode, [Month] )
SELECT Schedule.Flightcode, Schedule.Aircraft, Schedule.Departure, Schedule.Destination, [pilot] AS Expr1, [MONTH] AS Expr2
FROM Schedule
WHERE (((Schedule.Departure) Like "man*"));

View Replies !   View Related

---

Division By Zero?
I'm trying to trouble-shoot an undocumented datbase built by someone else and running into a recurring problem. She has it set up where a macro runs several queries. When these macros are run, the queries cause "Division by Zero" errors which stops the macro in its tracks. The following is a query that causes this error, but by looking at this query, I see no place where a division takes place. I'm assumming that another porblem is occuring and Access just states that it's division by zero error when it's actually something else.

1) Has anyone seen a "Division by Zero" error when running a query when it's actually something else causing the problem?
2) Can anyone see from the query what could possibly cause this and give me a hint as to a good way to trouble-shoot these queries to possibly handle whatever problem is occurring.

Thanks,

The query is as follows:
SELECT [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales AS [MTD NDS], [004c YTD Cogs by Category qry].SumOfNetSales AS [YTD NSD], [Buyer Category Fiscal Period for Report].EndDate
FROM ((((((((((((BuyerCategory LEFT JOIN dbo_Category ON BuyerCategory.CategoryID = dbo_Category.CategoryID) LEFT JOIN [008c GMROI by Category] ON BuyerCategory.CategoryID = [008c GMROI by Category].CategoryID) LEFT JOIN [016c MTD GM by Category] ON BuyerCategory.CategoryID = [016c MTD GM by Category].CategoryID) LEFT JOIN [017c YTD GM by Category] ON BuyerCategory.CategoryID = [017c YTD GM by Category].CategoryID) LEFT JOIN [010c Turns by Category] ON BuyerCategory.CategoryID = [010c Turns by Category].CategoryID) LEFT JOIN [005c MTD Cogs by Category qry] ON BuyerCategory.CategoryID = [005c MTD Cogs by Category qry].CategoryID) LEFT JOIN [004c YTD Cogs by Category qry] ON BuyerCategory.CategoryID = [004c YTD Cogs by Category qry].CategoryID) LEFT JOIN [006c MonthEnd Inv by Category qry] ON BuyerCategory.CategoryID = [006c MonthEnd Inv by Category qry].CategoryID) LEFT JOIN [018c MTD GPM by Category] ON BuyerCategory.CategoryID = [018c MTD GPM by Category].CategoryID) LEFT JOIN [019c YTD GPM by Category] ON BuyerCategory.CategoryID = [019c YTD GPM by Category].CategoryID) LEFT JOIN [015c Turns Buyer Category] ON BuyerCategory.Buyer = [015c Turns Buyer Category].Buyer) LEFT JOIN [014c GMROI Buyer Category] ON BuyerCategory.Buyer = [014c GMROI Buyer Category].Buyer) LEFT JOIN [Buyer Category Fiscal Period for Report] ON (BuyerCategory.Buyer = [Buyer Category Fiscal Period for Report].Buyer) AND (BuyerCategory.CategoryID = [Buyer Category Fiscal Period for Report].CategoryID)
GROUP BY [Buyer Category Fiscal Period for Report].FiscalMonth, [Buyer Category Fiscal Period for Report].FiscalYear, BuyerCategory.Buyer, BuyerCategory.Description, BuyerCategory.CategoryID, [008c GMROI by Category].[Category GMROI], [016c MTD GM by Category].[MTD GM], [017c YTD GM by Category].[YTD GM], [010c Turns by Category].[Category Turns], [005c MTD Cogs by Category qry].SumOfNetCogs, [004c YTD Cogs by Category qry].SumOfNetCogs, [006c MonthEnd Inv by Category qry].MonthEndInv, [014c GMROI Buyer Category].BuyerCategoryGMROI, [018c MTD GPM by Category].[Buyer Cat MTD GM], [019c YTD GPM by Category].[Buyer Cat YTD GM], [015c Turns Buyer Category].[BuyerCategory Turns], [005c MTD Cogs by Category qry].SumOfNetSales, [004c YTD Cogs by Category qry].SumOfNetSales, [Buyer Category Fiscal Period for Report].EndDate
ORDER BY BuyerCategory.Buyer;

View Replies !   View Related

---

Division By Zero
I have a field in the a query with the following:
% OVER NB: 1-([Monthly MTA Table]!NB/[main inventory table]!PRICE)
I then format the column in properties to percent
When I try to put anything in the criteria I get an error "Division by zero"

I want to only the records that are greater then 80%

View Replies !   View Related

---

Division By 0
I have a query with the following calc. but it has an error of 'division by o' when I run it. Some of the figures may be null or 0 within the fields and I still want it to return a % figure result

I know its probably the iif function but am not sure how to use it in this instance.

Process %: Sum([Process volume]*[se]/[minutes worked])

Thanks

View Replies !   View Related

---

Division Of A Field
I have a table named "Patients" with a field named "Code" and another field named "Patient" which contains the full name (LastName space FirstName) "Chaviatsos Nick" for example.
I want to make an other table from the table "Patients" with three fields:
1. A field named "Code"
2. A field named "LastName" which will contain the Last Name of the field "Patient" of the table 'Patients" and
3. A field named "FrstName" which will contain the Frst Name of the field "Patient" of the table 'Patients"
How Can I manage this?

View Replies !   View Related

---

Round Integer Division .5 To 1
Please help me with the round function. I want .5 to round to 1.
Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.

I used the following expression:
RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)

Thank you.

View Replies !   View Related

---

Division By Zero Error But It Works When I Dont Have Any Criteria
I have made a query with to take the percent difference of two mileages and then if they are greater than 2.5% to put a 1 or if not a 0. I try and put a 1 in criteria in order to only see the ones that are changed by greater than 2.5% and I get the divide by zero error. Any ideas how to fix this problem.

Here is my IFF statement

diff: IIf((Abs(([Mile1]-[Mile2]))/[Mile1]>=0.025),1,0)

View Replies !   View Related

---

Random
Hi,

I have a table which shows fraud order Nos accepted and fraud order No's denied and I would like the system to give me one random number from accetped(Per Agent) and 3 random order numbers denied (Per Agent).

Is this do able in Access or am I clutching at straws?????:eek:

View Replies !   View Related

---

Rnd Not Very Random
I have a small unbound form which has a txt box with code something like =Rnd((20-1+1)*Rnd()+1)) to generate random numbers. When the db and form are first opened the field always contains the same number. Using F9 to refresh will generate another number but it is always the same too.

If the form is closed then reopened (db is not closed) the field will happily generate a random number each time the form is opened or if F9 is used.

I have tried Recalc in numerous events but each time it failed to work. It's late at night here so I'm hoping the morning will bring a clearer outlook???

Has anyone got any suggestions? :confused:

View Replies !   View Related

---

Is Random Really Random?
Was playing around, and when using the
Code:Me.txtNum1.Value = Int((51 - 1 + 1) * Rnd + 1)
format, I noticed that, with 6 differnet text boxes, that after opening the database, the "random" numbers repeat in the same order. That is, with 6 randomly generated numbers, the first set is always the same and the second the same and so on.

Why is it not really random?

View Replies !   View Related

---

Random Numbers
Hi,

Can you please help me out--give me some guidance-if you have knowledge about random numbers. Say, for instance, I have 2,000 records and I want to randomly create a final list with 800 of these records, how do I go about it? Thanks.

View Replies !   View Related

---

Random Deletions
Hi, I have a DB that is basically a big electronic form. Each table is a section of the form and the main central table stores the ID's from the other tables. Most of the relationships are one to one but without referential integrity because sometimes sections need to be skipped.

It's been growing fairly rapidly lately as bit's keep getting added but now a weird deletion problem has started appearing. Every once in a while when a user goes to complete the form (right at the end) it suddenly dissapears. The records in the related "section" tables are still there but the main table storing all the ID's loses the record.

There are NO cascade update/delete's on any of the relationships, also no-error message appears it just goes. I think there is a problem in actually saving the record because I moved a DoCmd.RunCommand acSaveRec command from the complete button to a slightly earlier process and the point at which the record dissapeared also moved.

Does anyone know what may be causing it? It only happens to about 1 in 30 forms and there is no obvious link between the problem and
a) the machine it happens on
b) the person using it
c) the data entered into the form

If it happens the same user can input exactly the same data in exactly the same way and it saves it fine.

It really is random!

People are starting to get really frustrated. I was tring to "sell" the idea to a new department as well and had 15 people sitting in front of a projector when it happened to me. I was mortified :eek:

Any ideas would be greatly appreciated.

thanks,

Tom

View Replies !   View Related

---

Random Error
Hi,

everything was going fine until a random error appeared and now wont go away.

here is my code

MainFormPay()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = " Payment Numbers" ' name of form t open
ANumber = [LastOfVerificationID].Value ' set value of latest Verify number

stLinkCriteria = "[ContractorID]=" & me.ContractorID ' filter to contractorID
DoCmd.OpenForm stDocName, , , stLinkCriteria
Docmd.GoToRecord, , acNewRec

Forms![Payment Numbers]![VerificationID].Value = ANumber

end sub

this was working fine but now i get the following error message:

Run-time error '40036'
Method 'Item' of object 'Forms' failed

when i debug, it highlights the line
Forms![Payment Numbers]![VerificationID].Value = ANumber

the purpose is to get the LastOfVerificationID value from form 1 and then open Payment Numbers, create a new payment number, set verificationID to the new value. this was working fine and without even woring on the forms code, this error has arisen.

how can i fix it?


Nigel

View Replies !   View Related

---