Make two recordsets from master table with unique & random records

E

efandango

I have a master table where I want to take 18 records based on a form textbox
criteria, then split them across two forms (forms 1 & 2) so that each form
contains 9 each of the sub recordset, and each recordset contains a unique
set of subrecords; in other words Form 1 cannot contain any records that
appear in Form 2, and vice-versa.
Then each time I requery the forms, they display then in different (random)
order.

My master table is this:

Point_ID: Autonumber
Run_No: Number (used for form criteria)
Run_point_Venue
Run_point_Address
Run_point_Postcode
 
K

KARL DEWEY

These queries will pull the records with 9 having field Set = "1" and another
9 having field Set = "2" so you can put different sets in different forms.
Change table name efandango_1 to what your table is called in first query
efandango_18.

efandango_18
SELECT TOP 18 efandango_1.Point_ID
FROM efandango_1
ORDER BY Rnd([Point_ID]) DESC;

efandango_9x1
SELECT TOP 9 efandango_18.Point_ID, 1 AS [Set]
FROM efandango_18
ORDER BY efandango_18.Point_ID DESC;

efandango_9x2
SELECT TOP 9 efandango_18.Point_ID, 2 AS [Set]
FROM efandango_18 LEFT JOIN efandango_9x1 ON efandango_18.Point_ID =
efandango_9x1.Point_ID
WHERE (((efandango_9x1.Point_ID) Is Null))
ORDER BY efandango_18.Point_ID DESC;

efandango_2_Set
SELECT efandango_1.Point_ID, efandango_9x1.Set, efandango_1.Run_No,
efandango_1.Run_point_Venue, efandango_1.Run_point_Address,
efandango_1.Run_point_Postcode
FROM efandango_9x1 INNER JOIN efandango_1 ON efandango_9x1.Point_ID =
efandango_1.Point_ID
UNION ALL SELECT efandango_1.Point_ID, efandango_9x2.Set,
efandango_1.Run_No, efandango_1.Run_point_Venue,
efandango_1.Run_point_Address, efandango_1.Run_point_Postcode
FROM efandango_9x2 INNER JOIN efandango_1 ON efandango_9x2.Point_ID =
efandango_1.Point_ID;
 
E

efandango

Karl,

That is great, I now have a working system where I have to forms using the
[Run_No] criteria to select the master recordset numbers. I have customised
the query names to more accurately reflect the database usage, and added a
few other fields mentioned further on. Now, I want to enhance this
functionality to include other fields, such as [Postcode], [Area],
[District], and [Type].

At the moment I have a pairs of buttons, each relating to a field subject
mentioned above. When I press the Area buttons on Form_A and Form_B for
example, I want the form to reflect the Areas chosen in the combo boxes. Then
have the same options for the other fields. So my question is, do I have to
create new adapted copies of the underlying queries, (one set for each field
subject) or is it possible to change the query criteria via the form with
some code? What is the best way to achieve this?




KARL DEWEY said:
These queries will pull the records with 9 having field Set = "1" and another
9 having field Set = "2" so you can put different sets in different forms.
Change table name efandango_1 to what your table is called in first query
efandango_18.

efandango_18
SELECT TOP 18 efandango_1.Point_ID
FROM efandango_1
ORDER BY Rnd([Point_ID]) DESC;

efandango_9x1
SELECT TOP 9 efandango_18.Point_ID, 1 AS [Set]
FROM efandango_18
ORDER BY efandango_18.Point_ID DESC;

efandango_9x2
SELECT TOP 9 efandango_18.Point_ID, 2 AS [Set]
FROM efandango_18 LEFT JOIN efandango_9x1 ON efandango_18.Point_ID =
efandango_9x1.Point_ID
WHERE (((efandango_9x1.Point_ID) Is Null))
ORDER BY efandango_18.Point_ID DESC;

efandango_2_Set
SELECT efandango_1.Point_ID, efandango_9x1.Set, efandango_1.Run_No,
efandango_1.Run_point_Venue, efandango_1.Run_point_Address,
efandango_1.Run_point_Postcode
FROM efandango_9x1 INNER JOIN efandango_1 ON efandango_9x1.Point_ID =
efandango_1.Point_ID
UNION ALL SELECT efandango_1.Point_ID, efandango_9x2.Set,
efandango_1.Run_No, efandango_1.Run_point_Venue,
efandango_1.Run_point_Address, efandango_1.Run_point_Postcode
FROM efandango_9x2 INNER JOIN efandango_1 ON efandango_9x2.Point_ID =
efandango_1.Point_ID;


