unique and optimized pairs of records - no dupes.

[efandango]

I have a table of addresses, 16 in each recordset. I want to create 8 pairs
of unique addresses from each record set.


for example:

Rec No Address

1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P



Would be:


Rec_No: Address Rec_No: Address_B

1 A 15 O
6 F 9 I
3 C 11 K
16 P 10 J
7 G 2 B
13 M 14 N
4 D 8 H
12 L 5 E


at the moment I have this SQL:

INSERT INTO tbl_Point_2_Point_2 ( Run_No, Run_point_Venue,
Run_point_Address, Run_Point_Postcode, Run_No_B, Run_point_Venue_B,
Run_point_Address_B, Run_Point_Postcode_B )
SELECT TOP 18 tbl_Points.Run_No, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points_1.Run_No AS Run_No_B, tbl_Points_1.Run_point_Venue AS
Run_point_Venue_B, tbl_Points_1.Run_point_Address AS Run_point_Address_B,
tbl_Points_1.Run_Point_Postcode AS Run_Point_Postcode_B
FROM tbl_Points INNER JOIN tbl_Points AS tbl_Points_1 ON tbl_Points.OrderSeq
= tbl_Points_1.OrderSeq
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points_1.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points.Custom_Point)=0))
ORDER BY Rnd([tbl_Points.Point_ID]), Rnd([tbl_Points_1.Point_ID]) DESC ,
tbl_Points.Custom_Point DESC;


which gives me 16 records (instead of 8) and has a duplicate of any given
address in either the first address field or the 2nd address_B field, and
sometimes both.

Like this: 2nd row and last row (6 F)

Rec_No: Address Rec_No: Address_B

1 A 15 O
6 F 9 I
3 C 11 K
16 P 10 J
7 G 2 B
13 M 14 N
4 D 8 H
12 L 6 F

I want it so that the (example 6F) can only ever appear once, in just one
column (it is not important which row they appear in).

 

[John Spencer]

Are there any rules on creating the pairs?
Or are you trying to create more or less random pairs?
Is order within the pair of records important - that is does the first
member of the pair need to be the lower number - or is order random
within the pairs?


It would be simple to pair records as follows
1-9
2-10
3-11
4-12
5-13
6-14
7-15
8-16

And not too difficult to pair
1-16
2-15
....
8-9





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[efandango]

The order doesn't matter at all. Just so long as I can get 8 pairs of
addresses, where no address appears twice in either column.


at the moment I am trying another angle, using 2 queries where I am using
the Order_Seq field, to say in the first query any records with an OrderSeq
of <8 and in the other query, any record with an OrderSeq of >9; and then
join them together in the 3rd query, but for some reason the first column
repeats the same address for all 8 rows.


The OrderSeq field is a field that sequences the addresses in a certain
order for use elsewhere in another unrelated situation, but by its
defination, it is also a unique (manual) number that I comes in handy for
sorting and delineating records in other circumstances such as this one.

Are there any rules on creating the pairs?
Or are you trying to create more or less random pairs?
Is order within the pair of records important - that is does the first
member of the pair need to be the lower number - or is order random
within the pairs?


It would be simple to pair records as follows
1-9
2-10
3-11
4-12
5-13
6-14
7-15
8-16

And not too difficult to pair
1-16
2-15
....
8-9





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a table of addresses, 16 in each recordset. I want to create 8 pairs
of unique addresses from each record set.


for example:

Rec No Address

1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P



Would be:


Rec_No: Address Rec_No: Address_B

1 A 15 O
6 F 9 I
3 C 11 K
16 P 10 J
7 G 2 B
13 M 14 N
4 D 8 H
12 L 5 E


at the moment I have this SQL:

