Slow Concat Function

Please see attached Sample,.

This may look like its fairly quick,.. but its the slow as hell,.. across the network "front end back end" especially when cross referencing againts qty's already ordered. "not included in sample"

I have only attached a basic sample of the query with some data, on my full system this acts very much like a Dlookup would do on a continuous form. "if you were foolish enough to use one there"

Check out the module for the code

My Question is: Is there an alternate "Speedy way" to produce the same results as seen in the sample.


To explain more,.. this is what i have and works fine,..."except having to wait a minute for the concat" which as you can imagine is very irritating for the user,.. when they have many lines to purchase.

When a job is created item required to be purchased are put in the Requests Table

Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order1114N4082-6Leading Edge SkinStock Required1eaPriority 1WO07-421

Often the same part is Required on a different job,.

Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order50114N4082-6Leading Edge SkinStock Required1eaPriority 1WO07-690

A query then creates a list to purchase

Code:ID #Part NumberDescription StatusQty ReqUOMWork Order1-50114N4082-6Leading Edge SkinStock Required2eaWO07-421 - WO07-690
The ID is then purchased against on the Order Screen

The Id goes into the purchase order table

The Id is then Split back down to its original numbers

The original numbers then look back at the Request table and update the status to Ordered

Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order1114N4082-6Leading Edge SkinOrdered1eaPriority 1WO07-421


Code:Tbl_03_RequestsID #Part NumberDescription StatusQty ReqUOMPriorityReq #Work Order50114N4082-6Leading Edge SkinOrdered1eaPriority 1WO07-690

And so on,. When the unit is received it changes the status to received.

I can't do this with a temp table,.. that will take a bit of explaining so please take my experiance and word on that.

Sorry about the Table Formatting,... Is there another logical way to do this.