Concatenating the same field? 2

[Bennie47250]

Using Access 2003.

Working on a project to submit Level 3 Credit Card information in a batch mode. The Credit Card processor file requirements require us to submit one text string for each order number.

The first part of the text string file contains the Billing Information (credit card number, expiration date, street address, ….) The 2nd part of the text string will include the Order Details of what was on the order, (part number, description, quantity, unit of measure)

So the file we submit will look something like

4444555566667777,1010,845 North Main St.|FJ696,Adapter,5,ea|

This example they only ordered one item. If they order multiple items, the text string is to be formatted like this:

4444555566667777,1010,845 North Main St.|FJ123,Hose,5,ea||FJ456,Hose,3,ea||FJ789,Hose,4,ea|

Put another way the string is like this:
Billing Information | order details || order details || order details || order details|

Here is what I need assistance with, I’m collecting the order detail information and writing it to a table that looks like this:

Order # Part# Description Qty UnitofMeasure
123 FJ123 Hose 5 ea
123 FJ456 Hose 2 ea
123 FJ789 Hose 3 ea
567 FJ123 Hose 5 ea
963 FJ123 Hose 5 ea
963 FJ456 Hose 5 ea
741 FJ123 Hose 5 ea

By order number, I need to concatenate the order details. So after the concatenation, the above would look like this:
Order # Order Details
123 |FJ123,Hose,5,ea||FJ456,Hose,2,ea||FJ789,Hose,3,ea|
567 |FJ123,Hose5,ea|
963 |FJ123,Hose,5,ea||FJ456,Hose,5,ea|
741 |FJ123,Hose,5,ea|

As I see it right now, I will have 2 tables, one table that contains the Billing Information and the other table will contain the Order Details and I will join them using the order number.

Thanks for your suggestions!

 

[lespaul]

check out FAQ701-4233

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bennie47250]

PHV,

I have little to no experience with VB code so I'm having problems getting the code in the FAQ to work for me. I don't even know if I'm using ADO or DAO as I'm not familiar with these terms.

Can you give me some further instructions?

 

[PHV]

If the tables are JetSQL (ie native access tables vs linked tables) then use DAO.
When in VBE (Ctrl+G) menu Tools -> References ...
tick the Microsoft DAO 3.# Object Library.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bennie47250]

Building sample tables and using the code provided in the FAQ, I have been able to concatenate the first name fields into one field. How would the code be modified to concatenate additional fields?

As I have shown in my original post, I need to concatenate 5 fields.

Thanks

 

[lespaul]

probably something like:

CONCATENATE("SELECT Part# + ' ' + Description + ' ' + Qty + ' ' + UnitofMeasure As StringToConcatenate .....

 

[Bennie47250]

That did it, Thanks!