Joining Rows in a Form from a query

[lbaotf]

I am trying to join records in a single form or query based on a value from a field in a record. ie.



I have records that have a unique value field (Rating) 1-1000 the last number can vary depending on how many records are entered and will be a different amount of records everytime I try generating the form or query.

I need to be able to take the first record and the last record and combine selected fields, then the second record and the next to last record, then the third record and the third record from the last record.........and so on until the records meet in the middle.

Anyone have any Ideas?

Thank You

[June7]

And what happens if there is an odd number of records?

Seems like I've seen this before. I don't know if any combination of queries alone could accomplish this. I think VBA and a temp table will be needed.

[lbaotf]

There will never be an odd number and if there was would just need an error message to let user know they needed to add another record...

[June7]

Do you know anything about VBA and opening recordsets and writing records to table? Because that's what I think will be needed.

[lbaotf]

NO Not Sure but I will study that just a little bit It seems to be a simple solution but maybe it is more diff. than I think.

I am Basicly Sort Asending 1 Column and then if I have 10 records, selecting first then Last together, then second and nest to last and so on together.

If you could give me a little example it might help I would appreciate it.

Thank you so much

[June7]

Here is a simple procedure.

Option Compare Database
Option Explicit

Sub MatchRecs()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim i As Integer
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY RecNum ASC;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY RecNum DESC;", dbOpenSnapshot)
CurrentDb.Execute "DELETE FROM TempTable"
If rs1.RecordCount > 0 Then
    If rs1.RecordCount Mod 2 <> 0 Then
        MsgBox "Odd number of records. Procedure canceled."
    Else
        For i = 1 To rs1.RecordCount / 2
            CurrentDb.Execute "INSERT INTO TempTable(RecNum1, RecNum2, Data1, Data2) VALUES(" & _
            rs1!RecNum & ", " & rs2!RecNum & ", '" & rs1!Data & "', '" & rs2!Data & "')"
            rs1.MoveNext
            rs2.MoveNext
        Next
    End If
End If
End Sub

I created a table called Table1 with two fields - RecNum and Data. I created another table called TempTable to hold the manipulated records with 4 fields - RecNum1, RecNum2, Data1, Data2. I put the code in a general module and manually ran it with the Run command button on the VBA editor menu. You could put everything between Sub MatchRecs() and End Sub in a button Click event behind a form.

Change names in the code as appropriate.

[lbaotf]

OK I understand a little of that....I am going to give it a try now...
Thank you I will keep you posted.... You are great

[lbaotf]

Can the table contain an autonumber?

I created a table called Table1 with two fields - RecNum and Data. I created another table called TempTable to hold the manipulated records with 4 fields - RecNum1, RecNum2, Data1, Data2. I put the code in a general module and manually ran it with the Run command button on the VBA editor menu. You could put everything between Sub MatchRecs() and End Sub in a button Click event behind a form.

Change names in the code as appropriate.

[June7]

Which table? Table1? Don't see why not.

[lbaotf]

I Have put the code in the general module but I cant get it to run with a command. maybe im not commanding it properly.

[June7]

Click cursor anywhere within the procedure then click the Run button (green triangle) from VBA editor or F5 or click Run menu item.

Review link at bottom of my post for debugging guidelines.

[lbaotf]

HI June7
Thank you so much for your assistance. I have the test working I am moving forward to implement it into my current Data...

I want to thank you for your time.

Can this only be done at the table level? or can it be accomplished with queries or forms?

[June7]

That was the driving force behind the VBA and temp table solution. Don't think queries can do this. Really don't know what you mean by accomplishing with form.

[lbaotf]

Well what I have is a Database built with a table called Riders, with fields listed RidersID, FirstName, LastName, Rank, Rating, HorseName, Class,

I have generated forms from that table to enter Data in each field, I have Queries, and Macros pulling data by Class, Rating, Horsename, Combining FirstName and LastName together. Then I am using a random expr to randomly Sort the records into queries that provides the exact data needed by the RidersID based on the Class and Rating.

The Reason for the combine of records in the order with Last Record and First Record and so on, is that after these records have been randomly sorted is when I need to combine the records from last and first and so on. Ultimatley two of the Riders need to be puuled together into one record after all the sorting and then printed on a report.

Hope I explained it well enough...

Again thank You so much for the progress I am making...

[June7]

You can have a 'menu' form with button: "Match Riders and Print Report"