Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: checking for row data origin? (which table)

    one db has sales and purchases in two different tables, each starting on a high number what will "always" have 5 as the first digit for purchases and 7 for sales.

    I do checks for this when using queries that lists values from both tables :
    If Left(Me.ID_buy, 1) = "5" Then ...

    However, is there a more generic way to test this?

    The underlying table for each value will indeed be a different one, so I assume I could to a check on if this value comes from table x then ... ? How can that be done?

    Would such frequent checks put unecessary strain on the db? Is it significantly less work for this multi-user db if I check on the value instead of "delving into the db internals" ?

    D.

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    I can't see your queries so my answer is kind of generic.

    Add a field on you queries that id the source table, like 1 for Sales 2 for purchases. This works well for union type queries to allow the code a way to determine which table the info came from and take proper action if needed.
    KC

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by AZ KC
    I can't see your queries so my answer is kind of generic.

    Add a field on you queries that id the source table, like 1 for Sales 2 for purchases. This works well for union type queries to allow the code a way to determine which table the info came from and take proper action if needed.
    hmmm. I already sort of know that by having unique number series in the autonumber field, that is what the check for 5 or 7 is for.

    Anyway, that is a solution based on querying the data, my initial question was how one can perform a check on the very name of the underlying table ("behind the query").

    D.

  4. #4
    Join Date
    Feb 2004
    Posts
    142
    Originally posted by kedaniel
    hmmm. I already sort of know that by having unique number series in the autonumber field, that is what the check for 5 or 7 is for.

    Anyway, that is a solution based on querying the data, my initial question was how one can perform a check on the very name of the underlying table ("behind the query").

    D.
    I know of no way to directly check which table a specific ROW came from without building it into the query resultset itself or through the table in a field or other property as you have done.

    If you need to simply determine the source table of a given query I supose you could check the querydef SQL property for table names in the SQL string using instr. But this would only work if you know the table names in advance of coding.

    To hash out the table names is possible if you know SQL structure and want to write a routine to parse it but it would take some time to get it done.
    KC

  5. #5
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by AZ KC
    If you need to simply determine the source table of a given query I supose you could check the querydef SQL property for table names in the SQL string using instr. But this would only work if you know the table names in advance of coding.
    In this case - and most situations similar to this one - I know the table names, only want to check if it is one of two (which one) a certain value comes from, typical the ID field, so the querydef./"instr." might be what I am after. Will check if I have some documentation/code example on those expressions.

    Thanks.

    D.

Posting Permissions

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