Adding Text Field...

[MSherfey]

I am having trouble adding different values in the same text field together. Think of it like doing a SUM on a field in a table. However, with XT it obviously doesn't work.

I read how to do this if they were in different fields, but can't figure out how to do this if the values are in the same field.

I have a list of customers and the products they own. I want to 'reduce' that to one line per customer and a single field which has each of the products they own separated by a ' + '. It would look something like this:

Customer1 ProductA
Customer1 ProductB
Customer2 ProductB
Customer2 ProductC
Customer3 ProductB

Result:

Customer1 ProductA + ProductB
Customer2 ProductB + ProductC
Customer3 ProductB

This is really bugging me so I know it has to be simple. I find that I do a lot of things in Access the hard way until someone shows me the right way.

Any help would be great! Thanks!

 

[georgedwilkinson]

What you describe is called concatenation. Try this:
http://www.allenbrowne.com/func-concat.html

 

[MSherfey]

georgedwilkinson,

I'm sorry to sound so stupid with this but I'm a bit confused in the instructions.

I created the query:

SELECT [Breakdown Union xFY].ENDUSER_CUST_NUM, ConcatRelated("[FY 2007 Covered]","[Breakdown Union xFY]") AS Products
FROM [Breakdown Union xFY];

'Breakdown Union xFY' is a Union query which pulls a bunch of different tables together. There are about 10 different queries which look for a specific product for each customer. If they don't have the product, it won't return anything for them. If they do, then it returns the product name in the field.

'FY 2007 Covered' contains all the products I want to concatenate. Accoring to the instructions it looks like I'm doing this okay. However, I am getting the error 'Undefined function 'ConcatRelated' in expression.

I went into the VisualBasic module in Access 2007 and the module ConcateRelated is listed with the code from the link you sent. Not sure what to do here since the help file just says I've done something wrong.

Thank you already for all your help. If you have an idea of what I've done wrong, can you let me know?

 

[smig]

I think you will need to use some code here.
I'll give you here the idea, not the real code.

what you need are loops in loops code.
for each Customer you will have to go for all it's products and add them to the string. something like:

for i = 1 to NumberOfCustomers
  StrText(i) = CustomerName
  for f = 1 to NumberOfProductsForCustomer
    StrText = StrText & ProductName
  Next f
next i

 

[MSherfey]

I think I figured out the issue with the 'undefined function' error. I thought I had compiled it, but apparently I had not.

Now that it is executing without any issues, the problem is the return results. It now seems to be adding the first 6 records together regardless of which customer id they belong to. The results for the first 6 records are then being placed in every customer id.

Here's the union query results

Customer Product
1 Apple
1 Pear
2 Banana
3 Null
4 Apple
4 Banana

What should be returned is

Customer Product
1 Apple, Pear
2 Banana
3 Null
4 Apple, Banana

What is being returned is
Customer Product
1 Apple, Pear, Banana, Null, Apple, Banana
2 Apple, Pear, Banana, Null, Apple, Banana
3 Apple, Pear, Banana, Null, Apple, Banana
4 Apple, Pear, Banana, Null, Apple, Banana


Any ideas?

 

[smig]

did you try what I suggested ?

 

[MSherfey]

Smig - I'm sorry, but I haven't yet. I hate to admit it, but I'm not sure how.

I understand the loops and how they function. And the logic make sense. I'm just not sure how to get Access to count the number of customer ids for each one and set the loops accordingly.

I have a bit more time now so I'm going to dig into it a bit deeper. Thanks for the idea and direction.

 

[smig]

dim Recordset_Custemers as DAO.recordset
dim Recordset_CustemerProducts as DAO.recordset
dim QRY_Custemers as string
dim QRY_CustemerProducts as string
dim NumberOfCustomers as long
dim NumberOfCustomerProducts as long
dim StrText()
dim i as long
 
QRY_Custemers = "SELECT [Customers_Table].[Customer] " &_
  "FROM [Customers_Table] " &_
  "GROUP BY [Customers_Table].[Customer]"
 
 
Set db = CurrentDb()

Set Recordset_Custemers = db.OpenRecordset(QRY_Custemers)
        
NumberOfCustomers = Recordset_Custemers.RecordCount
        
Recordset_Custemers.Close
Set Recordset_Custemers = Nothing

redim StrText(NumberOfCustomers)
 
Set Recordset_Custemers_Table = db.OpenRecordset("Customers_Table")      
 
Recordset_Custemers_Table.movefirst
 
