Loop a Query

[LadyDi]

In my database, I have a table that houses information based on order project. An order project can have anywhere from 1 to 50 orders tied to it. This is just one record in my database, but there is a field that states how many orders go with the project. For average purposes, I would like to set up a query that will show one project number multiple times based on the number of orders (i.e. if a project has 10 orders, I would like it to show 10 times in my query). Is there a way to do that?

 

[arnelgp]

if 50 or more orders, union query will not suffice if it breaks the 512 character limit in query.

you need temporary table to hold the results using a function.

 

[jdraw]

For average purposes

depending on what this really means
why could you not just do AVG against the field that has the number of Orders

Select avg(yourOrderCount) from yourTable

 

[LadyDi]

I'm actually trying to get an average of the days to process the orders. The easiest way I could think to do this is to give each order its' own line. So, if a project with 10 orders takes two days to process, the average days to process and order is actually 0.2, not 2.

 

[jdraw]

if it you process 10 Orders in 2 days
then you process 1 order in (1*2)/10 days ====.2 days

I'm not sure where your 2 fits??

 

[Simon_MT]

The advantage of eacvh order having a separate record is thast you can record the time on individual orders. If that is not necessary you could store the total number of orders for each project. If anyone mentions storing totals in records then look at SQL Server Cubes temp tables storing totals.

I have four subsystems that are analysed by Scheme there is no way I'm going to get quick analysis using Queries. So each time entries are made in each subsystem the total values for each Scheme are updated to the master record.

Simon