Making report flow horizontally

[Scaffold]

Greetings!



I have a simple database where basically exist A group of values and B group of values, and more than one B value can be assigned to a single A value. When I create a report, I see that B values for each A value grow downwards. The question: is it possible to make them grow horizontally, so that it would look like

45 2 1,5 1
48 5 4 3 2 1

rather than like in my screenshot?

[maximus]

Simple write a function that will loop through all the values and the concatenate them and togher seperation each value with a seperator like , ; -.
to do this first determine the total count of the records and make sure you add a loop break so that it breaks when it reaches the end of the record set.

[Scaffold]

I have no clue how to do that

[maximus]

This is what I have done:

I have two tables:
1) Members
2) Marks

The table Marks is Joined to Tables Member One to many Relationship that means one entry in the member table can have multiple entries in the Marks Table.

This Function is what I had written to you about:

I have opened a Record Set, the SQL as suggested that the recordset is ordered by marks. So now this function loops through all the numbers and arrange them horizontally. I have used a loop for this.

Option Compare Database
Dim strSQL As String
Dim Mystring As String
Dim intmemberid As Integer
Function MySet(intmemberid) As String
Mystring = ""
strSQL = "SELECT tblmarks.ID, tblmarks.Member_id, tblmarks.Marks FROM tblmarks Where tblmarks.Member_id =" & intmemberid & " ORDER BY tblmarks.Marks;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.EOF And rs.BOF Then
Exit Function
End If

Do While Not rs.EOF
If Len(Mystring) > 0 Then
Mystring = Mystring & " - " & rs!Marks
Else
Mystring = rs!Marks
End If

rs.MoveNext
Loop

Set rs = Nothing
MySet = Mystring
End Function

refer to the sample attached.

[Scaffold]

Thank you! I will research that. I'm quite new at Access, but what points in slow start?

[maximus]

let me know the result of your research!!!!!!!!!!!!!

[NTC]

what you want is a 'CrossTab' report or query - which is an embedded feature of Access. Look that up in your textbook or Help area.

also, b.t.w. - this is swimming upstream. As you note - a database stores records 'vertically'. So any attempt to display data 'horizontally' is skating on thin ice. If the data quantity is reasonable it does work (i.e. crosstab). But as the data quantity increases the human ability to deal with horizontal structure - and the impracticality of printing begins to become an issue.

[maximus]

Yes I totally agree to you NTC.

[Scaffold]

what you want is a 'CrossTab' report or query - which is an embedded feature of Access. Look that up in your textbook or Help area.

also, b.t.w. - this is swimming upstream. As you note - a database stores records 'vertically'. So any attempt to display data 'horizontally' is skating on thin ice. If the data quantity is reasonable it does work (i.e. crosstab). But as the data quantity increases the human ability to deal with horizontal structure - and the impracticality of printing begins to become an issue.

Maybe. But sometimes horizontal flow is surely more practical. For instance, if I want to compare my electronic database with the source table which is printed in the book with entries located horizontally to each other.

[maximus]

Correct me if I am wrong but I don't see how a Crosstab query can help in this situation
in a Cross tab query I can select my Row and Column Header and the data that is in this column are generally arithmetical calculations like Average sum etc. Here the data that is vertically displayed in the column has to be displayed horizontally.

[Scaffold]

If I want to check if I copied the source paper table into electronic one correctly, it is easier to do if layout of both tables is similar. For me at least.

[maximus]

have you been able to solve you problem? did the solution work?

[Scaffold]

Haven't had time to attend it yet because I've been busy with more urgent needs.

BTW, I have one unsolved issue in another thread, maybe you could have a look? It's there, in my last message: https://www.accessforums.net/access/...port-6729.html.
Thanks in advance!