Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Have you tried to use DISTINCT in the SQL of the select query?
    Rui
    -------
    R4

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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]
    Attached Images Attached Images
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts
    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

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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.
    Rui
    -------
    R4

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  7. #7
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    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 .
    Rui
    -------
    R4

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •