Most efficient way to solve this problem

[CJ_London]

Why not use a make table query to create a local table with just the two fields from your rf1 query . Ensure both fields are indexed in the new table then run your query on that.

Time wise will at least give you an idea of efficiency.

i have tables with millions of records and no query takes longer than a few seconds to run

If your rf1 query is complex because the source data is not normalised perhaps it is time to consider importing the data into a normalised structure

 

[Space Cowboy]

I learned a lot yesterday, my joins were abysmal, on random names from the tables that seemed like a good idea. With some joins even the wrong way round. as soon as I improved them and linked ID fields to Id fields things got a lot,lot better. one query returned three and a half million records in a few seconds. It is shocking to think how bad the other queries are, even those that I thought were working well. I think that the source data is good. its mostly my fault. I read someone write its like rowing around in the mist, its worse than that, I have no paddles. but I have seen a tree I need to chop down to make one.

I like your suggestion, I could use that in several other queries. I will need to learn how to do it.

One of the problems, as I see it, is that some fields I would expect to be in a table are not, so I have to join two tables and then save it. then I have to link to tables again creating massive spaghetti junctions. I think that I kind of need to link together the half dozen tables that I need into one big lump with 200 fields and some how turn that into an auto updating thing and at least have everything in one place.

One of the possible solutions I am going to try today is your DCOUNT soloution and rank the dates with <= 6
Another is in the Formatting arena. So much to learn.

 

[Gasman]

visual foxpro

So why can't you do this in VP?

So, so , so much faster than Access.

 

[Space Cowboy]

So why can't you do this in VP?

So, so , so much faster than Access.

Thank you for that suggestion,

What is VP? could you please outline a brief overview of what is needed to do that?
I used access because thats what I had on my computer.
Since I am learning a new language it may as well be the right one.

 

[Gasman]

VP = Visual Foxpro ?

 

[Space Cowboy]

Access is fast enough for me - when you get it right it is like lightening. It took me a long time to find that 32 bit interface and even longer to configure it. It was that long ago I forget how I even did it.

and I would miss everyone, a nice friendly helpful bunch.

You can't buy that!