Need to show multiple query results in one field

[kerrycharnley]

Can anyone tell me how to do the following,

I have an access database that I need to create queries in.
I am getting duplicate results when running a query because some of the Companies I have listed, have several ID's selected.

Is there anyway I can get the query to show the company once, and the ID's in the same field separated by commas or similar?

Any help would be much appreciated

 

[DrSimon]

I'm not clear why you want to get the ID's delimited in the same field. What you need to do is to remove the replicates. The easiest way to find these is to use the 'Find Duplicates Query Wizard'. This will list the replicates and you can then choose to delete them.

Now of course if you have other tables which use those company ID's you must tidy them up before you doing any deletion in the Companies table!

Sorry to sound patronising, but anyone who designs a database should understand the concept of Normalisation. Yes, it's a little bit theoretical but once understood it will pay dividends for evermore!

Simon Rouse

 

[kerrycharnley]

Perhaps, I didn't explain clearly.

I am trying to sort out this problem for another user.

They sent me an example to explain what they want...can I email it to you? Perhaps you will see what I mean

Thanks

 

[Isadore]

Kerry: I have a routine which concatenates several results as it moves from one table (queried table) to a new table where it list the item once and then concatenates all the attendant duplicated parameters. This might help, let me know.

 

[kerrycharnley]

That would be great.

My email is kerry.l.charnley@defra.gsi.gov.uk

Thanks a lot

 

[DrSimon]

I'm delighted that Isadore is willing to help you help your user but my question and comments still stand!

Giving a user what they want is not the same as providing what they need! Perhaps I'm just an old cynic, but I'm happy to help if necessary.

Simon Rouse

 

[Isadore]

DrSimon. I agree. If at all possible these duplicates should be removed, however, I just assumed that because there is so much "not known" about the background information, that this was an inevitible result.

Dr S. is right w.r.t. "tidying up". If that is all that is required (even if its a days work) then you will avert problems down the road which could become substantial and wipe out a lot of your effort. I would only proceed to concatenate in one field if that is the "only" solution to your problem.

Some time ago, on this forum, I posted a similar questions, having one ID field, but with many people attached to it, and so I wanted 2 fields, one with ID, and the other with concatenated names:

ID Name
2 Barry Johnson, Tom Smith, George Litte, Susy Cue
4 Virginia Thomas, Louis Lemire
6 Jeff Brown, Serio Corodova, Andy Ducharme

I submitted my solution, and another programmer submitted a different solution, which he claimed was "better, faster" and perhaps it was true, never changed my code after that (time) but my routine worked as well.

What I do is query out all the distinct ID's. I placed them in a table with an ID field and an Empty Name field. Then I loop through another table and grab all the names for that particular ID and concatenate them in the empty field.

...along those lines. Before proceeding howevermake sure this is what you need. Also, you may want to search out this argument from several months ago. One of the keywords used was "sideways" (name1, name2, name3, etc) and possibly concatenation. If you could find that, then that has all the info I would post here. Post back and we'll follow this through.

 

[DrSimon]

I'm not sure that I agree with concatenated names Isadore, but I promise not to tell Edgar Codd. I'd structure that table

ID N Name
2 1 Barry Johnson
2 2 Tom Smith
2 3 George Litte
2 4 Susy Cue
4 1 Virginia Thomas
4 2 Louis Lemire
6 1 Jeff Brown
6 2 Serio Corodova
6 3 Andy Ducharme

So ID,N is the primary key but ID remains as the link you seem to need. I admit that creating a delimited string using SQL isn't easy if you need that.

How do you cope if there were too many names for the field or the names themselves were too long?

Simon Rouse

 

[Isadore]

DrS. The concatenation was done to fill up a Names field on a final report. For example, the ID was a site ID where several people took samples, so, in the report:

Site ID Collectors

02002004 Tom Smith, Jim Brown,
Susan Small

etc etc. So, the only reason we used the concatenation of field elements in a sideways fashion was to present the data in an appealing way in a Report.

Our use has nothing to do with the overall structure of the tables, which are, for the most part, one to many.

 

[kerrycharnley]

Isodore,
I'd be grateful if you could point me in the right direction of where I can find the instructions on how to do this.
This sites keyword search is down and I am unable to find your previous thread.

Many thanks
Kerry

 

[Isadore]

No problem Kerry.

Keep in mind that part of the code you won't see are the DoCmd.OpenQuery statements. Here I create the following two tables:

Table1: Unique ID field, Empty Memo field
Note this is a Memo field in case the
concatenation becomes > 255 (text max)

Table 2: This table will have all the ID's and their
associated values (say Names e.g.); so, before
beginning the routine develop the queries you
need to create:

Table1: ID Names (plural)
1 Null
2 "
3 "
4 ...and so on

Table2: ID Name (single)
1 Tom Smith
1 Linda Brown
1 Jerry Turner
2 George Little
3 Barry Summers
3 Genie Hartt
4 Mark Gicalone
4 Thomas Smith
5 ...and so on

Kerry; keep in mind that the one solution (I'll eventually dig it up in my references and forward to your email)offered by a programmer on Tek-Tips may be worth substituting here). It looked pretty good - stored the variables in a virtual table and then brought in the results. At any rate, you can compare later - the routine below, as I said, works just fine.

If you have any problems creating Table1 and Table2 let me know.

The code:

DoCmd.SetWarnings False 'turn off warnings
'create Table1
'create Table2
....
'now run horizontal population routine...
Dim dbs As Database
Dim rst, rst1 As Recordset
Dim temp_var, MyCriteria, MyBookmark As String
Dim Ct, i As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Table2&quot
Ct = 0
rst.MoveFirst
Do
MyCriteria = rst("ID&quot
While Not rst.EOF
If rst("ID&quot = MyCriteria Then
MyBookmark = rst.Bookmark
If Len(temp_var) < 1 Then
temp_var = rst(&quot;Name&quot
Else
temp_var = temp_var & &quot;, &quot; & rst(&quot;Name&quot
End If
End If
rst.MoveNext
Wend
Ct = Ct + 1
Set rst1 = dbs.OpenRecordset(&quot;Table1&quot
rst1.MoveFirst
If (Ct) > 1 Then
For i = 1 To Ct - 1
rst1.MoveNext
Next i
End If
rst1.Edit
rst1(&quot;Names&quot = temp_var
rst1.Update
rst1.Close
temp_var = &quot;&quot;
rst.Bookmark = MyBookmark
rst.MoveNext
Loop Until rst.EOF
dbs.Close
End If

...if you have any problems post back.