Joining Rows in a Form from a query

[lbaotf]

Thanks for all your assistance I am still having trouble getting the Module to run. I can run it fine in the VB command but when I try adding it a a command button on my form it will not run... Any Ideas?

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

[lbaotf]

I think I understand but not sure how to accomplish the Menu form and button. I have tried several differetn combinations but my button will not command the run.

[June7]

In the button Click event property, select [Event Procedure]. Click the ellipsis (...) to open the VBA editor at that procedure. Type or copy/paste code within the Click event procedure. This code can be the full procedure or can call the procedure in the general module.

[lbaotf]

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

High June I have ran into a small issue with the counting of records and deviding them mathing the last with the first and so on... When the records get to the Middle two it is duplicating only two records in the Temp table and misses two records.... Really wierd thing.

[lbaotf]

RidersID	Rating	Full_Name	Rank	HorseName	Class
246	1	Jamilyn Weigand	AM	Fabie	Jr Horse
254	1	Deanna Armstrong	AM	Color Me Paint	Jr Horse
241	1	Jordan Lesh	PRO	After shocks lady lena	Jr Horse
243	1	Bill Womack	AM	Carry Lynx	Jr Horse
255	2	Shelley Fitzgerald	AM	Badges Smart Chica	Jr Horse
253	2	Colleen Boyer	AM	Beckys Pepto	Jr Horse
249	2	JT Powell	AM	Indian Oak	Jr Horse
256	2	Colleen Bowman	AM	Back For Cash	Jr Horse
238	2	Pete Maragos	AM	2 Socks	Jr Horse
244	2	Kelly Downs	AM	Dakota	Jr Horse
240	3	Brian McClain	NP	Haven	Jr Horse
248	3	Jessica Burk	NP	Haidas Classie Chick	Jr Horse
251	3	Dan Weigand	NP	Kates Smokin Gun	Jr Horse
252	4	Elaine Jackson	NP	Little Girl	Jr Horse
239	4	Rick Gaston	PRO	A Happenin Bet	Jr Horse
247	4	Brian Kruger	NP	Genuine Skyline	Jr Horse

After I run the Module for I get this returned It duplicates the two middle records..

RecNum1	RecNum2	RecNum3	RecNum4	RecNum5	Data1	Data2	Data3	Data4	Data5
246	247	Jamilyn Weigand	Brian Kruger	1	4	AM	NP	Fabie	Genuine Skyline
254	239	Deanna Armstrong	Rick Gaston	1	4	AM	PRO	Color Me Paint	A Happenin Bet
241	252	Jordan Lesh	Elaine Jackson	1	4	PRO	NP	After shocks lady lena	Little Girl
243	240	Bill Womack	Brian McClain	1	3	AM	NP	Carry Lynx	Haven
255	248	Shelley Fitzgerald	Jessica Burk	2	3	AM	NP	Badges Smart Chica	Haidas Classie Chick
253	251	Colleen Boyer	Dan Weigand	2	3	AM	NP	Beckys Pepto	Kates Smokin Gun
249	249	JT Powell	JT Powell	2	2	AM	AM	Indian Oak	Indian Oak
256	256	Colleen Bowman	Colleen Bowman	2	2	AM	AM	Back For Cash	Back For Cash

[June7]

I cannot replicate the issue.
Post the actual code you are using or even provide the db. Follow instructions at bottom of my post.

[lbaotf]

Here is the Code I am using....

Here is the actual Module Code I am working with...

Sub MatchRecs()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim i As Integer
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY Rating ASC;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY Rating 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, RecNum3, RecNum4, RecNum5, Data1, Data2, Data3, Data4, Data5) VALUES(" & _
rs1!Rating & ", " & rs2!Rating & ", '" & rs1!Full_Name & "', '" & rs2!Full_Name & "', '" & rs1!Rank & "', '" & rs2!Rank & "', '" & rs1!RidersID & "', '" & rs2!RidersID & "', '" & rs1!HorseName & "', '" & rs2!HorseName & "')"
rs1.MoveNext
rs2.MoveNext
Next
End If
End If
End Sub

[June7]

I can't see anything wrong with the code. I still can't replicate the issue with the data sample you posted even using your expanded INSERT sql.

[lbaotf]

It seems that everything works ok until i have all 2 in the rating column then it duplicated some of the records.... and I must run the module with the rating because it is critical could it be because I have the rating beinf ASC and DESC causing the issue.

[June7]

I did not notice that. Yes, that would be an issue. My suggested code orders by unique identifier.

Try:

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY Rating, ID;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM Table1 ORDER BY Rating DESC, ID DESC;", dbOpenSnapshot)

[lbaotf]

If I change these to lines of the Module it does not duplicate the records....
Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY RidersID ASC;", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM DrawJrHorseQuery ORDER BY RidersID DESC;", dbOpenSnapshot)
It appears that when it is counting and matching the records using the rating thats when it gets to the middle records and it skips and is random and then pulls duplicates.. it seems to be something with the duplicate numbers in the rating field.......

[June7]

This method requires use of a unique record identifier so cannot use any field alone that has duplicate identifiers.

If RidersID is a unique record identifier, it should work.