Snaking records on a form

[mcelligott]

I want to snake records on a form (just like on a report). I realize Access says I can't do this but is there a way I can manipulate it?

I have tried using two subforms that pull records from the same table. The records are sorted on the first two fields (agency then medic). Subform A needs to display the sorted records 1-10 and subform B display sorted records 10-whatever is left.

Any suggestions? Thank you in advance for any anticipated help.

 

[sko]

This is kind of ugly but maybe it will give you a place to start. I'm not a programmer which will be obvious in a moment

I've use three queries
First query for all records, sorted by agency & medic "qrySnakeColumns"

Second query for top 10 records "qryFirst10"
SELECT TOP 10 tblSnakeColumns.Recordnum, tblSnakeColumns.Agency, tblSnakeColumns.Medic
FROM tblSnakeColumns
ORDER BY tblSnakeColumns.Agency, tblSnakeColumns.Medic;

Third query to find the remaining records I looked for unmatched records between the query for top 10 and the query for all records "qryRemainingRecords"

SELECT qrySnakeColumns.Recordnum, qrySnakeColumns.Agency, qrySnakeColumns.Medic
FROM qrySnakeColumns LEFT JOIN qryFirst10 ON qrySnakeColumns.Recordnum = qryFirst10.Recordnum
WHERE (((qryFirst10.Recordnum) Is Null));


I based SubformA on qryFirst10
I based SubformB on qryRemainingRecords and I assumed there would be no more than 20 records total.

 

[UncaKev]

This is an approach I've used elsewhere which works, if a couple of assumptions are met:

1 - There is a maximum number of records you want to display on each side (in your case you said 12, can be anything).

2 - The records either have a sequence number (1 to MaxRecordsPerSide * 2, 24 in your example) in them, or you can put them there. You don't need all 24 records.

If so:

YourQuery1: (This can have selection criteria if needed)

CriteriaField, Seq#, Field1... FieldX, LinkField: Seq# + (MaxLinesToDisplayPerColumn). Selection is CriteriaField = {Whatever}.

YourQuery2:

Left outer join YourQuery1 to YourQuery1 on CriteriaField to CriteriaField and LinkField to Seq#. Display all fields. You can display this in one subform. Selection is Seq# <= 12. The 24 records should display side-by-side. The SQL will look like:

SELECT Query1.CriteriaField, Query1.Seq#, Query1.Field1... FieldX, Query1_1.Seq#, Query1_1.Field1... FieldX
FROM YourQuery1 as Query1 LEFT JOIN YourQuery1 AS Query1_1 ON (Query1.LinkField = Query1_1.Seq# AND Query1.CriteriaField= Query1_1.CriteriaField) where Query1.CriteriaField = {Whatever} and Query1.Seq# <=12 order by Query1.Seq#;

Yell if this isn't clear. It looks harder than it is <g>.

Kevin