How to combine many records into just one record 2

[ruthcali]

i am using Access 97 in a Novell environment.

i am trying to produce a report using two tables.

Table1: Main Table with a field called ID
Table2: Work Ticket Table with a field called ID.

The IDs have a one-to-many relationship.

So, in Main table, i have a ID number (example: 1) and in the Work Ticket table, i might have two or three entries relating to ID 1:

for example,
ID:1
Type of ticket: Iron

ID: 1
Type of ticket: Power

ID:1
Type of ticket: Fiber

In my report, i want just one line for ID 1. i would like a text box that displays all the types of tickets for each ID.
So, in my report, i would like just one line for ID# 1

ID:1
Type of tickets created: Iron, Power, Fiber

Any ideas? i keep getting 3 lines in my report, one for:
ID:1
Type: Iron

ID:1
Type: Power

ID:1
Type: Fiber

But, i want just one line in my report.
ID:1
Type: Iron,Fiber,Power

Hope i wasn't too confusing. please help!

thanks in advance.

 

[jgarnick]

I posted this same question and got a great reply from someone! He sent me an example database via email which I will be happy to forward to you if you would like--

Send me an email--

 

[aug4570]

jgarnick, could you also email that sample database to me at aug4570@aol.com. thanks!!!

 

[AussieTony]

Dear JGarnick,

is that possible to e-mail the sample database to me as well to tonykanchi@aol.com

Thanks in advance,
Antony.

 

[Vandys]

Hi JGarnick,

I would appreciate if you can forward the sample database to me also as I am also facing the same situation and was about to post it in the forum for some help. I should have asked GOD for something else also today!!!

My E-mail ID is vandanasanjay@hotmail.com

Thanks in advance!!!!

Vandys

 

[MikeKohler]

Hi, I was also wondering if someone could send me a copy of the sample database. I am looking for a similiar solution.
Thank you, Michael Kohler
mkohler@telusplanet.net

 

[Guest_imported]

Could you please send one to me too at:
ruizcor@auburn.edu

Thank you very much.

 

[ruthcali]

I sent both of you a copy.

 

[JPeters]

Could you possilby post the thread that you asked the original question in so that I can go look over it? This sounds like some good stuff. Thanks.

-Josh -Happen609
Wasting more of your valuable time...

 

[ruthcali]

Happen,
the original question is at the top of the thread.

 

[snayjay]

I don't know what the mystery example database says since nobody will copy paste the code from it. But I do the same thing in one of my reports. Just put the fields in your report....make the fields as small as you wish. (I'm speaking of the 3 lines) Make the fields property for visible to equal false. fieldname.visible = false....do that in the properties tab. Then make an unbound field and in the code section I do this. In this instance I have field1, field2, field3 and the unboundfield. The 3 numbered fields are set to fieldname.visible = false in their properties tab. The unbound field value is blank and here is the code behind the detail on format.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

unboundfieldname = field1 & field2 & field3

end sub

for my report I have this. I have a persons name, their company, and their office name. So my code is like this

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

mystr = name & " from " & company & " working in " officename

end sub

so if my fields are Mike Smith, WalMart, Layaway....the unbound field shows...
Mike Smith from Walmart working in Layaway.

It's really simple...at least it works great for me. Somebody post this mysterious database example's code please....so I can compare and see if their is an easier way.

 

[BoxHead]

Here's another two cents...

I've been using a function I call ConcatRow to string together different fields of a record. It allows me to specify the fields, the separator and the table as well as any criteria (if I want) in the same way as a DLookup function.

I put ConcatRow into a module and call it in queries or forms throughout the database.

EG: the following in a query returns a string made up of the record's Property ID, Property Name, and Property Street divided by semicolons.

  'Expr1: ConcatRow("[prpid],[prpname],[prpstreet]","; ","tblProperties","[prpid] = " & [prpid])

Public Function ConcatRow(MyFld As String, MyBreak As String, _
TblQryName As String, Optional strCriteria As String) _
As String

Dim MyString As String, strSQL As String, intF As Integer
Dim db As Database, rst As Recordset, FString As String
Dim FirstFld As String
If InStr(MyFld, &quot;,&quot <> 0 Then
FirstFld = Left(MyFld, InStr(MyFld, &quot;,&quot - 1)
Else
FirstFld = MyFld
End If

If strCriteria = &quot;&quot; Then
strSQL = &quot; SELECT &quot; & MyFld & &quot; FROM &quot; & TblQryName _
& &quot; WHERE &quot; & TblQryName & &quot;.&quot; & FirstFld & &quot; = &quot; & FirstFld & &quot;;&quot;
Else
strSQL = &quot; SELECT &quot; & MyFld & &quot; FROM &quot; & TblQryName & &quot; WHERE &quot; & strCriteria & &quot;;&quot;
End If
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

For intF = 0 To rst.Fields.Count - 1
MyString = MyString & rst.Fields(intF) & MyBreak
Next intF

ConcatRow = Left(MyString, Len(MyString) - Len(MyBreak))

End Function

John

Use what you have,
Learn what you can,
Create what you need.

 

[zahara666]

Do you think I could get a copy of that sample database? Been scrounging around with a simalar problem.

Thanks,
Julie-jpaperwala@hotmail.com

 

[zahara666]

the email address is just: jpaperwala@hotmail.com
thanks..

 

[ruthcali]

The sample database that i was sent back in Aug 2000 is described below:

There are 2 tables:

The first table is named Table1 and has 2 fields:
field name1 is: ID which is an autonumber field and the primary key.
field name2 is: Description and is a text field.

The second table is named Table2 and has 2 fields:
field name1 is: IDmatch and is a number field.
field name2 is: Date is and a text field.

The two tables have a one to many relationship based on ID and IDmatch.

Enter some sample data:
in table1:
Id Description
1 Joe Some
2 Bill Jones
3 Davy Crockett

In table2:
IdMatch Date
1 1990
1 1997
1 1999
2 1975
2 1921
2 1977
2 2001
3 2101
3 1998

There is a form with 3 objects on it:

-there is a combo box called combo0 with a row source of: SELECT Description, Id FROM Table1. The column count is 2 and the column widths are: 1.5&quot;;0&quot;.

--there is a text box called text4.

--there is a list box called list2. it has no row source.

The code is triggered by the combo0 change event:
Private Sub Combo0_Change()
Dim dbs As Database, rst As Recordset, temp_var As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;SELECT * FROM Table2 WHERE IdMatch = &quot; & [Combo0])

'This line sets the rowsource of the listbox to a SQL statement which pulls the matching dates
List2.RowSource = &quot;SELECT Date FROM Table2 WHERE IdMatch = &quot; & [Combo0]

'This fills the text box with matching dates seperated by commas
While Not rst.EOF
If Len(temp_var) < 1 Then
temp_var = [rst]![Date]
Else
temp_var = temp_var & &quot;, &quot; & [rst]![Date]
End If
rst.MoveNext
Wend
[Text4] = temp_var
End Sub

The code doesn't just take a few field and combine them into one field. It takes the 'Many' table (in a 'one to many' relationship) and combines all the 'Manys' into one field separated by commas.