How do I concatenate fields with a query or code?

[irethedo]

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:

[pre]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
[/pre]
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:

[pre]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[/pre]
Is there a way to concatenate these fields with a Query or is it easier to do this with code?

Thanks

 

[Andrzejek]

You may find some useful information here: faq701-4233

But just to get this:

[pre]
LineNo PartNo PartDesc
1 12341 Dell OptiPlex PC
2 12341 Dell OptiPlex PC
3 12341 Dell OptiPlex PC
[/pre]
Your Select statement would look like either:
[tt]
Select LineNo, PartNo, PartDesc
From Order_tbl
Where LineNo = 12341
[/tt]
or
[tt]
Select LineNo, PartNo, PartDesc
From Order_tbl
Where PartDesc = 'Dell OptiPlex PC'[/tt]

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[irethedo]

Thanks Andy-

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.

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:
[pre]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[/pre]

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:
[pre]PartNo PartDesc
12341 Dell OptiPlex PC
12432 Dell Laptop
12234 IBM Desktop
13422 IBM Laptop
[/pre]
and an Options table that looks like this:
Options_tbl:
[pre]PartNo PartDesc
12211 Network Card
13121 Video Card
31131 Decryption
21322 Mini-Keyboard
[/pre]
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:

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:

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:

[pre]
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
[/pre]

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

[pre]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[/pre]