how to introduce a constant value?

[bobchapman]

I want to use a "constant" in some of my calculations. In this case it's a postal mailing application which selects different names-and-addresses depending upon whether it's the first, second, or third attempt to contact people. So, I want a way to enter the value 1 into every record of a select transaction this month, the value 2 next month, etc.

I'm running Access 2007 with Windows 7 Pro.

I've tried to do this by having a table containing this value and another value used in a similar manner. Then I did a select query with the name-and-address table and this "values" table. I end up with duplicate records in the query result: every name appears exactly twice. I set up a test database, with the same result. Obviously, I'm doing something wrong, but don't understand what my error is!

Thanks for any help,
Bob Chapman

[ruirib]

Have you tried to use DISTINCT in the SQL of the select query?

[johnhutchison]

You can introduce a constant value into a query as show below. This would display 2 on each row.
Just type a name (any name you like) a colon then the constant you want.
[attachment=91135:QueryConstant.gif]

[bobchapman]

Thanks, John.
Now, suppose that I want the value "2" in your example to be used in several queries, but arranged so that I only have to enter it once somewhere (in another table, for example). In facat, I have several of these constants that I want to introduce to tell the software what to do. So my approach was to have a table, call it Values, containing these values, and then pull that table into the appropriate Queries that I develop.

DISTINCT does not seem to solve the problem for me.

It does work if I have a table with only a single value in it, and that's how I've worked around the problem for now. But it seems like a wrong solution to me.

Bob

[ruirib]

Maybe if you tell us the structure of both tables and the SQL used in the query we can provide better help. I agree you shouldn't be restricted to a single value table, but can't say much more until the problem is better known.

[johnhutchison]

It makes sense to have a table of such constants, but each time you want to use one you need to say which one you want to use. Otherwise they all get used, and you get the problem of mutliples. One way to restrict them is to impose criteria on the query.
Another consideration here (that may or may not be relevant to your situation) is that over time the values you want to use may need to be changed. Ideally you want to be able to just change the values in the table, and not need to modify the queries.

You can use custom functions in queries. So another option is to create function that does a lookup of the relevant value in the table. But you still need a way of specifying which value you want found. This would be slower than just joining the table, but with small data sets you would not notice it.

[ruirib]

When using constants in situations similar to yours, I usually add a name column, so that I can use the name to identify the constant, when I want to retrieve it. It's almost as using a constant in your a programming language, you need to give it a name .

[johnhutchison]

Another thought on this.

If you introduce multiple values into your constants table, by using multiple fields rather than multiple records, it should solve your problem.
You won't get duplicates in your queries. For any query where you want to use one of the constants, you just need to choose the correct field for the situation.