Merging info in one field

[capsula4]

Hello!

I have a 1-many relationshiop:

TRANSACTION -> PRODUCTS

is there a way to have a field in a query called "ALL PRODUCTS" separated by commas? I need this as text in a report, not as a list (not one below the previous one).

EXAMPLE
[TRANSACTION ID] [CLIENT] [ALL PRODUCTS] (autogenerated)
1 | MARY | garlic, onion, pumpkin
2 | GEORGE | onion, pumpkin

[TRANSACTION ID] [PRODUCT ID] [PRODUCT]
1 | 1 | garlic
1 | 2 | onion
1 | 3 | pumpkin
2 | 2 | onion
2 | 3 | pumpkin

 

[Uncle Gizmo]

I answered a similar question recently and I think you may find something useful in this thread.

 

[capsula4]

Thanks Gizmo!

I tried to use the module you posted but I couldn't really make it work in my database.

I'm not sure what fields should I put in the query...

[TRANSACTION ID] | [PRODUCTS] | fConList([TRANSACTION ID],[PRODUCTS])

But doesn't work.. not sure how to make it work..

 

[Uncle Gizmo]

>>>I answered a similar question recently<<<

I just see that was 2004! Doesn't time fly.

Granted my example does appear complicated, however it's not that complicated to do.

You need to create the text version of a query, (the SQL statement) so that it just returns results for Mary. Hard code the "1" representing Mary in the query.

In other words you need to design a query that returns these results:

1 | 1 | garlic
1 | 2 | onion
1 | 3 | pumpkin

Once you've got this working, then upgrade the query so that you can replace the hard coded "1" with any Variable you so desire from outside of the routine.

Now you should have a query where you can return Mary's or George's results easily.

Once you have this query then you should be able to work out how to place it in the module in the example, and then extract the product description.

Like I said my example isn't very good as an example for showing someone because it is actually passed two variables, the serial number and the batch number. This is done to prevent a unit being incorrectly listed if it is returned for a service at a later date.

 

[capsula4]

Thank you for the help, I have just realised that I was importing just the module and didn't make any modification to it! I just opened it now and realised that I actually have to change the module.

I'm pretty newbie to access, I thought that the module would work for any DB as long as the arguments were okay.

 

[capsula4]

If you can gimme a lil help with this it would be nice, I'm really newbie on ACCESS and really don't know much about SQL coding and almost nothing from modules.

I already could make the query:

[TRANSACTION ID] | [ID_PRODUCT] | [PRODUCT]
1 | 1 | onion
1 | 2 | garlic
2 | 1 | onion
2 | 3 | pumpkin

[TRANSACTION ID] belongs to table "MAIN"
[ID_PRODUCT] and [PRODUCT] belong to table "DETAILS"

Now, how can I make this work if I want to have "products listed by transaction"?

 

[Uncle Gizmo]

Please post a query that produces these results:

[TRANSACTION ID] | [ID_PRODUCT] | [PRODUCT]
1 | 1 | onion
1 | 2 | garlic
2 | 1 | onion
2 | 3 | pumpkin

 

[capsula4]

The sample I posted was representative, here is the exact query I'm using:

SELECT CONTENEDORES.N_OP, CONTENEDORES.ID_CONTENEDOR, CONTENEDORES.CONTENEDOR
FROM CONTENEDORES;

N_OP = OPERATION NUMBER (TRANSACTION ID)
ID_CONTENEDOR = ID CONTAINER (PRODUCT ID)
CONTENEDOR = CONTAINER (PRODUCT)

I connected them using the TRANSACTION ID, not the CONTAINER ID since one transaction may have more than one container (1-many relationship).

If you can gimme a lil help with this it would be wonderful!! And I guess that it might be useful for many people.

 

[Uncle Gizmo]

Sorry my mistake the query needs to return these results:

1 | 1 | onion
1 | 2 | garlic

(where N_OP = 1)

so I reckon your query should look something like this:

SELECT CONTENEDORES.N_OP, CONTENEDORES.ID_CONTENEDOR, CONTENEDORES.CONTENEDOR
FROM CONTENEDORES;
WHERE N_OP = 1

