Add index to make-table query

[RichardMGreen]

Hi all

I have 5 make-table queries and I would like to add an index to each table as it's made. The index would be on the same field in each table but the entries in each field are not unique, i.e. you can have the same value multiple times.

That gives me two questions:-
1) Is it possible?
2) How would I do it if it is?

I have googled the problem but could get anything useful/understandable out if it (I'm a bit of a novice when it comes to things like this).

Any help greatly appreciated.

 

[Norie]

Richard

Page setup was one of the things I was going to suggest might be causing a slowdown.

Have you only removed one part of it?

If you remove the rest is it any quicker?

The other thing I was wondering about was the userform(s).

Are they essential or, as I think they might be, only being used to monitor progress?

Those are the 2 things that stand out after having a quick look.

I'll have a closer look later.

 

[RichardMGreen]

Hi Norie

I've removed the part that changed for each member ID and that's speeded things up considerably. Just removing that part has made the whole thing go from producing 4 reports a minute to an average of 11 so I think I'll suggest that it stays gone.

One userform is used for password entry and disappears again straight away (couldn't use an inputbox as I need the characters to be hidden).

The other (userform1) is used to monitor progress. It's more of a "nice to have" than a necessity, but it shows the process hasn't fallen over.

 

[Norie]

Richard

I wasn't sure what the first userform.

I can see the advantage of having something to monitor progress and I've used it myself.

However when it's appeared to be slowing things down I've usually removed it.

You can't really replace a userform but you can monitor things in other ways.

The simplest one would be using the status bar I suppose.

I've even just logged progress on a worksheet, which isn't really monitoring I suppose.

Anyway, none of that matters if the userform isn't slowing things down and if you are going to pass this on might be good to keep anyway.

Almost forgot, instead of a header footer could you not just stick the ID somewhere on a worksheet?

You could just put it discreetly at the bottom, or use a large font and bright colours to make sure everyone see's it.

 

[RichardMGreen]

The member ID one the first page of each report anyway.
The draft I was given in PDF format had it as a page footer so I was trying to keep it.

I don't think removing it will make much of a difference to the final report, but it really has speeded up generating times.

 

[RichardMGreen]

Hi Norie - just had confirmation that the footer can stay gone (if you see what I mean).

Thanks to one and all for all your help.

 

[SydneyGeek]

I've just found out what's taking the bulk of the time.
I've removed the "Adjust_Footers" section which puts the C4C ID in the center footer of each page and it's now churning out 11 reports a minute (from around 4 with the footer in place).

If they need to keep the footer in place, is there a better way of doing it?

Richard,

Good to see that you found the culprit. If the footers can stay 'lost', great. Otherwise I remember seeing a comparison using an Excel4 Macro call that was significantly faster for page setups. Could try to dig it up if you are interested.

Denis

 

[RichardMGreen]

Hi Denis

I'd be obliged if you could dig it out. It will probably come in handy for future projects.

 

[RichardMGreen]

Bit of a random question regarding the footers.
I've done a bit more digging and it looks like I can add the centre footer (which was causing the problem) just before the document is printed.

Would I need to add the code to each sheet or can I just put it on one and have it populate all the sheets in one go?

Also, is there a way of adjusting/adding the footer at application level (to get all the sheets on one go) rather than at sheet level?

 

[SydneyGeek]

Hi Richard,

Still digging but I came across this...
http://www.mrexcel.com/forum/showthread.php?t=9141

A good starting point.

Denis

 

[RichardMGreen]

Thanks for that. I'll have a look when I get chance in case they change their minds.