Random Selection

Substitute your table name (tbl_Customers?) for "FrankM" in the query,in both
places.

It doesn't build a temporary table, as such, it uses a subquery and to
distinguish between "FrankM" in the main part of the query and in the
subquery, you need to identify which version of the table is being used, so
Karl has created aliass Q and Q1 for the two different copies of "FrankM"

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

email address is invalid
Please reply to newsgroup only.



"FrankM" wrote:

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

Thank you; that makes sense.

"Dale Fye" wrote:

Substitute your table name (tbl_Customers?) for "FrankM" in the query,in both
places.

It doesn't build a temporary table, as such, it uses a subquery and to
distinguish between "FrankM" in the main part of the query and in the
subquery, you need to identify which version of the table is being used, so
Karl has created aliass Q and Q1 for the two different copies of "FrankM"

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

email address is invalid
Please reply to newsgroup only.



"FrankM" wrote:

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

Two questions ...

Instead of running this Query based off of the Table can I instead base this
Query off of a separate Query that uses the Randomizer feature to mix-up the
entries. It is important that the results not be the first couple of records
for each Rep but be a random selection. I think this is no big deal and I'm
going to try it anyway ... I'll just substitute my new Random Query for
FrankM.

Second and more important. When I ran this Query it seemed to miss one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6 entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2 of each
Prod. Example, I ran the Query, which ran great thank you for that, and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any suggestions?

Thank you so much for all of your assistance. This has been very, very
helpful and has saved my sanity



"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

for the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c,
1d and Prod 2 2a, 2b but no Prod 3.

1a, 1b, 1c, 1d is four different products by the way I count.

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

Two questions ...

Instead of running this Query based off of the Table can I instead base this
Query off of a separate Query that uses the Randomizer feature to mix-up the
entries. It is important that the results not be the first couple of records
for each Rep but be a random selection. I think this is no big deal and I'm
going to try it anyway ... I'll just substitute my new Random Query for
FrankM.

Second and more important. When I ran this Query it seemed to miss one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6 entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2 of each
Prod. Example, I ran the Query, which ran great thank you for that, and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any suggestions?

Thank you so much for all of your assistance. This has been very, very
helpful and has saved my sanity



"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

I'm sorry, again, I am not communicating well.

What I meant by 1a, 1b, 1c, 1d is that it is Prod 1, showing up four times.
The "a", "b", "c" and "d" were just meant to show that it is a new record
with the same Prod.

ID, Site, Rep, Cust, Prod, Serv, Date, Act

123, here, Jack, ABC Company, Prod1, Serv1, 4/3/08, Act
234, here, Jack, XYZ Company, Prod1, Serv2, 4/2/08, Act
345, here, Jack, CDE Company, Prod1, Serv1, 4/1/08, Act
567, here, Jack, QWE Company, Prod1 Serv3, 4/1/08, Act
678, here, Jack, ASD Company, Prod2, Serv1, 4/2/08, Act
890, here, Jack ZXC Company, Prod2, Serv2, 4/3/08, Act

I have Prod1, Prod2 and Prod3 ... I need two of each selected (randomly) in
the above example there is no Prod3 but there is Prod1 four times (I
originally referenced this as 1a, 1b, 1c, 1d just to show that the same Prod
was being selected but from different records).

I really hope that helps and I'm very sorry for the confusion. I have to
work on explaining things better. I'm sorry. I really do appreciate your
assistance.




"KARL DEWEY" wrote:

for the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c,
1d and Prod 2 2a, 2b but no Prod 3.

1a, 1b, 1c, 1d is four different products by the way I count.

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

Two questions ...

Instead of running this Query based off of the Table can I instead base this
Query off of a separate Query that uses the Randomizer feature to mix-up the
entries. It is important that the results not be the first couple of records
for each Rep but be a random selection. I think this is no big deal and I'm
going to try it anyway ... I'll just substitute my new Random Query for
FrankM.

Second and more important. When I ran this Query it seemed to miss one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6 entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2 of each
Prod. Example, I ran the Query, which ran great thank you for that, and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any suggestions?

Thank you so much for all of your assistance. This has been very, very
helpful and has saved my sanity



"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

One mo time --
SELECT Q.ID, Q.Site, Q.Rep, Q.Cust, Q.Prod, Q.Serv, Q.Date, Q.Act, (SELECT
COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep]
AND Q1.ID Q.ID)+1 AS Expr1
FROM FrankM AS Q
WHERE ((((SELECT COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep] AND Q1.[Prod] = Q.[Prod]
AND Q1.ID Q.ID)+1)=2))
ORDER BY Q.Rep;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I'm sorry, again, I am not communicating well.

What I meant by 1a, 1b, 1c, 1d is that it is Prod 1, showing up four times.
The "a", "b", "c" and "d" were just meant to show that it is a new record
with the same Prod.

ID, Site, Rep, Cust, Prod, Serv, Date, Act

