Skip to Content
Former Member
Jun 22, 2012 at 02:17 PM

Linking based on one of four fields



I have a report that uses two SQL queries (incredably slow without).

One query uses a Product table that links data from four sources - Stock, Reserved, Ordered, and Usage. Subqueries for pulling data from these tables use varying criteria, but for the purpose of this question, the query (command) uses a parameter for passing location (simply 1, 2, or 3). The rows that are returned have four fields: Loc_Stock, Loc_Rsrv, Loc_Ord, and Loc_Use. These fields will always contain a value of 0 or the location passed. All, or some of the fields will have a non-0 value, but will never be all 0s. e.g. - 0,1,0,1 - 1,1,1,1 - 1,0,0,0...

The second query pulls data from a table maintained in a separate database. It wants to match up by location, so I need to pass this location value, either from the command parameter or from something like the max value of the four location fields.

I can probably get by with creating a parameter in the second query for location, but I'm trying to avoid the possibility of User error in entering two different locations. I guess ideally I'd like to be able to create a parameter at the report level that passes through to the queries.

I also need to print the location name in the header and wonder about the ability to use the command parameter beyond just the query.

Any ideas?

Matt Tallon