using a puesdo record/autonumber in a make table query

[efandango]

I want to use a make table(or append) query to generate a suequence of
numbers to go with record lines so that I can use the same generated table
across two 'identical' forms, where in form 1, I can use the first lower
values of the numbered records, and in form two, I can use the higher values.

What code do I need to generate a number sequence in my query. I would
prefer numbers 1-18, but if neccesary, I would settle for a random range,
just so long as they were sequential and that I could demarcate them across
the two forms.


My table for the query already has an autonumber field.

 

[Lord Kelvan]

what are the fields in your query and your table name to generate that
you need to do a comparasion query it is good to have something unique
in your table such as an auto number if you have that and you want
another list of numbers based on the filtered records then it should
be simple enough i would also need to know the filter conditions for
your query

you could play with this if you cannot get it working you can post the
above requested information and ill do it for you

recordnum: (select count(subtable.autonumfield)+1 from thetable as
subtable where subtable.autonumberfield < thetable.autonumberfield and
allfilterconditionsonthemainquery)

hope this helps

Regards
Kelvan

 

[efandango]

I can't entirely follow your SQL suggestion. I think i understand the logic,
but can't deal with the syntax.

This is my SQL:

INSERT INTO tbl_Point_2_Point_2 ( Run_No, Point_ID1, Run_point_Venue,
Run_point_Address, Run_Point_Postcode )
SELECT DISTINCTROW TOP 17 tbl_Points.Run_No, tbl_Points.Point_ID,
tbl_Points.Run_point_Venue, tbl_Points.Run_point_Address,
tbl_Points.Run_Point_Postcode
FROM tbl_Points
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))
ORDER BY Rnd([tbl_Points.Point_ID]), tbl_Points.Run_point_Venue;



Going into (or making) this Table:

Point2Point_ID (Autoumber)
Run_No Number
Run_Point_Venue Text
Run_Point_Address Text
Run_Point_Postcode Text
Point_ID Number

 

[Lord Kelvan]

this is a guess

INSERT INTO tbl_Point_2_Point_2 ( Point2Point_ID,Run_No, Point_ID1,
Run_point_Venue,
Run_point_Address, Run_Point_Postcode )
SELECT DISTINCTROW TOP 17 (select count(subtable.Point_ID)+1 from
tbl_Points as
subtable where subtable.Point_ID < tbl_Points.Point_ID and
(((subtable.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))) as recordnum,
tbl_Points.Run_No, tbl_Points.Point_ID,
tbl_Points.Run_point_Venue, tbl_Points.Run_point_Address,
tbl_Points.Run_Point_Postcode
FROM tbl_Points
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))
ORDER BY Rnd([tbl_Points.Point_ID]), tbl_Points.Run_point_Venue;


that should work but is untested

Regards
Kelvan

 

[efandango]

Good guess!...

though a slight problem is that if I try to sort the order for recordnum, I
get error: incomplete query clause. If this is a problem I am stucj with,
then so be it, but how would I tell my two forms (A & B), to just show the
first 9 records and the 2nd form to show the other (last) 9 records?

 

[efandango]

Ahhh!, I see what you have cleverly done now... you have appended the
recordnum to the Point2PointID, and they are beautifully sequential!

thanks so much for the very quick response Kelvan. much appreciated.

Good guess!...

though a slight problem is that if I try to sort the order for recordnum, I
get error: incomplete query clause. If this is a problem I am stucj with,
then so be it, but how would I tell my two forms (A & B), to just show the
first 9 records and the 2nd form to show the other (last) 9 records?

this is a guess

INSERT INTO tbl_Point_2_Point_2 ( Point2Point_ID,Run_No, Point_ID1,
Run_point_Venue,
Run_point_Address, Run_Point_Postcode )
SELECT DISTINCTROW TOP 17 (select count(subtable.Point_ID)+1 from
tbl_Points as
subtable where subtable.Point_ID < tbl_Points.Point_ID and
(((subtable.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))) as recordnum,
tbl_Points.Run_No, tbl_Points.Point_ID,
tbl_Points.Run_point_Venue, tbl_Points.Run_point_Address,
tbl_Points.Run_Point_Postcode
FROM tbl_Points
WHERE (((tbl_Points.Run_No) Between
[Forms]![frm_Runs].[cbo_Point2Point_From] And
[Forms]![frm_Runs].[cbo_Point2Point_To]))
ORDER BY Rnd([tbl_Points.Point_ID]), tbl_Points.Run_point_Venue;


that should work but is untested

Regards
Kelvan

 

[Lord Kelvan]

yeah no problem glad to help

Regards
Kelvan