Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: Derived tables in Access

    Hi,

    I'm new to Databases and I'm starting in an entry position that has a lot to do with elementary things of databases. That is the same case for a co-worker and we have a little discussion going on about derived tables in Access.

    To make it easier to explain, let's say we are looking at the ER diagram and we don't have access to any other view of the Database. I say that there are not derived tables on that diagram because by definition a derived table is a table obtained from other tables directly or indirectly through the evaluation of a query expression, and if we look at those tables, there are fields in each of them that can't be find in any other table of the Database. From this ER view a derived table would be a table that can be entirely gotten from fields in the other tables.

    He says he doesn't believe me, and I'm kind of new in this subject, so now I'm kind of un-sure if I my reasoning is right. Who is right ?

    Thanks.

    Regards,

    David

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Interesting...I'ld say it depends upon how the derived table was initialy structured and the specific purpose it serves. If the data within the derived table will not interfere (modify) with current data held within the database tables which the derived table is generated from in any way then I'ld say you're right. But if it does then I'ld say you're wrong.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    May 2006
    Posts
    2
    Thanks CyberLynx for your answer.

    I'm trying to understand what you meant as I'm new in this subject:

    The discussion I have with my co-worker is how to determine if there are derived tables just by looking at the ER diagram. When you said: "it depends upon how the derived table was initialy structured", what do you mean, having in mind that we are just looking at the ER diagram, and we don't know which tables from that diagram are base and which are derived. That's what we are trying to determine.

    Regards,

    David

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DavidMoore
    Thanks CyberLynx for your answer.

    I'm trying to understand what you meant as I'm new in this subject:

    The discussion I have with my co-worker is how to determine if there are derived tables just by looking at the ER diagram. When you said: "it depends upon how the derived table was initialy structured", what do you mean, having in mind that we are just looking at the ER diagram, and we don't know which tables from that diagram are base and which are derived. That's what we are trying to determine.

    Regards,

    David
    Cyber is quite right ...Since you're looking at an ER diagram AFTER THE FACT, you can't know which tables are derived or not ... Keep in mind: you can have junction tables that have no new info in them but bind to disparate tables together ... Which is which?

    As to your original question: in my minds eye I would consider a "derived" table to be a temporary table and as by nature that would not and should not show up on an ER diagram ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well here we go... a bit of controversy

    I actually agree with you David young man. A derived table to my mind is a SQL construct. As such it is not a table at all. Very simple example (very simple indeed actually):
    Code:
    SELECT * 
    FROM (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable
    http://www.google.co.uk/search?hl=en...ved+table&meta=

    I think you and your co worker need to be specific about what you mean by derived table. I suppose you can use it as a natural English term to mean "a table that you created that was derived from another table" but, as Mike says, you wouldn't expect that to appear on an ERD.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    So Poots ... By your definition (proxy for Young David's) then any stored query in Access would be a derived table ... Or for that matter, any adhoc query generated ...

    I can buy that. When a query is run, it does go into an internal temp table that Access provides ... Hmmm ...

    Yes indeed. The only problem I'm having is with the "table" nomenclature ... It kinda confuses the issue.

    Your example confuses this issue to: Does code (compiled or not) itself constitute the the end result (when is a table a table?)
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh dearie me - my hangover is really kicking in now

    I am not certain but I think that you think that I think that any SQL result is a "derived table". I actually meant the very specific case where a subquery is parenthesised and aliased in a FROM clause. To expand on the example:
    Code:
    SELECT * FROM ##MyOtherTable is a "real" table## MyOtherTable INNER JOIN ##MyTable is a "real" table. MyDerivedTable is a "pseudo" table derived from MyTable## (SELECT * FROM MyTable WHERE MyID >10) AS MyDerivedTable ON MyOtherTable.ID = MyDerivedTable.ID
    Derived Table is defo a SQL term for the above (the link shows many examples of its usage) however this might not be what David means. I guess it still comes down to what David means by a "derived table"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I got what you're talking about ... David needs to know what the SCOPE of a derived table is ... Per your definition it will not be in an ERD ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think we are agreed then. David is our hero and his co worker is the villain.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    I think we are agreed then. David is our hero and his co worker is the villain.
    He is not my hero. A newbie can never be my hero. He is correct tho ...

    And no, you're not my hero yet ... You've not pulled my bacon out of a fire yet ...
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Indeed not - you are, after all, the grand poobah

    I have four heroes on this forum:
    You, Ted, Izy and Wayne. Rudy if counted but he is more of an Access forum butterfly.

    Mark is heading that way and posts like his last one are only helping his cause.

    I am my own hero but I admit to a little bias in that regard.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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