Results 1 to 3 of 3

Thread: Views

  1. #1
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58

    Unanswered: Views

    Creating Views in Access 2000 thru the GUI I have not found a way to use a "variable", eg referencing a text box, or a user defined function. This could be done if the view could be created programmatically from a sql string.
    I can find no reference to this in help. Can this be done ?
    Or should I go to stored procedures with parameters ?

    I am creating a complex select statement, using nested subselects for aggregates, & could make it much more readable & testable if the subselects could be views.
    catkins

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Could you post an example of how you would like to use this? I'm not sure I have a handle on how the variables would be used for the view.

    Usually in a scenario where I need to use a subselect, I filter the data using the where clause. You can reference values from the "main" select statement in your subselect, therefore "passing" the value.

    Eg:

    Say I have a table with codes & descriptions, and a table with codes & amounts

    SELECT t1.code, t1.description, (SELECT SUM(amount) FROM view WHERE view.code=t1.code) AS amount

    Basically this is passing the t1.code as a variable to the subselect for each record.

    Is that what you're looking to do?

  3. #3
    Join Date
    Dec 2003
    Location
    Tasmania
    Posts
    58
    Thanks for that. I am ok with subselects. My question was more on how to create views in Access programmatically, so that I could use views to build up a select to make it more readable - & testable.

    However I got around it by using named strings for the components.
    eg
    strSql_ItemsLocnsTests = _
    "SELECT L.ItemUniq, L.ItemId, L.SerialNum, L.ItemDesc, L.LocnDesc, " & _
    "T.TestFreq_Months, T.TestDate " & _
    "FROM " & _
    "(" & strSql_ItemsLocns & ") L " & _
    "RIGHT OUTER JOIN (" & _
    strSql_ItemsTests & ") T " & _
    "ON L.ItemUniq = T.ItemUniq " & _
    "ORDER BY ItemId"
    catkins

Posting Permissions

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