INSERT INTO tbl_Point_2_Point_2 ( Run_No, Run_point_Venue,
Run_point_Address, Run_Point_Postcode, Run_No_B, Run_point_Venue_B,
Run_point_Address_B, Run_Point_Postcode_B )
SELECT TOP 18 tbl_Points.Run_No, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points_1.Run_No AS Run_No_B, tbl_Points_1.Run_point_Venue AS
Run_point_Venue_B, tbl_Points_1.Run_point_Address AS Run_point_Address_B,
tbl_Points_1.Run_Point_Postcode AS Run_Point_Postcode_B
FROM tbl_Points INNER JOIN tbl_Points AS tbl_Points_1 ON tbl_Points.OrderSeq
= tbl_Points_1.OrderSeq
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points_1.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points.Custom_Point)=0))
ORDER BY Rnd([tbl_Points.Point_ID]), Rnd([tbl_Points_1.Point_ID]) DESC ,
tbl_Points.Custom_Point DESC;


which gives me 16 records (instead of 8) and has a duplicate of any given
address in either the first address field or the 2nd address_B field, and
sometimes both.

Like this: 2nd row and last row (6 F)

Rec_No: Address Rec_No: Address_B

1 A 15 O
6 F 9 I
3 C 11 K
16 P 10 J
7 G 2 B
13 M 14 N
4 D 8 H
12 L 6 F

I want it so that the (example 6F) can only ever appear once, in just one
column (it is not important which row they appear in).

 

[efandango]

John,

I re-read the first line of my response, the order does matter to an extent;
in so much as I want the order to be random, so that each time i refresh the
query, I get a different combination. This way, the user doesn't get used to
the same patterns.

The order doesn't matter at all. Just so long as I can get 8 pairs of
addresses, where no address appears twice in either column.


at the moment I am trying another angle, using 2 queries where I am using
the Order_Seq field, to say in the first query any records with an OrderSeq
of <8 and in the other query, any record with an OrderSeq of >9; and then
join them together in the 3rd query, but for some reason the first column
repeats the same address for all 8 rows.


The OrderSeq field is a field that sequences the addresses in a certain
order for use elsewhere in another unrelated situation, but by its
defination, it is also a unique (manual) number that I comes in handy for
sorting and delineating records in other circumstances such as this one.

Are there any rules on creating the pairs?
Or are you trying to create more or less random pairs?
Is order within the pair of records important - that is does the first
member of the pair need to be the lower number - or is order random
within the pairs?


It would be simple to pair records as follows
1-9
2-10
3-11
4-12
5-13
6-14
7-15
8-16

And not too difficult to pair
1-16
2-15
....
8-9





'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a table of addresses, 16 in each recordset. I want to create 8 pairs
of unique addresses from each record set.


for example:

Rec No Address

1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J
11 K
12 L
13 M
14 N
15 O
16 P



Would be:


Rec_No: Address Rec_No: Address_B

1 A 15 O
6 F 9 I
3 C 11 K
16 P 10 J
7 G 2 B
13 M 14 N
4 D 8 H
12 L 5 E


at the moment I have this SQL:

INSERT INTO tbl_Point_2_Point_2 ( Run_No, Run_point_Venue,
Run_point_Address, Run_Point_Postcode, Run_No_B, Run_point_Venue_B,
Run_point_Address_B, Run_Point_Postcode_B )
SELECT TOP 18 tbl_Points.Run_No, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points_1.Run_No AS Run_No_B, tbl_Points_1.Run_point_Venue AS
Run_point_Venue_B, tbl_Points_1.Run_point_Address AS Run_point_Address_B,
tbl_Points_1.Run_Point_Postcode AS Run_Point_Postcode_B
FROM tbl_Points INNER JOIN tbl_Points AS tbl_Points_1 ON tbl_Points.OrderSeq
= tbl_Points_1.OrderSeq
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points_1.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points.Custom_Point)=0))
ORDER BY Rnd([tbl_Points.Point_ID]), Rnd([tbl_Points_1.Point_ID]) DESC ,
tbl_Points.Custom_Point DESC;


which gives me 16 records (instead of 8) and has a duplicate of any given
address in either the first address field or the 2nd address_B field, and
sometimes both.

Like this: 2nd row and last row (6 F)

Rec_No: Address Rec_No: Address_B

1 A 15 O
6 F 9 I
3 C 11 K
16 P 10 J
7 G 2 B
13 M 14 N
4 D 8 H
12 L 6 F

I want it so that the (example 6F) can only ever appear once, in just one
column (it is not important which row they appear in).

 

[John Spencer]

Assuming 16 records in the table and the Rec_No is a number field, you
could use a query like the following to assign a random position number.