efandango said:
I have a master table where I want to take 18 records based on a form textbox
criteria, then split them across two forms (forms 1 & 2) so that each form
contains 9 each of the sub recordset, and each recordset contains a unique
set of subrecords; in other words Form 1 cannot contain any records that
appear in Form 2, and vice-versa.
Then each time I requery the forms, they display then in different (random)
order.

My master table is this:

Point_ID: Autonumber
Run_No: Number (used for form criteria)
Run_point_Venue
Run_point_Address
Run_point_Postcode
Click to expand...
Click to expand...
 
K

KARL DEWEY

You can just add fields to efandango_2_Set query.

Add the new fields to both halfs of the union query.


efandango said:
Karl,

That is great, I now have a working system where I have to forms using the
[Run_No] criteria to select the master recordset numbers. I have customised
the query names to more accurately reflect the database usage, and added a
few other fields mentioned further on. Now, I want to enhance this
functionality to include other fields, such as [Postcode], [Area],
[District], and [Type].

At the moment I have a pairs of buttons, each relating to a field subject
mentioned above. When I press the Area buttons on Form_A and Form_B for
example, I want the form to reflect the Areas chosen in the combo boxes. Then
have the same options for the other fields. So my question is, do I have to
create new adapted copies of the underlying queries, (one set for each field
subject) or is it possible to change the query criteria via the form with
some code? What is the best way to achieve this?




KARL DEWEY said:
These queries will pull the records with 9 having field Set = "1" and another
9 having field Set = "2" so you can put different sets in different forms.
Change table name efandango_1 to what your table is called in first query
efandango_18.

efandango_18
SELECT TOP 18 efandango_1.Point_ID
FROM efandango_1
ORDER BY Rnd([Point_ID]) DESC;

efandango_9x1
SELECT TOP 9 efandango_18.Point_ID, 1 AS [Set]
FROM efandango_18
ORDER BY efandango_18.Point_ID DESC;

efandango_9x2
SELECT TOP 9 efandango_18.Point_ID, 2 AS [Set]
FROM efandango_18 LEFT JOIN efandango_9x1 ON efandango_18.Point_ID =
efandango_9x1.Point_ID
WHERE (((efandango_9x1.Point_ID) Is Null))
ORDER BY efandango_18.Point_ID DESC;

efandango_2_Set
SELECT efandango_1.Point_ID, efandango_9x1.Set, efandango_1.Run_No,
efandango_1.Run_point_Venue, efandango_1.Run_point_Address,
efandango_1.Run_point_Postcode
FROM efandango_9x1 INNER JOIN efandango_1 ON efandango_9x1.Point_ID =
efandango_1.Point_ID
UNION ALL SELECT efandango_1.Point_ID, efandango_9x2.Set,
efandango_1.Run_No, efandango_1.Run_point_Venue,
efandango_1.Run_point_Address, efandango_1.Run_point_Postcode
FROM efandango_9x2 INNER JOIN efandango_1 ON efandango_9x2.Point_ID =
efandango_1.Point_ID;


efandango said:
I have a master table where I want to take 18 records based on a form textbox
criteria, then split them across two forms (forms 1 & 2) so that each form
contains 9 each of the sub recordset, and each recordset contains a unique
set of subrecords; in other words Form 1 cannot contain any records that
appear in Form 2, and vice-versa.
Then each time I requery the forms, they display then in different (random)
order.

My master table is this:

Point_ID: Autonumber
Run_No: Number (used for form criteria)
Run_point_Venue
Run_point_Address
Run_point_Postcode
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Karl,

Ok, I understand how do add extra fields, but how do I change the query
criteria on the form; from say this 'baseline' example which uses the
[Run_from] and [Run_To] combo on each respective form, and relies on each
forms 'Run_No' combo box:

Between [Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To] And
<[Forms]![frm_Runs].[txt_Run_Limit]