123, here, Jack, ABC Company, Prod1, Serv1, 4/3/08, Act
234, here, Jack, XYZ Company, Prod1, Serv2, 4/2/08, Act
345, here, Jack, CDE Company, Prod1, Serv1, 4/1/08, Act
567, here, Jack, QWE Company, Prod1 Serv3, 4/1/08, Act
678, here, Jack, ASD Company, Prod2, Serv1, 4/2/08, Act
890, here, Jack ZXC Company, Prod2, Serv2, 4/3/08, Act

I have Prod1, Prod2 and Prod3 ... I need two of each selected (randomly) in
the above example there is no Prod3 but there is Prod1 four times (I
originally referenced this as 1a, 1b, 1c, 1d just to show that the same Prod
was being selected but from different records).

I really hope that helps and I'm very sorry for the confusion. I have to
work on explaining things better. I'm sorry. I really do appreciate your
assistance.




"KARL DEWEY" wrote:

for the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c,
1d and Prod 2 2a, 2b but no Prod 3.

1a, 1b, 1c, 1d is four different products by the way I count.

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

Two questions ...

Instead of running this Query based off of the Table can I instead base this
Query off of a separate Query that uses the Randomizer feature to mix-up the
entries. It is important that the results not be the first couple of records
for each Rep but be a random selection. I think this is no big deal and I'm
going to try it anyway ... I'll just substitute my new Random Query for
FrankM.

Second and more important. When I ran this Query it seemed to miss one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6 entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2 of each
Prod. Example, I ran the Query, which ran great thank you for that, and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any suggestions?

Thank you so much for all of your assistance. This has been very, very
helpful and has saved my sanity



"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

Karl,

I admire your patience.

Dale

"KARL DEWEY" wrote in message
...
One mo time --
SELECT Q.ID, Q.Site, Q.Rep, Q.Cust, Q.Prod, Q.Serv, Q.Date, Q.Act, (SELECT
COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep]
AND Q1.ID Q.ID)+1 AS Expr1
FROM FrankM AS Q
WHERE ((((SELECT COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep] AND Q1.[Prod] = Q.[Prod]
AND Q1.ID Q.ID)+1)=2))
ORDER BY Q.Rep;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I'm sorry, again, I am not communicating well.

What I meant by 1a, 1b, 1c, 1d is that it is Prod 1, showing up four
times.
The "a", "b", "c" and "d" were just meant to show that it is a new record
with the same Prod.

ID, Site, Rep, Cust, Prod, Serv, Date, Act

123, here, Jack, ABC Company, Prod1, Serv1, 4/3/08, Act
234, here, Jack, XYZ Company, Prod1, Serv2, 4/2/08, Act
345, here, Jack, CDE Company, Prod1, Serv1, 4/1/08, Act
567, here, Jack, QWE Company, Prod1 Serv3, 4/1/08, Act
678, here, Jack, ASD Company, Prod2, Serv1, 4/2/08, Act
890, here, Jack ZXC Company, Prod2, Serv2, 4/3/08, Act

I have Prod1, Prod2 and Prod3 ... I need two of each selected (randomly)
in
the above example there is no Prod3 but there is Prod1 four times (I
originally referenced this as 1a, 1b, 1c, 1d just to show that the same
Prod
was being selected but from different records).

I really hope that helps and I'm very sorry for the confusion. I have to
work on explaining things better. I'm sorry. I really do appreciate your
assistance.




"KARL DEWEY" wrote:

for the first Rep on the results there were 2 Prods. Prod 1 ... 1a,
1b, 1c,
1d and Prod 2 2a, 2b but no Prod 3.

1a, 1b, 1c, 1d is four different products by the way I count.

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

Two questions ...

Instead of running this Query based off of the Table can I instead
base this
Query off of a separate Query that uses the Randomizer feature to
mix-up the
entries. It is important that the results not be the first couple of
records
for each Rep but be a random selection. I think this is no big deal
and I'm
going to try it anyway ... I'll just substitute my new Random Query
for
FrankM.

Second and more important. When I ran this Query it seemed to miss
one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6
entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2
of each
Prod. Example, I ran the Query, which ran great thank you for that,
and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b,
1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to
make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results
to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any
suggestions?

Thank you so much for all of your assistance. This has been very,
very
helpful and has saved my sanity



"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

That is PERFECT! It did exactly what I needed it to do. Thank you very, very
much. I am very grateful.


"KARL DEWEY" wrote:

One mo time --
SELECT Q.ID, Q.Site, Q.Rep, Q.Cust, Q.Prod, Q.Serv, Q.Date, Q.Act, (SELECT
COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep]
AND Q1.ID Q.ID)+1 AS Expr1
FROM FrankM AS Q
WHERE ((((SELECT COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep] AND Q1.[Prod] = Q.[Prod]
AND Q1.ID Q.ID)+1)=2))
ORDER BY Q.Rep;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I'm sorry, again, I am not communicating well.

What I meant by 1a, 1b, 1c, 1d is that it is Prod 1, showing up four times.
The "a", "b", "c" and "d" were just meant to show that it is a new record
with the same Prod.