You may need to use a temporary table

INSERT INTO WorkTable (Position,Rec_No,Address)
SELECT Int(Rnd(Rec_No)*16)+1

 

[efandango]

John,

thanks for replying. To be honest, i'm at a loss to know how/wher to
implement your suggestion?

Is WorkTable a temporary table, or should I rename it to the actual table
name i am using?

I tried using:

but the query told me that the number of query values and destination fields
are not the same.


where would I put your code into this SQL:

INSERT INTO tbl_Point_2_Point_2 ( Run_No, Run_point_Venue,
Run_point_Address, Run_Point_Postcode, Run_No_B, Run_point_Venue_B,
Run_point_Address_B, Run_Point_Postcode_B, Point_ID1, Point_ID2 )
SELECT DISTINCTROW TOP 9 tbl_Points.Run_No, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points_1.Run_No AS Run_No_B, tbl_Points_1.Run_point_Venue AS
Run_point_Venue_B, tbl_Points_1.Run_point_Address AS Run_point_Address_B,
tbl_Points_1.Run_Point_Postcode AS Run_Point_Postcode_B, tbl_Points.Point_ID,
tbl_Points_1.Point_ID
FROM tbl_Points INNER JOIN tbl_Points AS tbl_Points_1 ON tbl_Points.Point_ID
= tbl_Points_1.Point_ID
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points_1.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points.Custom_Point)=0))
ORDER BY Rnd([tbl_Points.Point_ID]), Rnd([tbl_Points_1.Point_ID]),
tbl_Points.Custom_Point DESC , tbl_Points.OrderSeq, tbl_Points_1.OrderSeq
DESC;


INSERT INTO WorkTable (OrderSeq,Point_ID,Run_point_Address)
SELECT Int(Rnd(Point_ID_1)*16)+1

 

[John Spencer]

Sorry, for some reason my response got truncated. I may have to switch
to another newsreader. This has happened to me a few times lately.

Anyway, I said in my response, that I would have to revisit your
request. The solution that I started to proposed had a problem in that
it was liable to generate duplicates. In other words, it would not work.

I think that a simpler solution might be to use some VBA to generate the
result set. I don't have the time to work on that at the present.
Perhaps someone else will have a solution you can implement.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

thanks for replying. To be honest, i'm at a loss to know how/wher to
implement your suggestion?

Is WorkTable a temporary table, or should I rename it to the actual table
name i am using?

I tried using:

but the query told me that the number of query values and destination fields
are not the same.


where would I put your code into this SQL:

INSERT INTO tbl_Point_2_Point_2 ( Run_No, Run_point_Venue,
Run_point_Address, Run_Point_Postcode, Run_No_B, Run_point_Venue_B,
Run_point_Address_B, Run_Point_Postcode_B, Point_ID1, Point_ID2 )
SELECT DISTINCTROW TOP 9 tbl_Points.Run_No, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points_1.Run_No AS Run_No_B, tbl_Points_1.Run_point_Venue AS
Run_point_Venue_B, tbl_Points_1.Run_point_Address AS Run_point_Address_B,
tbl_Points_1.Run_Point_Postcode AS Run_Point_Postcode_B, tbl_Points.Point_ID,
tbl_Points_1.Point_ID
FROM tbl_Points INNER JOIN tbl_Points AS tbl_Points_1 ON tbl_Points.Point_ID
= tbl_Points_1.Point_ID
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points_1.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]) AND ((tbl_Points.Custom_Point)=0))
ORDER BY Rnd([tbl_Points.Point_ID]), Rnd([tbl_Points_1.Point_ID]),
tbl_Points.Custom_Point DESC , tbl_Points.OrderSeq, tbl_Points_1.OrderSeq
DESC;


INSERT INTO WorkTable (OrderSeq,Point_ID,Run_point_Address)
SELECT Int(Rnd(Point_ID_1)*16)+1

Assuming 16 records in the table and the Rec_No is a number field, you
could use a query like the following to assign a random position number.

You may need to use a temporary table

INSERT INTO WorkTable (Position,Rec_No,Address)
SELECT Int(Rnd(Rec_No)*16)+1