Merging fields

[SeriesIII]

I need to merge some data I am importing from a text file. The data is imported into a table like thus;

JobNumber, ..lots of other fields..., MaterialCode

i.e.;

00001, ..lots of other fields... , E6671
00001, , E6640
00003, , E7722
00004, , E6671
00004, , E6640
00004, , E6640

The 'lots of other field' tie up with our database, and are the same where job number are duplicated. I need to end up with the following format, as each job is a single instance to our workshop, we don't need to manipulate or otherwise use the MaterialCOde, but we do need to record it for delivery note reports. Thus, I need to end up with;

JobNumber (no duplicates), ..lots of other fields..., MaterialsUsed ( a single text field of combined data)

So the above data would end up in a new table as;


00001, ..lots of other fields... , E6671+E6640
00003, '' , E7722
00004, '' , E6671+E6640+E6640

I've been scratching my head as to how to achieve this ~ any ideas?

 

[SeriesIII]

No, it's a flat file from a customer system, which is an ancient VAX, IIRC

 

[SeriesIII]

It's a text file, which contains data as per the example ~ like a csv file in effect. Some of the lines, again as per above, are duplicated, but that is correct ~ a 'Job' might have 3 lines, two of which are identical, which would need to be merged , i.e.

00004, lots of other fields , E6671
00004, lots of other fields , E6640
00004, lots of other fields, E6640

would need to become;

00004, 'lots of other fields , E6671+E6640+E6640 <- this is a single field

Annoyingly, one of the 'other fields' is a quantity field, but they claim they can't use that..

 

[neileg]

Trouble is that combining multiple pieces of data in a single field is a no-no in good database design terms, so it goes against the grain to do this.

 

[Extra_Cover]

Is this a one off exercise?

Are all the 'E' codes the same length?

I had a similar problem as solved it using Excel.

 

[SeriesIII]

But is isn't 'multiple pieces of data' to us. We don't use or otherwise manipulate it, we just have a requirement to print it (combined) on a report that prints a delivery note at the end of our shop process.

I suppose an analogy was if we were a food packing company. We would legally be obliged to print the ingredients on the individual pie labels (or whatever), but we wouldn't care what it was, as we just take lots of pies and package them individually.

All our other customers realise this, and so provide the data we need, one of which will be filed with the material codes. It's just these guys who can't.

So, it's as if this single customer is providing a list of ingredients for the final label rather than a text field with the required information. We need to combine them.

I realise this isn't a perfect analogy, you wouldn't include 'apples' twice in an ingredients list, but we need to where a meterial appears twice.

Hope that makes a little more sense!

 

[SeriesIII]

Extra_Cover - no, this will be an ongoing thing that I need to automate

 

[neileg]

If you want this printing then you can use the sorting and grouping options that relate to reports. You don't need to do this in a query.

 

[SeriesIII]

I'm sorry, I don't understand what you mean.

Before I can do anything I need to import the data into my DB. Clearly, I'm not going to import each line into a separate record, as that would give me 3 jobs in my database when there is only one. I just need the data in the last field merging so I can print it out on a label on the end. It has no other meaning to us whatsoever.

Is there no way of combining these before importing into my main table?

 

[neileg]

You're wanting to do it the hard way> Why does it matter whether you have three records or one? It's the output that matters not the data in the table.

 

[SeriesIII]

Ok, I'll try to explain. It is a daft situation, I'll admit.

Each record is a job for our workshop. Unfortunately, we get the same 'job' details multiple times in the flat file, with only the materials field different ~ it will be the same piece of kit that needs machining.

I need to combine the materials fields into one field, and all the other (identical) data from one of the other lines in the flat file so that I cam append it into a single 'job', or record, in our database, so it can be tracked through the various processes we offer ~ if I had multiple records for the same lump of metal things could get messy very quickly.

If we didn't need to record the final materials filed, it would be easy. I could import all the lines in the flat file, ignoring the materials field, delete any duplicates, and bingo. But, for various reasons, we have to have this on the final packing label, even though we have no need for it ourselves nad no requirement to record it locally. As I said previously, this is the only customer who provide the info like this, but they are insistent that they can't change this (I suspect they don't know how).

Hope that explains things.

 

[neileg]

That's what I thought you are doing. What I am saying is that you don't need to do this in a table or a query, you can do it at print time.
http://support.microsoft.com/kb/210163/en-us

 

[SeriesIII]

I think I see what you're saying ~ that I should have a one to many relationship between the 'main' job record and the (just for this customer) materials table and have a completely separate delivery label report, or change all the other import routines to have a separate materials table for all jobs, which would, for everyone else, effectively be a one to one relationship, change the report that prints the delivery labels for everyone.

Is this really less work than combining the single field for this one customer? I think I must be having a dim moment or something.

 

[neileg]

No matter what the current requirements around this data are, you can be 99% certain that at a point in time someone will say "Could you just do...". If you have embedded poor design into your application, this will then trip you up. It is always a good idea to store your data in the simplest form possible because it makes future changes much more straightforward.