compond field

[HowardHammerman]

My client has a database of parts and because of some limitations in the database, she has to, in some cases, insert the quantity and the part number into the same field. Here is an example:
12-12345;6-54321
This indicates that for that record (linked to a piece of equipment) there are 12 of part 12345 and 6 of part 54321. Of course there are many records of this type.
My assignment is to create a summary report indicating the grand total of all the various parts. That is, I need to sum up all the quantities of part 12345 and all the quantities of part 54321. There is no reference table of valid part numbers.
I am able to create separate fields for the two quantities and the two part numbers. The trick is to GROUP on the two. Not every record has two.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[IanWaterman]

Howard

I think you will have to do this in SQL. If you are using SQL Server 2008 you can use Split() which is same as function in Crystal.

In SQL Command you can run through data twice and unioning the two queries together using Split(field)[1] in first query and Split(field)[2] in second query excluding nulls when there is no second part number.

Ian