ID, Site, Rep, Cust, Prod, Serv, Date, Act

123, here, Jack, ABC Company, Prod1, Serv1, 4/3/08, Act
234, here, Jack, XYZ Company, Prod1, Serv2, 4/2/08, Act
345, here, Jack, CDE Company, Prod1, Serv1, 4/1/08, Act
567, here, Jack, QWE Company, Prod1 Serv3, 4/1/08, Act
678, here, Jack, ASD Company, Prod2, Serv1, 4/2/08, Act
890, here, Jack ZXC Company, Prod2, Serv2, 4/3/08, Act

I have Prod1, Prod2 and Prod3 ... I need two of each selected (randomly) in
the above example there is no Prod3 but there is Prod1 four times (I
originally referenced this as 1a, 1b, 1c, 1d just to show that the same Prod
was being selected but from different records).

I really hope that helps and I'm very sorry for the confusion. I have to
work on explaining things better. I'm sorry. I really do appreciate your
assistance.




"KARL DEWEY" wrote:

for the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c,
1d and Prod 2 2a, 2b but no Prod 3.

1a, 1b, 1c, 1d is four different products by the way I count.

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

Two questions ...

Instead of running this Query based off of the Table can I instead base this
Query off of a separate Query that uses the Randomizer feature to mix-up the
entries. It is important that the results not be the first couple of records
for each Rep but be a random selection. I think this is no big deal and I'm
going to try it anyway ... I'll just substitute my new Random Query for
FrankM.

Second and more important. When I ran this Query it seemed to miss one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6 entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2 of each
Prod. Example, I ran the Query, which ran great thank you for that, and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b, 1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any suggestions?

Thank you so much for all of your assistance. This has been very, very
helpful and has saved my sanity



"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

As do I.

"Dale Fye" wrote:

Karl,

I admire your patience.

Dale

"KARL DEWEY" wrote in message
...
One mo time --
SELECT Q.ID, Q.Site, Q.Rep, Q.Cust, Q.Prod, Q.Serv, Q.Date, Q.Act, (SELECT
COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep]
AND Q1.ID Q.ID)+1 AS Expr1
FROM FrankM AS Q
WHERE ((((SELECT COUNT(*) FROM FrankM Q1
WHERE Q1.[Rep] = Q.[Rep] AND Q1.[Prod] = Q.[Prod]
AND Q1.ID Q.ID)+1)=2))
ORDER BY Q.Rep;

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

I'm sorry, again, I am not communicating well.

What I meant by 1a, 1b, 1c, 1d is that it is Prod 1, showing up four
times.
The "a", "b", "c" and "d" were just meant to show that it is a new record
with the same Prod.

ID, Site, Rep, Cust, Prod, Serv, Date, Act

123, here, Jack, ABC Company, Prod1, Serv1, 4/3/08, Act
234, here, Jack, XYZ Company, Prod1, Serv2, 4/2/08, Act
345, here, Jack, CDE Company, Prod1, Serv1, 4/1/08, Act
567, here, Jack, QWE Company, Prod1 Serv3, 4/1/08, Act
678, here, Jack, ASD Company, Prod2, Serv1, 4/2/08, Act
890, here, Jack ZXC Company, Prod2, Serv2, 4/3/08, Act

I have Prod1, Prod2 and Prod3 ... I need two of each selected (randomly)
in
the above example there is no Prod3 but there is Prod1 four times (I
originally referenced this as 1a, 1b, 1c, 1d just to show that the same
Prod
was being selected but from different records).

I really hope that helps and I'm very sorry for the confusion. I have to
work on explaining things better. I'm sorry. I really do appreciate your
assistance.




"KARL DEWEY" wrote:

for the first Rep on the results there were 2 Prods. Prod 1 ... 1a,
1b, 1c,
1d and Prod 2 2a, 2b but no Prod 3.

1a, 1b, 1c, 1d is four different products by the way I count.

--
KARL DEWEY
Build a little - Test a little


"FrankM" wrote:

Two questions ...

Instead of running this Query based off of the Table can I instead
base this
Query off of a separate Query that uses the Randomizer feature to
mix-up the
entries. It is important that the results not be the first couple of
records
for each Rep but be a random selection. I think this is no big deal
and I'm
going to try it anyway ... I'll just substitute my new Random Query
for
FrankM.

Second and more important. When I ran this Query it seemed to miss
one part.
There are 3 Prods and I need to select 2 of each Prod; hence the 6
entries
per Rep. This does seem to give me 6 entries per Rep but it is not 2
of each
Prod. Example, I ran the Query, which ran great thank you for that,
and for
the first Rep on the results there were 2 Prods. Prod 1 ... 1a, 1b,
1c, 1d
and Prod 2 2a, 2b but no Prod 3. I took a look at my original data to
make
certain they had Prod 3 entries and they definitely did. I saw this
throughout the results so it looks like it just limited the results
to 6 per
Rep without scrutinizing the Prods. I hope that make sense. Any
suggestions?

Thank you so much for all of your assistance. This has been very,
very
helpful and has saved my sanity



"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