Query a result that should first be concatenated based on a sku & sequence number

hmm...

1. Select all rows having ITEM01 (or whatever ID you want) as the Sku, ordered by SequenceNumber. You need to collect these rows in a recordset (presumably your db_connect.asp takes care of this).

2. declare a variable strDescription.

3. Loop through the recordset, building the description...
Do Until rst.EOF
    strDescription = strDescription + rst("Description")
    rst.movenext

Loop

Job done.

Not sure if the syntax for the recordset is right (think I might be confusing my ADO and DAO!) but the idea should work :).

s46

Wow - interesting question.

The easiest solutoin is to restructure your database to allow for a single line description or alternatively build a second table with the description in concatenated form.

Here is the problem. In order to get a complete description field you have to join fields from different rows together. Because there can be any number of rows making up the description a single query will not do it.

What I would do is build a second table which has the Item code and description fields. I would then create a process that runs each time new data comes in to do the following

Does the Item ID exist in the new table
    NO: Insert new_table (item_id, descr) values ( ... )
    YES: update new_table set descr = descr + next_descr where item_id = incoming_item_id

You will need to do this for each new record coming in. When done the new_table will contain an Item ID and a complete description on which you can do your search.
I don't know if your database supports Stored Procedures and / or triggers but these would be useful in updating the new_table.

Does this help?

Thank you for reponding solution46 and julianH.

I am considering your ideas as options & will need some time to test.  I anticipate that neither can in whole solve my problem but hope that they can be used as tools.

solution46:  The problem anticipated with your suggestion, is that it appears to only deal with one 'item'.  What I believe I need to do is "build" the description for EVERY item in the database into a recordset / result which itself can be keyword searched / queried (what a waste of server resources - I know).  It sounds like your idea is on the right track & I will play with your sample to see if I can accomplish it.  At the glance, it still looks like I will have problem but won't know until I kick it around a bit.

julianH:  An interesting alternative that I had not thought of.  The problem(s) anticipated with your suggestion is likely due to foundational stuff that I failed to include in my initial post; This is an proprietary Mas90/200 ERP system.  New items are added to the proprietary ProvideX db using a GUI interface / not the web app that I'm working on.  The database is jacked / there is probably not much I can do with it unless I do as you suggested to a table in an Access db or something.  But I still run into a problem of dynamically adding new inventory item to the new table when the addition is coordinated through the chitty GUI.

Make sense?

I have decided to present some additional information that at first, thought would just further complicate the matter but know see that it is possible to help.

There exists another table, the InventoryMasterfile.  This is actually the main inventory item table where, the table in my question above is the ended description table.  The InventoryMastefile holds records based on a unique sku.  If the inventory item has a description less than 50 characters, then an ItemEndedDescription table entry is not created.  If the item description is greater than 50 characters, I believe the first 50 characters of description are entered into the InventoryMasterfile table, & re entered in the ItemEndedDescription according to the format in my original post / question.

What I am thinking is, that the Sku field (being unique) in the InventoryMasterfile field can be the index to identify which items in ItemExtendedDescription get their descriptions "built".

Anyhow I'll post my progress hopefully soon.

P.S.

When I stat that "I believe the first 50 characters of description are entered into the InventoryMasterfile table, & re entered in the ItemEndedDescription according to the format in my original post / question." I actually mean that the data is INSERTED into the tables at runtime when the user is entering them through the proprietary GUI.

Also, through a "Y" or "N" value, the InventoryMasterfile does flag whether or not the inventory item description is larger than 50 characters / whether or not data is entered in the ItemEndedDescription table for item.