To say, this example which uses each forms combo box for Area like this:
[Forms]![frm_Runs].[cbo_Point2Point_Area_From]

and

[Forms]![frm_Runs].[cbo_Point2Point_Area_To]


This is what my form looks like:

Form A

Combo_Run_No Combo_Postcode Combo_Area

btn_Run_No btn_Postcode btn_Area


I select an Area from the Combo, and then press the button beneath it, and
the query shows just records for the chosen Area. Which means it doesn't any
longer require the 'Run_To' criteria, therefore it cannot rely on that
particular 'baseline' query; hence my question, do I need a bunch of
different queries correpsonding with each combo/button scenario, or can it be
done from some form coding?




KARL DEWEY said:
You can just add fields to efandango_2_Set query.

Add the new fields to both halfs of the union query.


efandango said:
Karl,

That is great, I now have a working system where I have to forms using the
[Run_No] criteria to select the master recordset numbers. I have customised
the query names to more accurately reflect the database usage, and added a
few other fields mentioned further on. Now, I want to enhance this
functionality to include other fields, such as [Postcode], [Area],
[District], and [Type].

At the moment I have a pairs of buttons, each relating to a field subject
mentioned above. When I press the Area buttons on Form_A and Form_B for
example, I want the form to reflect the Areas chosen in the combo boxes. Then
have the same options for the other fields. So my question is, do I have to
create new adapted copies of the underlying queries, (one set for each field
subject) or is it possible to change the query criteria via the form with
some code? What is the best way to achieve this?




KARL DEWEY said:
These queries will pull the records with 9 having field Set = "1" and another
9 having field Set = "2" so you can put different sets in different forms.
Change table name efandango_1 to what your table is called in first query
efandango_18.

efandango_18
SELECT TOP 18 efandango_1.Point_ID
FROM efandango_1
ORDER BY Rnd([Point_ID]) DESC;

efandango_9x1
SELECT TOP 9 efandango_18.Point_ID, 1 AS [Set]
FROM efandango_18
ORDER BY efandango_18.Point_ID DESC;

efandango_9x2
SELECT TOP 9 efandango_18.Point_ID, 2 AS [Set]
FROM efandango_18 LEFT JOIN efandango_9x1 ON efandango_18.Point_ID =
efandango_9x1.Point_ID
WHERE (((efandango_9x1.Point_ID) Is Null))
ORDER BY efandango_18.Point_ID DESC;

efandango_2_Set
SELECT efandango_1.Point_ID, efandango_9x1.Set, efandango_1.Run_No,
efandango_1.Run_point_Venue, efandango_1.Run_point_Address,
efandango_1.Run_point_Postcode
FROM efandango_9x1 INNER JOIN efandango_1 ON efandango_9x1.Point_ID =
efandango_1.Point_ID
UNION ALL SELECT efandango_1.Point_ID, efandango_9x2.Set,
efandango_1.Run_No, efandango_1.Run_point_Venue,
efandango_1.Run_point_Address, efandango_1.Run_point_Postcode
FROM efandango_9x2 INNER JOIN efandango_1 ON efandango_9x2.Point_ID =
efandango_1.Point_ID;


:

I have a master table where I want to take 18 records based on a form textbox
criteria, then split them across two forms (forms 1 & 2) so that each form
contains 9 each of the sub recordset, and each recordset contains a unique
set of subrecords; in other words Form 1 cannot contain any records that
appear in Form 2, and vice-versa.
Then each time I requery the forms, they display then in different (random)
order.

My master table is this:

Point_ID: Autonumber
Run_No: Number (used for form criteria)
Run_point_Venue
Run_point_Address
Run_point_Postcode
Click to expand...
Click to expand...
Click to expand...
 
E

efandango

Karl,

Please disregard my previous post; I have solved the problem for now. And I
don't want to waste your valued time unnecessarily. For now, thank you so
much for your initial, elegant solution, and further feedback on the extra
fields; your help is much appreciated.

regards

Eric



efandango said:
Karl,

Ok, I understand how do add extra fields, but how do I change the query
criteria on the form; from say this 'baseline' example which uses the
[Run_from] and [Run_To] combo on each respective form, and relies on each
forms 'Run_No' combo box:

Between [Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To] And
<[Forms]![frm_Runs].[txt_Run_Limit]



To say, this example which uses each forms combo box for Area like this:
[Forms]![frm_Runs].[cbo_Point2Point_Area_From]

and

[Forms]![frm_Runs].[cbo_Point2Point_Area_To]


This is what my form looks like:

Form A

Combo_Run_No Combo_Postcode Combo_Area

btn_Run_No btn_Postcode btn_Area


I select an Area from the Combo, and then press the button beneath it, and
the query shows just records for the chosen Area. Which means it doesn't any
longer require the 'Run_To' criteria, therefore it cannot rely on that
particular 'baseline' query; hence my question, do I need a bunch of
different queries correpsonding with each combo/button scenario, or can it be
done from some form coding?




KARL DEWEY said:
You can just add fields to efandango_2_Set query.

Add the new fields to both halfs of the union query.


efandango said:
Karl,

That is great, I now have a working system where I have to forms using the
[Run_No] criteria to select the master recordset numbers. I have customised
the query names to more accurately reflect the database usage, and added a
few other fields mentioned further on. Now, I want to enhance this
functionality to include other fields, such as [Postcode], [Area],
[District], and [Type].

At the moment I have a pairs of buttons, each relating to a field subject
mentioned above. When I press the Area buttons on Form_A and Form_B for
example, I want the form to reflect the Areas chosen in the combo boxes. Then
have the same options for the other fields. So my question is, do I have to
create new adapted copies of the underlying queries, (one set for each field
subject) or is it possible to change the query criteria via the form with
some code? What is the best way to achieve this?




:

These queries will pull the records with 9 having field Set = "1" and another
9 having field Set = "2" so you can put different sets in different forms.
Change table name efandango_1 to what your table is called in first query
efandango_18.

efandango_18
SELECT TOP 18 efandango_1.Point_ID
FROM efandango_1
ORDER BY Rnd([Point_ID]) DESC;

efandango_9x1
SELECT TOP 9 efandango_18.Point_ID, 1 AS [Set]
FROM efandango_18
ORDER BY efandango_18.Point_ID DESC;

efandango_9x2
SELECT TOP 9 efandango_18.Point_ID, 2 AS [Set]
FROM efandango_18 LEFT JOIN efandango_9x1 ON efandango_18.Point_ID =
efandango_9x1.Point_ID
WHERE (((efandango_9x1.Point_ID) Is Null))
ORDER BY efandango_18.Point_ID DESC;

efandango_2_Set
SELECT efandango_1.Point_ID, efandango_9x1.Set, efandango_1.Run_No,
efandango_1.Run_point_Venue, efandango_1.Run_point_Address,
efandango_1.Run_point_Postcode
FROM efandango_9x1 INNER JOIN efandango_1 ON efandango_9x1.Point_ID =
efandango_1.Point_ID
UNION ALL SELECT efandango_1.Point_ID, efandango_9x2.Set,
efandango_1.Run_No, efandango_1.Run_point_Venue,
efandango_1.Run_point_Address, efandango_1.Run_point_Postcode
FROM efandango_9x2 INNER JOIN efandango_1 ON efandango_9x2.Point_ID =
efandango_1.Point_ID;


:

I have a master table where I want to take 18 records based on a form textbox
criteria, then split them across two forms (forms 1 & 2) so that each form
contains 9 each of the sub recordset, and each recordset contains a unique
set of subrecords; in other words Form 1 cannot contain any records that
appear in Form 2, and vice-versa.
Then each time I requery the forms, they display then in different (random)
order.

My master table is this:

Point_ID: Autonumber
Run_No: Number (used for form criteria)
Run_point_Venue
Run_point_Address
Run_point_Postcode
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Get a Max value from a continous form's (subset) records 5
unique and optimized pairs of records - no dupes. 6
Match multiple field records to any sub records. (complex....) 1
2 databases using the same autoumbers, how can I merge them into 1 7
Displaying records that have one of two fields equal to Master Field 3
refresh combo boxes with a random record 1
Post Exp Date in Master Rcd (row) from trans record 8
VBA Append Multiple Random Records to Row 4

Top