How to use a field in an non-related table in a query

rmccafferty · August 2nd, 2009, 08:19 PM

In MS Access I need to use a field in a "constants" table in a query with several tables to which the constants table cannot be related directly.

In simplified form, I take a sum1 field from table 1 and a sum2 field from table 2. These two tables are related by a CustID field. I want to compare those two sums and produce a report if the sums are different by a set, but varying percentage.

I want to have a constants table in which one field is SumDiff. (There are other "constants" in it that I would use in other ways) That is, if we want to see all records where Sum1 in greater than sum2 by the amount specified in the constants table, I would have a query with an expression such as:

if(([table1].[sum1]-[table2].[sum2])/[table2].[sum2]=>[constants].[SumDiff],"yes","no")

When I try to construct this query, it is fine when I bring in table1 and table2 and relate them, but as soon as I bring in the Constants table and do not relate it, the query returns an error message.

Certainly I could "hard code" in the percentage, but it then makes it difficult for the users to easily change that percentage difference in the two sum fields. And we need to somewhat regularly change that number in SumDiff. I would also like to avoid making the user interact with the query by typing in the percentage every time both for ease of use and possible typing errors, but also because the person running the query may not know the number to enter.

How can I draw this SumDiff field into the query from a table that has no other common fields with table1 and table2?

Old Pedant · August 3rd, 2009, 01:56 AM

You could do this with an outer join.

I wish you had shown your main query. As it is, I'll have to just use a sample:

Code:

SELECT T1.custID, T1.sum1, T2.sum2, 
       (T1.sum1-T2.sum2)/T2.sum2 AS pct.
       IIF( C.SumDiff IS NULL, 'No', 'Yes' ) AS isPercentageBigEnough
FROM ( table1 AS T1 INNER JOIN table2 AS T2 ON T1.custid = T2.custid )
LEFT JOIN Constants AS C ON ( (T1.sum1-T2.sum2)/T2.sum2 ) >= C.SumDiff

This assumes that there is only one row in the Constants table. And you put each contant into its own field.

If, instead, the Constants table has only two fields (ConstantName, ConstantValue say) then we'd need a variation on that. Incidentally, I really think that such a table with only the two fields is a superior design. It allows you to add new constants at any time without needing to restructure the DB. The only thing I might do is have 3 fields: ConstantName, ConstantNumber, ConstantString. Where each ConstantName has a value in *EITHER* (but not both) ConstantNumber and ConstantString. And use DOUBLE for the ConstantNumber. It can always be compared to any other numeric type without need for conversion.

Oh, what the heck. Here's what the query would look like if you used my concept of a 3-column Constants table:

Code:

SELECT T1.custID, T1.sum1, T2.sum2, 
       (T1.sum1-T2.sum2)/T2.sum2 AS pct.
       IIF( C.SumDiff IS NULL, 'No', 'Yes' ) AS isPercentageBigEnough
FROM ( table1 AS T1 INNER JOIN table2 AS T2 ON T1.custid = T2.custid )
LEFT JOIN Constants AS C 
ON ( C.ConstantName = 'SumDiff' AND ( (T1.sum1-T2.sum2)/T2.sum2 ) >= C.ConstantNumber )

The extra parens in the ON clause for the LEFT JOIN *are* necessary, but if you store this as a query in Access and then retrieve it, Access will have lost the parens. It's a bug I reported in Access98, Access2000, and Access2003. Dunno if it got fixed in more recent versions.

SerranoG · August 13th, 2009, 08:15 AM

Without knowing table structure and other details, my first guess would be

iif([table2].[sum2] = 0, "N/A", iif(([table1].[sum1] - [table2].[sum2]) / [table2].[sum2] => [constants].[SumDiff], "yes", "no"))