Please could you open your query again and add a "1" in the criteria for the column headed "N_OP" and then post that SQL here.

 

[capsula4]

SELECT CONTENEDORES.N_OP, CONTENEDORES.ID_CONTENEDOR, CONTENEDORES.CONTENEDOR
FROM CONTENEDORES
WHERE (((CONTENEDORES.N_OP)=1));

Pretty similar to what you said, I guess it would have worked!

Anyway, is really necessary to make the criteria thing? Cause I would like to have the list of containers for all of the operations.

 

[Uncle Gizmo]

Add the following code to a module and call it as shown in the "report example"

I don't think it will work if the field "N_OP" is a text field and not an integer, if this is the case let me know and I will show you how to adjust the code.

Public Function fConList(intN_OP As Integer)

Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

strSQL1 = "SELECT N_OP, ID_CONTENEDOR, CONTENEDOR "
strSQL2 = "FROM CONTENEDORES "
strSQL3 = "WHERE (((N_OP)="
'1
strSQL4 = "))"


Dim strSQL As String
Dim strText As String

strSQL = strSQL1 & strSQL2 & strSQL3 & intN_OP & strSQL4


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!CONTENEDOR
                Else
                strText = strText & ", " & RS!CONTENEDOR
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

Let fConList = strText
End Function

 

[Cosmos75]

Here's another way - DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause . (old link). The code is a lot longer but it's meant to be a generic function.

 

[capsula4]

Thanks a lot Gizmo! Gotta say that works pretty good now (N_OP is an integer field in my case). The only lil thing is that entries in the query are being duplicated, if the OP has 3 containers, then I have 3 registries of the same OP. Despite this, it works fine for what I want in a report by just including once the field.

And also thank Cosmos75 for your contribution! So far the code Gizmo gave me is okay, in case I gotta go further, I may take a look into it.

 

[Uncle Gizmo]

Here's another way - Concatenating unique field values for records matching a query's GROUP BY clause. The code is a lot longer but it's meant to be a generic function.

thats a nice piece of Code .... Thanks, will use it I'm sure

 

[capsula4]

Actually when the field I'm trying to list horizontally is empty, it gives an error.

I tried to add an if condition but definitively I'm still too newbie with modules...
I guess the error is in the condition "RS!RECORDER = Null", how should it be so as to recognize RECORDER is empty?

    Do Until RS.EOF
            If RS!RECORDER = Null Then
            Else
                If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                    strText = RS!RECORDER
                    Else
                    strText = strText & ", " & RS!RECORDER
                End If
            End If
        RS.MoveNext

 

[Uncle Gizmo]

What is the error message, error number And which line of code does it highlight?

 

[capsula4]

Using the NULL if it gives this error:

"Error 94: not valide use of Null" (im translating froms spanish)

Going back to the first sample in which I gave you the SQL query:

It highlights: "strText = RS!CONTENEDOR"

....

To my surprise, I also tried taking out the If condition with the NULL thing and gives exactly the same error!

 

[capsula4]

This is even more weird! I just found out that the error was given if the FIRST field was empty, so I changed:

strText = RS!CONTENEDOR

with this

strText = "" & RS!CONTENEDOR

Now it works!!!

I'm gonna try to make this module with 3 arguments, TABLE, CONCATENATED FIELD and GROUP BY, actually the only argument might the GROUP BY. If I can't make it I will ask for some help I guess.

I know that someone already posted a module like what I'm trying to do but I couldn't really make it work.

I opened a topic about this module at Modules/VBA forum category:
http://www.access-programmers.co.uk/forums/showthread.php?p=666122

 

[Uncle Gizmo]

Sorry, you have lost me, I do not know if you still have a problem or not. Please could you clarify the situation.

 

[capsula4]

Sorry, you have lost me, I do not know if you still have a problem or not. Please could you clarify the situation.

I had a problem but since it was module stuff I decided to open a new topic and someone already answered me. So I don't have any problem!

I modified a bit the module so as to make it generic, enter here in case you wanna see it (it's finished):

http://www.access-programmers.co.uk/forums/showthread.php?p=666122

Thank you for all your help Gizmo!! You are really patient!!