for i = 1 to NumberOfCustomers
  StrText(i) = Recordset_Custemers_Table.fields("Customer")
  StrText(i) = StrText(i) & ","
 
  QRY_CustemerProducts = "SELECT [Customers_Table].[Customer] " &_
    "FROM [Customers_Table] " &_
    "WHERE [Customers_Table].[Customer] = " & Recordset_Custemers_Table.fields("Customer")

    Set Recordset_CustemerProducts = db.OpenRecordset(QRY_CustemerProducts)
 
   NumberOfCustomerProducts = Recordset_CustemerProducts.RecordCount

  for f = 1 to NumberOfCustomerProducts
    StrText(i) = StrText(i) & Recordset_CustemerProducts.fields("Product")
    StrText(i) = StrText(i) & ","
    Recordset_CustemerProducts.close
    Set Recordset_CustemerProducts = Nothing
  Next f
next i
 
Recordset_Custemers_Table.close
Set Recordset_Custemers_Table = Nothing

the array StrText(i) will hold your data

 

[MSherfey]

Is this a query in itself or a function I need to define?

 

[smig]

it's a full code.
in this code your table name is Customers_Table
and the fields names are Customer, Product

if you use different names just change the code.

all the QRY_ are query defs
all the Recordsets are recordsets

 

[MSherfey]

This is pretty cool. I didn't know you could do the code directly like this. I assumed they all had to start with select statements.

I'll give this a shot ASAP. Thanks!

 

[smig]

the Query defs do start with SELECT statemments

 

[MSherfey]

SMIG - sorry to sound so stupid here, but where does this get placed? When I put this in the SQL window and try to save (before I start editing) it comes back with an error stating it is an invalid SQL statement and is needing either a DELETE, INSERT, PROCEDURE, SELECT or UPDATE.

 

[smig]

this is a code, not an SQL statement
it can go either into a Sub or a Function in your module

this code go over your table and take the data from it and put it into the StrText() array.
you can take this array and put it into another table, or put it directly into a NewTable


create a new table with the fields (Custemer, NewProduct) and name it NewTable
make sure the NewProduct field is big enough to hold long strings

your full code will look like:

Sub PutAllCustemersProductsTogether()
 
dim Recordset_Custemers as DAO.recordset
dim Recordset_CustemerProducts as DAO.recordset
dim Recordset_NewTable as DAO.recordset
dim QRY_Custemers as string
dim QRY_CustemerProducts as string
dim NumberOfCustomers as long
dim NumberOfCustomerProducts as long
dim AllCustemerProducts as string
dim i as long
 
QRY_Custemers = "SELECT [Customers_Table].[Customer] " &_
  "FROM [Customers_Table] " &_
  "GROUP BY [Customers_Table].[Customer]"
 
 
Set db = CurrentDb()
 
Set Recordset_Custemers = db.OpenRecordset(QRY_Custemers)
 
NumberOfCustomers = Recordset_Custemers.RecordCount
 
Recordset_Custemers.Close
Set Recordset_Custemers = Nothing
 
Set Recordset_Custemers_Table = db.OpenRecordset("Customers_Table")      
 
Set Recordset_NewTable = db.OpenRecordset("NewTable")      
 
Recordset_Custemers_Table.movefirst
Recordset_NewTable.movefirst
 
for i = 1 to NumberOfCustomers
  Recordset_NewTable.AddNew     ' --- add new record to the NewTable
  Recordset_NewTable.fields("Customer") = Recordset_Custemers_Table.fields("Customer")   ' --- Copy Customer from old table to new
 
  QRY_CustemerProducts = "SELECT [Customers_Table].[Customer] " &_
    "FROM [Customers_Table] " &_
    "WHERE [Customers_Table].[Customer] = " & Recordset_Custemers_Table.fields("Customer")
 
    Set Recordset_CustemerProducts = db.OpenRecordset(QRY_CustemerProducts)
 
   NumberOfCustomerProducts = Recordset_CustemerProducts.RecordCount
 
   AllCustemerProducts = ""
  for f = 1 to NumberOfCustomerProducts
    AllCustemerProducts = AllCustemerProducts & Recordset_CustemerProducts.fields("Product")
    AllCustemerProducts = AllCustemerProducts & ","
 
    Recordset_CustemerProducts.close
    Set Recordset_CustemerProducts = Nothing
  Next f
 
  Recordset_NewTable.fields("NewProduct") = AllCustemerProducts     ' --- put all the custemer products as one chained product in the new table
  Recordset_NewTable.Update        ' --- Update the data in the new table
next i
 
Recordset_NewTable.Close
Set Recordset_NewTable = Nothing
 
Recordset_Custemers_Table.Close
Set Recordset_Custemers_Table = Nothing
 
end sub

and I don't think you'r stupid. you just don't know
I think a stupid is someone who will not say he don't know