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.
:
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
:
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
:
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
:
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.
:
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.
:
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.
:
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!