Random Selection

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

"FrankM" wrote:

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

Your math is wrong again - 50 Rep with 2 product each is 100. Try these
three queries (it will run slow).
FrankM_1 ---
SELECT T.Site, T.Rep, T.Cust, T.ID, T.Prod, T.Serv, T.Date, T.Act, (SELECT
COUNT(*)
FROM [FrankM] T1
WHERE T1.Rep&T1.Prod = T.Rep&T.Prod) AS Rank
FROM FrankM AS T;

FrankM_2 ---
SELECT FrankM_1.Rank, FrankM_1.Rep, FrankM_1.Prod
FROM FrankM_1
WHERE (((Val([rep]+[Rank])*3254 Mod 3)0))
ORDER BY FrankM_1.Rep, FrankM_1.Prod;

SELECT FrankM_1.*
FROM FrankM_1 INNER JOIN FrankM_2 ON FrankM_1.Rank = FrankM_2.Rank
ORDER BY FrankM_1.Rep, FrankM_1.Prod;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

"FrankM" wrote:

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

If you have 50 reps and you return two products each, sounds to me like
that's 100 (e.g. 50 * 2). Please elaborate.

Bob

FrankM wrote:
I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200804/1

I'm sorry if I wasn't clear ... there are THREE Prods, I need TWO of each PER
Rep ... THREE by TWO is SIX. Six By 50 Reps is the 300 I mentioned. I think I
did say I have three Prods and need two of each.

"raskew via AccessMonster.com" wrote:

If you have 50 reps and you return two products each, sounds to me like
that's 100 (e.g. 50 * 2). Please elaborate.

Bob

FrankM wrote:
I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200804/1

I'm sorry if I wasn't clear ... there are THREE Prods, I need TWO of each PER
Rep ... THREE by TWO is SIX. Six By 50 Reps is the 300 I mentioned. I think I
did say I have three Prods and need two of each.

I will try your suggestion later this morning ... Thanks!



"KARL DEWEY" wrote:

Your math is wrong again - 50 Rep with 2 product each is 100. Try these
three queries (it will run slow).
FrankM_1 ---
SELECT T.Site, T.Rep, T.Cust, T.ID, T.Prod, T.Serv, T.Date, T.Act, (SELECT
COUNT(*)
FROM [FrankM] T1
WHERE T1.Rep&T1.Prod = T.Rep&T.Prod) AS Rank
FROM FrankM AS T;

FrankM_2 ---
SELECT FrankM_1.Rank, FrankM_1.Rep, FrankM_1.Prod
FROM FrankM_1
WHERE (((Val([rep]+[Rank])*3254 Mod 3)0))
ORDER BY FrankM_1.Rep, FrankM_1.Prod;

SELECT FrankM_1.*
FROM FrankM_1 INNER JOIN FrankM_2 ON FrankM_1.Rank = FrankM_2.Rank
ORDER BY FrankM_1.Rep, FrankM_1.Prod;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

"FrankM" wrote:

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

Frank,

Your explanation is still a little vague. Do you mean that you want records
for each of the Reps, and each of the possible permutations of the three
products, taken two at a time? Like:

Rep Prod1 Prod2
John A B
John B A
John A C
John C A
John B C
John C B

If so, then your reference to a random selection does not make any sense.
If you want one of these combinations (technically, these are permutations,
not combinations). There are only 3 possible combinations of 3 objects (A,
B, C), they would be (A, B), (A,C), (B,C).

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"FrankM" wrote:

I'm sorry if I wasn't clear ... there are THREE Prods, I need TWO of each PER
Rep ... THREE by TWO is SIX. Six By 50 Reps is the 300 I mentioned. I think I
did say I have three Prods and need two of each.

"raskew via AccessMonster.com" wrote:

If you have 50 reps and you return two products each, sounds to me like
that's 100 (e.g. 50 * 2). Please elaborate.

Bob

FrankM wrote:
I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200804/1

I'm sorry for the confusion. Clearly I need to work on my explanations.

The three prods can show up multiple times for each Rep (since there are
4,000 records currently and only 50 Reps and 3 Prods you can see that each
Rep will likely have many entries for each Prod). I need to randomly select
two of each Prod per Rep.

Prod1
Prod2
Prod3

Rep1
Rep2

I need ...

Rep1 Prod1a
Rep1 Prod1b
Rep1 Prod2a
Rep1 Prod2b
Rep1 Prod3a
Rep1 Prod3b
Rep2 Prod1a
Rep2 Prod1b
Rep2 Prod2a
Rep2 Prod2b
Rep2 Prod3a
Rep2 Prod3b

I hope that helps. I really appreciate the assistance.



"Dale Fye" wrote:

Frank,

Your explanation is still a little vague. Do you mean that you want records
for each of the Reps, and each of the possible permutations of the three
products, taken two at a time? Like:

Rep Prod1 Prod2
John A B
John B A
John A C
John C A
John B C
John C B

If so, then your reference to a random selection does not make any sense.
If you want one of these combinations (technically, these are permutations,
not combinations). There are only 3 possible combinations of 3 objects (A,
B, C), they would be (A, B), (A,C), (B,C).

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"FrankM" wrote:

I'm sorry if I wasn't clear ... there are THREE Prods, I need TWO of each PER
Rep ... THREE by TWO is SIX. Six By 50 Reps is the 300 I mentioned. I think I
did say I have three Prods and need two of each.

"raskew via AccessMonster.com" wrote:

If you have 50 reps and you return two products each, sounds to me like
that's 100 (e.g. 50 * 2). Please elaborate.

Bob

FrankM wrote:
I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200804/1

Try this assuming your ID field is an Autonumber field --
SELECT Q.ID, Q.Site, Q.Rep, Q.Cust, Q.Prod, Q.Serv, Q.Date, Q.Act
FROM FrankM AS Q
WHERE ((((SELECT COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep]
AND Q1.ID Q.ID)+1)=6))
ORDER BY Q.Rep;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I'm sorry for the confusion. Clearly I need to work on my explanations.

The three prods can show up multiple times for each Rep (since there are
4,000 records currently and only 50 Reps and 3 Prods you can see that each
Rep will likely have many entries for each Prod). I need to randomly select
two of each Prod per Rep.

Prod1
Prod2
Prod3

Rep1
Rep2

I need ...

Rep1 Prod1a
Rep1 Prod1b
Rep1 Prod2a
Rep1 Prod2b
Rep1 Prod3a
Rep1 Prod3b
Rep2 Prod1a
Rep2 Prod1b
Rep2 Prod2a
Rep2 Prod2b
Rep2 Prod3a
Rep2 Prod3b

I hope that helps. I really appreciate the assistance.



"Dale Fye" wrote:

Frank,

Your explanation is still a little vague. Do you mean that you want records
for each of the Reps, and each of the possible permutations of the three
products, taken two at a time? Like:

Rep Prod1 Prod2
John A B
John B A
John A C
John C A
John B C
John C B

If so, then your reference to a random selection does not make any sense.
If you want one of these combinations (technically, these are permutations,
not combinations). There are only 3 possible combinations of 3 objects (A,
B, C), they would be (A, B), (A,C), (B,C).

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"FrankM" wrote:

I'm sorry if I wasn't clear ... there are THREE Prods, I need TWO of each PER
Rep ... THREE by TWO is SIX. Six By 50 Reps is the 300 I mentioned. I think I
did say I have three Prods and need two of each.

"raskew via AccessMonster.com" wrote:

If you have 50 reps and you return two products each, sounds to me like
that's 100 (e.g. 50 * 2). Please elaborate.

Bob

FrankM wrote:
I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200804/1

Unfortunately, when I put this code in a got an error message saying cannot
find input Table or Query FrankM. Does this code build a temporary Table Q1?


"KARL DEWEY" wrote:

Try this assuming your ID field is an Autonumber field --
SELECT Q.ID, Q.Site, Q.Rep, Q.Cust, Q.Prod, Q.Serv, Q.Date, Q.Act
FROM FrankM AS Q
WHERE ((((SELECT COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep]
AND Q1.ID Q.ID)+1)=6))
ORDER BY Q.Rep;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I'm sorry for the confusion. Clearly I need to work on my explanations.

The three prods can show up multiple times for each Rep (since there are
4,000 records currently and only 50 Reps and 3 Prods you can see that each
Rep will likely have many entries for each Prod). I need to randomly select
two of each Prod per Rep.

Prod1
Prod2
Prod3

Rep1
Rep2

I need ...

Rep1 Prod1a
Rep1 Prod1b
Rep1 Prod2a
Rep1 Prod2b
Rep1 Prod3a
Rep1 Prod3b
Rep2 Prod1a
Rep2 Prod1b
Rep2 Prod2a
Rep2 Prod2b
Rep2 Prod3a
Rep2 Prod3b

I hope that helps. I really appreciate the assistance.



"Dale Fye" wrote:

Frank,

Your explanation is still a little vague. Do you mean that you want records
for each of the Reps, and each of the possible permutations of the three
products, taken two at a time? Like:

Rep Prod1 Prod2
John A B
John B A
John A C
John C A
John B C
John C B

If so, then your reference to a random selection does not make any sense.
If you want one of these combinations (technically, these are permutations,
not combinations). There are only 3 possible combinations of 3 objects (A,
B, C), they would be (A, B), (A,C), (B,C).

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"FrankM" wrote:

I'm sorry if I wasn't clear ... there are THREE Prods, I need TWO of each PER
Rep ... THREE by TWO is SIX. Six By 50 Reps is the 300 I mentioned. I think I
did say I have three Prods and need two of each.

"raskew via AccessMonster.com" wrote:

If you have 50 reps and you return two products each, sounds to me like
that's 100 (e.g. 50 * 2). Please elaborate.

Bob

FrankM wrote:
I actually had my math wrong. Three Prods selecting two by 50 Reps would
really be 300. It would have been 150 if I was only selecting one Prod per
Rep but I need two Prods per Rep. Again randomly selected.

I have a Table (Q1) with several fields (Site, Rep, Cust, ID, Prod, Serv,
Date, Act). I need to randomly make a couple of selections. There are three
"Prod" types. I need to select two "Prod" per Rep. There are about fifty Reps
so my end number of entries should be 150. The Table has about 4,000 entries
right now. The 150 have to be completely random. Any suggestions. I'm racking
my brains and getting nowhere. Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200804/1