SQL SELECT range of records

[GraeagleBill]

Not clear from search if one can select a specific range of records in Access as it seems can be done from SQL Server. I want to load a few sub-forms like 30 records each, e.g., 1 to 30; 31 to 60; 61 to 90; etc. What's the method in a SELECT to specify such ranges?

Thanks,
Bill

[June7]

Only way I know is with unique ID.

[GraeagleBill]

Thanks June, I'll let the thread linger awhile to see if another thought emerges.
Bill

[isladogs]

Other than using an ID field, you could use a query to rank the records by one of the fields.
Allen Browne has several examples on his website
I prefer to use the serialize function which I explained in this thread from another forum https://www.access-programmers.co.uk...d.php?t=297922

Or SELECT TOP 30... For 1-30

For 31-60 first SELECT TOP 60, repeat SELECT TOP 30 then do an unmatched query on both to get 31-60 ... it works but a bit unwieldy

Or it's relatively easy to do this in a report where numbering can be generated

Is that enough thoughts for now?

[GraeagleBill]

Thanks Colin. I've essentially done a similar approach to that which you describe in your URL reference. Basically, I serialize the Recordset and create individual queries for each of the sub-forms. While that approach works perfectly its a pain in the posterior to maintain, which is behind my posting of this issue.
Thanks again,
Bill

[isladogs]

I don't believe there is an easier way of doing it ... except in a report.
Serialize is easier than multiple SELECT TOP in my view.
Just for my interest, why do you think it's a PITA to maintain?

[June7]

Wouldn't the nested query still depend on a unique identifier?

And couldn't the ranking return more than 30 records because of repeated values (tied values get the same rank)?

Don't know how report could accomplish because the numbering is done by RunningSum expression in textbox and numbers records of otherwise filtered recordsource.

Like roads to Rome, all leads back to unique ID.

[GraeagleBill]

I don't mean to make too much out of the PITA. It's just that if one has like 8 sub-forms one needs to maintain 8 separate queries. If I could be assured that query defs could be modified with code triggered from a RowSource =function as the sub-forms were being populated I would write such a function. Hardly what one might call a normalized DB............

[GraeagleBill]

You are absolutely correct. Unique serialization requires that the underlying table include a serialization field so the function can serialize the Recordset.

[June7]

One query, 8 forms with their own SQL statement in RecordSource.

Maybe Allen Browne's article on multiple instances of form would be useful http://allenbrowne.com/ser-35.html

[GraeagleBill]

Allen's multi-instances is an interesting read if nothing else.
Generally speaking, Access wasn't designed to deal with multi-column forms. If it were, one could easily maintain a completely normalized DB and link the relationships accordingly. As it is here, if "ONE INSISTS" that a hierarchical detectable tree be displayed similar to one that could be done with a report, one has to place parallel sub-forms upon the main form and create the corresponding RowSource queries accordingly. I don't know how else to get a SINGLE detectable display of a hierarchical tree any other way. (Note that this approach would not be of similar value if the number of records became substantial.)
Thanks,
Bill

[June7]

Yes, then multiple form objects each with their own SQL in RecordSource property but not 8 query objects.

Are these subforms used for data entry and not just displaying data?

[GraeagleBill]

Each of the sub-forms contain only a single text box control detectable but non-updatable so display yes, data entry no. How would one eliminate the need for 8 query objects without having a RecordSource function that modified a single query def as each sub-form is populated with a range of records? E.g., Serialization field criteria >=1 and <=30; >=30 and <=60; etc set and saved by function as each RecordSource is evaluated?

Ooops! I just re-read your post. I think now you mean to put the SQL SELECT statement directly as the RecordSource property.

I've only used this approach to mult-column forms once in the past and the queries were much more involved so my thinking was influenced by that experience.

[June7]

Or use form Filter and FilterOnLoad properties.

I have used listboxes to display different data from same data source. I was doing various aggregate calcs. Display for info only, locked and no event code.

[GraeagleBill]

Or use form Filter and FilterOnLoad properties.

I think your Filter suggestion is the cleanest. The "List" idea would be the best were it not for that requirement to employ conditional formatting and the use of field formatting within the query itself.

Thanks June, both you and Colin have been very helpful,
Bill