Run Macro Several Times based on different criteria

[tylerpickering]

Hi all,



I have a database where I am getting averages rates based on an origin/destination (logistics). I currently have it set up to have (1) the user enter the origin, destination, and radius, (2) database will calculate radius distance based on origin/destination and return average rates if they are within this radius.

Example:
Los Angeles CA to Dallas TX ---> $2.50 per mile on average

However, I want to be able to run these in batches instead of one at a time. The problem is that the database will have to calculate the first origin/destination radius, return the average cost per mile, and then continue on to the next one and do the same.

Example:
Los Angeles CA to Dallas TX---> $2.50 per mile on average
Chicago IL to Dallas TX---> $3.50 per mile on average
Boston MA to Dallas TX---> $1.50 per mile on average

I will basically need to run the same queries over and over in order to do this, but I am unsure how (or if it's possible). Any suggestions?

Database with dummy data is attached; requires a PCMiler module so I am unsure if it will work without that, but you can at least get the general idea of what I am trying to accomplish.

IM DB 3.0 - Copy.zip

Thanks so much!

[ranman256]

A query runs everything in batch.

Select STARTCITY, ENDCITY, SUM(MILES) as dist, avg(MILES) as Avg

(you may need 2 queries)
Q1 = Select STARTCITY, ENDCITY,calc(miles) as Miles

Q2 = select * , avg(MILES) as Avg from Q1

[tylerpickering]

Hi Ranman,

Thanks for the reply--my problem is that I will have (1) my table of data and (2) my imported table of origin/destination city-state that my user will want to compare (instead of the current form).

So say that they have 10 different origin/destinations that they want to compare--that means that I will have to compare every single origin in my data table to the first origin in the user requested table, then recalculate the radius distance for the second origin and get average rates, etc. (does that make sense? I may be rambling, sorry!)

So here's a really small example--say my data table is as follows
Chicago IL to Dallas TX 2.50
Chicago IL to Plano TX 2.75
Chicago IL to Los Angeles, CA 3.60
Chicago IL to Fort Worth TX 1.90
Chicago IL to Columbus OH 2.46
Chicago IL to Cleveland OH 2.50
Chicago IL to Akron OH 1.88

And then the user imports the following rate requests (100 mile radius)

Chicago IL to Dallas TX
Chicago IL to Cleveland OH

This means the database will need to compare the Dallas TX one to all points in the database (for the radius of 100 miles) and then next compare the Cleveland OH one to get that radius as well.

I may have done a terrible job of explaining my issue...does that make sense?

[June7]

Don't quite understand the process.

Why is the form unbound? Is this used only to input filter criteria, not enter data to table?

As ranman suggests, a SELECT query with calculated fields should be able to generate the data. Those calculations can reference textboxes on form for inputs. Apply filter criteria to restrict the dataset.

Then build a report based on that query to display/print the data.

Why do you have Filtered_Table? Why do you have UPDATE, INSERT, and MAKE TABLE queries?

Any process that regularly modifies db (MAKE TABLE query) is poor design. Actually, it appears you have a DELETE query that deletes records from Filtered_Table therefore the MAKE TABLE is not even needed.

[ranman256]

I hope your tables know the distances, ....then yes sql can compare all cities where Chicago to X <=100 miles.
but I dont know how your tables are built to give you the sql.