Form to run different queries (1 Viewer)

[Number11]

So i need to have a form to run different queries depending on the week number selected

Please Select week No: (01-41)

If then week number 01 is selected it would run query providing the data for just that week no

Reports_0 (which contains weeks 01-03 data) have to split as the data is too large

Report_1 (which contains weeks 04-06 and so on?

 

[CJ_London]

have to ask, why do you need separate queries for each week? why not just include a criteria to limit the returned results to that week? Then you only need the one query.

Or are you saying you have too much data so have separate tables - in which case presumably they are in separate databases since there is no benefit in have separate tables in the same database.

But to answer your question - perhaps use a case statement

Select Case weekSelected
    case 1,2, 3
       'run a query for these weeks
    case 3,4,5
         'run a query for these weeks
etc

end select

 

[Number11]

have to ask, why do you need separate queries for each week? why not just include a criteria to limit the returned results to that week? Then you only need the one query.

Or are you saying you have too much data so have separate tables - in which case presumably they are in separate databases since there is no benefit in have separate tables in the same database.

But to answer your question - perhaps use a case statement

Select Case weekSelected
    case 1,2, 3
       'run a query for these weeks
    case 3,4,5
         'run a query for these weeks
etc

end select

the data tables are too big and i did union them, but it takes hours to run so need to change to individual queries

 

[CJ_London]

If it is slow, it is probably due to one or more of poor table design, lack of indexing or query design. The fact you are using a union query points to poor table design although there are occasions when they are required.

A client of mine had a query which they had to run overnight because it took so long - with a few adjustments I got the time down to a few minutes.