Changing the input data to a series of Queries

[wcrimi]

I have a query called "Last Race" that looks at a table containing information on every race every racehorse in my database has ever run. It gets the most recent race for each horse. That query is then used as input to a lot of other queries that pick up information from other tables and do a lot of calculations.



Now I want to do the same exact thing for a horse's last 5 races.

Writing the query to get the last 5 races was rather complex. So instead I exported the races table to an Excel spreadsheet where I have a little routine that made it easy for me select the last 5 races for each horse. Then I imported the spreadsheet back to a table called "Last 5 Races". Now I have the data I need.

All the field names for Last Race query and Last 5 Races table are the same.

You would think it would be easy to substitute one table as input for the other and just run the "last 5 races" table through the same process, but it doesn't work that way.

Various calculations in these queries refer to fields that are prefixed by "Last Race" so the queries know where the original source was in case of duplicate names. That means if I substitute the "Last 5 Races" table at the start of this process a bunch of fields will not be found downstream. I know, I tried it.

I thought that maybe I could rename my Last Race query to something else and rename my Last 5 Races table to Last Race and fool it. That would be a nice idea in "theory". However, the times I renamed a query I either destroyed a bunch of linkages or all the linkages were renamed with it. In this case one would be a disaster and the other a waste of time. So that won't work.

The other possibility is to duplicate every single query that uses Last Race and create a new one that will use Last 5 Races instead and also change all the field names referenced so they point to right place. That's a monumental effort and leaves me screwed long term because any time I change something in one I will have to change it both places.

Anyone have an ideas?

[June7]

What was the query to pull last 5 races? Something like this example http://allenbrowne.com/subquery-01.html#TopN

I don't know if you can get away without duplicating everything. I am not sure if the number in the TOP N parameter can be set dynamically by reference to a textbox.

An alternative is VBA to change the query object with QueryDefs. This might be a way to change TOP 1 to TOP 5 and then everything downstream will still fit.

[wcrimi]

It was similar to that, but it got extremely complex because of the data I have. I got some help here on the forum and we got very close. But eventually I just threw in the towel because it was massively easier to just do it in Excel and then import the new spreadsheet back to Access.

[June7]

Then I suggest you use the same process even for the 1 record requirement. That way no need to change object names or duplicate.

[wcrimi]

Then I suggest you use the same process even for the 1 record requirement. That way no need to change object names or duplicate.

That's the problem. I can't.

The input to a stream of queries is the output of a query called "Last Race".

Now I have a table called "Last 5 Races" that I want to input to the same stream. But the next query in the sequence is looking for "Last Race" as input not "Last 5 races". If I duplicate that one and make it look at Last 5 Races, then I have to change the next one and on and on. (no good)

If I go into that first one and just change it to look at "Last 5 Races" with the intention of changing back and forth as needed, that one query works fine but it eventually gets to calculations later in the stream that are looking for fields [last race].xxxxxx that it will no longer find because the input was now [last 5 races].xxxxxx.

Hope that's clear.

It's a catch 22 unless I reinvent the wheel and have 2 identical streams with 2 different inputs and everything else the same.

[June7]

Use the Excel process to populate a TABLE named Last Race whether you need 1 or 5 records per horse.

[wcrimi]

Ah yes, that will work.

It's a little more manual intensive than just running a query, but that's a very good idea! Thanks.

[June7]

Lesser of two evils??

Good luck!

[wcrimi]

I just realized this may not work.

Let say I have 2 Excel spread sheets, one with each set of data I want. My goal is to upload either into a table called "Last Race" depending on which I want. Sp far so good. The problem is that first I have to get rid of my "Last Race" query because you can't have a table and query with the same name. So let's say I rename the Last Race query to Last Race Old. I don't think that will work because all the other queries than use it will be modified to look for that new name of "Last Race Old". I am in the same boat.

I could also delete the current "Last Race" query, but I'm not sure what that will do to all the queries that use it. Will it mess things up?

[June7]

I just did a test. Built Query1. Built Query2 that references Query1. Deleted Query1. Built table named Query1. Query2 works.

[wcrimi]

Thanks. I'm kind of paranoid because I've had some issues with renaming and deleting queries.

[June7]

Make a backup copy of db first.

[wcrimi]

Make a backup copy of db first.

This worked like a charm! Thanks a bunch for the idea and direction.