INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How do I concatenate fields with a query or code?

How do I concatenate fields with a query or code?

(OP)
I have a table created from an Excel spreadsheet and the DoCmd.TransferSpreadsheet method.

The spreadsheet contains order information from a sales order which can have several items
with the same line number and I am attempting to use a query to take the contents of this table
and combine the options into one field. Not sure if this is even possible but here is what I
have in mind...

The table containing the order information and the options might appear like this:
Order_tbl:

LineNo	PartNo	PartDesc		Qty	
1	12341	Dell OptiPlex PC	1
1	12211	Network Card		1
1	13121	Video Card		1
1	31131	Decryption		1
2	12341	Dell OptiPlex PC	1
2	12211	Network Card		1
2	31131	Decryption		1
3	12341	Dell OptiPlex PC	1
3	31131	Decryption		1
 
Where I would like to convert this into the following format to show the PC as the PartNo & PartDesc
and then the option part numbers in the Options field:

LineNo	PartNo	PartDesc		Options:
1	12341	Dell OptiPlex PC	12211 - 13121 - 31131
2	12341	Dell OptiPlex PC	12211 - 31131
3	12341	Dell OptiPlex PC	31131 
Is there a way to concatenate these fields with a Query or is it easier to do this with code?

Thanks

RE: How do I concatenate fields with a query or code?


You may find some useful information here: FAQ701-4233: How to concatenate multiple child records into a single value

But just to get this:

LineNo	PartNo	PartDesc
1	12341	Dell OptiPlex PC
2	12341	Dell OptiPlex PC
3	12341	Dell OptiPlex PC
 
Your Select statement would look like either:

Select LineNo, PartNo, PartDesc
From Order_tbl
Where LineNo = 12341

or

Select LineNo, PartNo, PartDesc
From Order_tbl
Where PartDesc = 'Dell OptiPlex PC'


Would you know to go after this LineNo or PartDesc ???

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: How do I concatenate fields with a query or code?

You seem to have a crazy mixed up Sales Order format.

The unstated assumption must be that the first occurrence of a new LineNo implies that the PartNo is the real PartNo and every PartNo after the real PartNo is an Option associated with the real PartNo until a new LineNo occurs. I'm just guessing from the way that you stated your results, cuz you never stated what the logic was to accomplish your stated results.

If that's the case, performing what you want via a query will be quite difficult, as the PartNo that need to be considered as Options have NOTHING to relate back to the REAL PartNo other than the LineNo associated with the REAL PartNo. And in a relational database, there is no order, nothing that puts what you consider the REAL PartNo first for all other LineNos.

You ought to modify your Excel table to include a column for REAL PartNo.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: How do I concatenate fields with a query or code?

(OP)
Thanks Andy-

Quote:

Would you know to go after this LineNo or PartDesc ???

There is no way for me to know what LineNo or PartDesc to go after...

Thanks Skip-
You are correct that the first occurrence is the real PartNo.

Quote:

You ought to modify your Excel table to include a column for REAL PartNo.

I apologize but in order to focus on concatenating fields and to eliminate a long drawn out
post that was harder to follow, I left out that I have couple of tables (and queries)
between the DoCmd.TransferSpreadsheet command and the Order_tbl that insert the first
occurrences as the real partNo combining the fields of the Order_tbl

So the spreadsheet actually looks similar to this:
Line	PartNum	PartName	   Order_Qty
1	12211	Network Card		1
1	60000	90 day waranty		1
1	50000	System Install Service	1
1	40000	Onsite training		1
1	13121	Video Card		1
1	31131	Decryption		1
1	12341	Dell OptiPlex PC	1
2	31131	Decryption		1
2	50000	System Install Service	1
2	40000	Onsite training		1
2	12211	Network Card		1
2	12341	Dell OptiPlex PC	1
3	31131	Decryption		1
3	60000	90 day waranty		1
3	40000	Onsite training		1
3	12341	Dell OptiPlex PC	1 

This spreadsheet is dumped into a table called ExcellOrderSheet_tbl from the DoCmd.TransferSpreadshet method.

I have a PC table that looks like this:
PC_tbl:
PartNo	PartDesc
12341	Dell OptiPlex PC
12432	Dell Laptop
12234	IBM Desktop
13422	IBM Laptop
 
and an Options table that looks like this:
Options_tbl:
PartNo	PartDesc
12211	Network Card
13121	Video Card
31131	Decryption
21322	Mini-Keyboard
 
The first query that I run is PC_qry which sifts through the ExcellOrderSheet_tbl
and places records that match the PC part number field from PC_tbl into a table called
Order_tbl:

CODE -->

SELECT ExcellOrderSheet_tbl.Line, ExcellOrderSheet_tbl.PartNum, PC_tbl.PartDesc, ExcellOrderSheet_tbl.Order_Qty INTO Order_tbl
FROM ExcellOrderSheet_tbl INNER JOIN PC_tbl ON ExcellOrderSheet_tbl.PartNum = PC_tbl.PartNo; 

The send query that I run appends Order_tbl with options that are found in the ExcellOrderSheet_tbl
called Options_qry:

CODE -->

INSERT INTO Order_tbl ( Line, PartNum, PartDesc, Order_Qty )
SELECT ExcellOrderSheet_tbl.Line, ExcellOrderSheet_tbl.PartNum, Options_tbl.PartDesc, ExcellOrderSheet_tbl.Order_Qty
FROM ExcellOrderSheet_tbl INNER JOIN Options_tbl ON ExcellOrderSheet_tbl.PartNum = Options_tbl.PartNo
ORDER BY ExcellOrderSheet_tbl.Line; 

This leaves me with an Order_tbl that looks like this:

Line	PartNum	PartDesc	   Order_Qty
1	12341	Dell OptiPlex PC	1
2	12341	Dell OptiPlex PC	1
3	12341	Dell OptiPlex PC	1
1	31131	Decryption		1
1	13121	Video Card		1
1	12211	Network Card		1
2	31131	Decryption		1
2	12211	Network Card		1
3	31131	Decryption		1
 

But the goal is to end up with something like this:

LineNo	PartNo	PartDesc		Options:
1	12341	Dell OptiPlex PC	12211 - 13121 - 31131
2	12341	Dell OptiPlex PC	12211 - 31131
3	12341	Dell OptiPlex PC	31131 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close