LEFT JOIN problem

Discussion in 'ASP General' started by Mary, Jun 18, 2009.

  1. Mary

    Mary Guest

    I have two databases and want to combine the data like below:

    BRecord.mdb.BRecord
    ProductID Qty Year Month
    A101 20 2009 6
    A102 30 2009 6

    Goods.mdb.Goods
    ProductID Name
    A101 Flower
    A102 Dog

    I want to get the result as below
    ProductID Qty Name
    A101 20 Flower
    A102 30 Dog

    I try the following clause but failed,

    Goods = Server.MapPath("Goods.mdb")
    BRecord = Server.MapPath("BRecord2004.mdb")

    SQL = "Select ProductID, SUM(Qty) As EEE From ("
    SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where Year =
    2009 And Month = 6"
    SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID = " &
    Goods & ".Goods.ProductID"
    SQL = SQL & ") Group By ProductID Order By ProductID"

    Can anyone help me ? Thanks !
     
    Mary, Jun 18, 2009
    #1
    1. Advertising

  2. Mary

    Bob Barrows Guest

    Mary wrote:
    > I have two databases and want to combine the data like below:


    Type and version of database please? It is almost always relevant.

    >
    > BRecord.mdb.BRecord


    OK, I think I see that this is Access (Jet) but you should start by telling
    us this, instead of making us guess.

    > ProductID Qty Year Month
    > A101 20 2009 6
    > A102 30 2009 6
    >
    > Goods.mdb.Goods
    > ProductID Name
    > A101 Flower
    > A102 Dog
    >
    > I want to get the result as below
    > ProductID Qty Name
    > A101 20 Flower
    > A102 30 Dog


    This appears to be a simple join to me. Why do you use the word "LEFT" in
    your subject?

    The only problem I see is that these are different database files. You will
    not be able to perform a sql join between them given that they are in
    different databases, unless one of the databases has a link to the table in
    the other database.
    >
    > I try the following clause but failed,


    Please describe your symptoms without using the words "didn't work", or
    "failed", or "no joy". Did you receive an error message? Incorrect result?
    Did your computer crash? Did the world end? :)
    >
    > Goods = Server.MapPath("Goods.mdb")
    > BRecord = Server.MapPath("BRecord2004.mdb")
    >
    > SQL = "Select ProductID, SUM(Qty) As EEE From ("
    > SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where
    > Year = 2009 And Month = 6"
    > SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID =
    > " & Goods & ".Goods.ProductID"
    > SQL = SQL & ") Group By ProductID Order By ProductID"
    >


    Well, this seems very wrong-headed (sorry). A Join statement requires two
    database tables on either side:
    table1 join table2
    You have a table and what appears to be a field. Please show us the result
    of :
    Response.Write SQL

    If you have created a sql statement correctly, you should be able to open
    your database file in Access, create a new query, switch it to sql view,
    paste in the sql statement from the browser window and run it without
    modification.

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #2
    1. Advertising

  3. Mary

    Mary Guest

    Dear Bob,

    The version of two databases is "Microsoft.Jet.OLEDB.4.0"

    Yes, I am newbie in using "Join", especially join on two different database.
    Since I search through the internet, most users join two tables within one
    database, it seems very simple, unfortunately I can't find a sample that
    join two tables in two different databases, thus I copy some clauses to
    modify but can't make the way, this my problems.

    I am using frontpage 2003 to write the program, and back to IE to see the
    result . Each time if the SQL statement failed, the browser will only tell
    me "Here needs an object", no hints what going wrong. That make me crazy .

    Fine, by using "Response.write SQL" , I get the output as below :

    Select ProductID, SUM(Qty) As EEE From (Select ProductID From
    D:\Sites\Pos\BRecord.mdb.BRecord where Year = 2009 And Month = 6 And BID > 0
    LEFT JOIN Name On D:\Sites\Pos\BRecord2004.mdb.BRecord.ProductID =
    D:\Sites\Pos\Goods.mdb.Goods.ProductID) Group By ProductID Order By
    ProductID

    The only thing I need is to catch the name from Goods.mdb to the left side
    of ProductID in Record.mdb, but the difficult is they are two different
    databases.

    Thansk for yr response very much !


    "Bob Barrows" <> ¼¶¼g©ó¶l¥ó·s»D:...
    > Mary wrote:
    >> I have two databases and want to combine the data like below:

    >
    > Type and version of database please? It is almost always relevant.
    >
    >>
    >> BRecord.mdb.BRecord

    >
    > OK, I think I see that this is Access (Jet) but you should start by
    > telling us this, instead of making us guess.
    >
    >> ProductID Qty Year Month
    >> A101 20 2009 6
    >> A102 30 2009 6
    >>
    >> Goods.mdb.Goods
    >> ProductID Name
    >> A101 Flower
    >> A102 Dog
    >>
    >> I want to get the result as below
    >> ProductID Qty Name
    >> A101 20 Flower
    >> A102 30 Dog

    >
    > This appears to be a simple join to me. Why do you use the word "LEFT" in
    > your subject?
    >
    > The only problem I see is that these are different database files. You
    > will not be able to perform a sql join between them given that they are in
    > different databases, unless one of the databases has a link to the table
    > in the other database.
    >>
    >> I try the following clause but failed,

    >
    > Please describe your symptoms without using the words "didn't work", or
    > "failed", or "no joy". Did you receive an error message? Incorrect result?
    > Did your computer crash? Did the world end? :)
    >>
    >> Goods = Server.MapPath("Goods.mdb")
    >> BRecord = Server.MapPath("BRecord2004.mdb")
    >>
    >> SQL = "Select ProductID, SUM(Qty) As EEE From ("
    >> SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where
    >> Year = 2009 And Month = 6"
    >> SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID =
    >> " & Goods & ".Goods.ProductID"
    >> SQL = SQL & ") Group By ProductID Order By ProductID"
    >>

    >
    > Well, this seems very wrong-headed (sorry). A Join statement requires two
    > database tables on either side:
    > table1 join table2
    > You have a table and what appears to be a field. Please show us the result
    > of :
    > Response.Write SQL
    >
    > If you have created a sql statement correctly, you should be able to open
    > your database file in Access, create a new query, switch it to sql view,
    > paste in the sql statement from the browser window and run it without
    > modification.
    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
     
    Mary, Jun 19, 2009
    #3
  4. Mary wrote on Fri, 19 Jun 2009 05:37:48 +0800:

    > I have two databases and want to combine the data like below:


    > BRecord.mdb.BRecord
    > ProductID Qty Year Month
    > A101 20 2009 6
    > A102 30 2009 6


    > Goods.mdb.Goods
    > ProductID Name
    > A101 Flower
    > A102 Dog


    > I want to get the result as below
    > ProductID Qty Name
    > A101 20 Flower
    > A102 30 Dog


    > I try the following clause but failed,


    > Goods = Server.MapPath("Goods.mdb")
    > BRecord = Server.MapPath("BRecord2004.mdb")


    > SQL = "Select ProductID, SUM(Qty) As EEE From ("
    > SQL = SQL & "Select ProductID From " & BRecord & ".BRecord Where Year
    > = 2009 And Month = 6"
    > SQL = SQL & " LEFT JOIN Name On " & BRecord & ".BRecord.ProductID = "
    > & Goods & ".Goods.ProductID"
    > SQL = SQL & ") Group By ProductID Order By ProductID"


    > Can anyone help me ? Thanks !



    Your SQL is invalid - you're using Name in the JOIN but then joining using
    Goods, and your JOIN should be before the WHERE. You also don't need the
    subquery. And if every ProductID has a matching row in Goods then you don't
    use a LEFT join, you use an INNER. I think this will work, but I haven't
    tested it.

    Select b.ProductID, SUM(b.Qty) as Qty, g.Name
    From " & BRecord & ".BRecord as b
    INNER JOIN " & Goods & ".Goods as g On b.ProductID = g.ProductID
    Where b.Year = 2009 And b.Month = 6
    Group By b.ProductID, g.Name
    Order By b.ProductID

    It's a lot simpler to use a table alias than keep using the full mdb path
    which is why the "as b" and "as g" are in there. If you do need a LEFT JOIN
    because there are BRecord rows that don't have a matching Goods row then
    just change INNER JOIN to LEFT JOIN, but be aware that the Name column in
    the results will be NULL where there is no matching Goods row.

    --
    Dan
     
    Daniel Crichton, Jun 19, 2009
    #4
  5. Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:

    > The only problem I see is that these are different database files. You
    > will not be able to perform a sql join between them given that they
    > are in different databases, unless one of the databases has a link to
    > the table in the other database.


    Bob, something I realised when testing my response to an earlier post Mary
    made is that it is possible :)

    Jet allows the use of the full path to the mdb file along with the table
    name in that file as the table identifier in queries. For instance,

    SELECT * FROM c:\database\mydata.mdb.Products

    will return all rows from the Products table in the c:\database\mydata.mdb
    file.

    I never realised this was possible myself until recently. It certainly makes
    it simpler than using linked tables, especially in a hosted environment
    where the path to the external mdb file may change (site moved to another
    drive/server without notice).

    --
    Dan
     
    Daniel Crichton, Jun 19, 2009
    #5
  6. Mary

    Bob Barrows Guest

    Daniel Crichton wrote:
    > Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:
    >
    >> The only problem I see is that these are different database files.
    >> You will not be able to perform a sql join between them given that
    >> they are in different databases, unless one of the databases has a link
    >> to the table in the other database.

    >
    > Bob, something I realised when testing my response to an earlier post
    > Mary made is that it is possible :)
    >
    > Jet allows the use of the full path to the mdb file along with the
    > table name in that file as the table identifier in queries. For
    > instance,
    > SELECT * FROM c:\database\mydata.mdb.Products
    >
    > will return all rows from the Products table in the
    > c:\database\mydata.mdb file.


    Really? I knew about using the IN operator to get to an external database
    but I have never seen this syntax.

    >
    > I never realised this was possible myself until recently. It
    > certainly makes it simpler than using linked tables, especially in a
    > hosted environment where the path to the external mdb file may change
    > (site moved to another drive/server without notice).


    Have you gotten it to work, Daniel? Maybe I'll take a few minutes and give
    it a try myself this morning.

    Mary, if this syntax does indeed work via ADO, and assuming your connection
    string is pointing at Goods.mdb, your statement should become:

    Select ProductID, SUM(Qty) As EEE From
    D:\Sites\Pos\BRecord.mdb.BRecord as r
    LEFT JOIN Goods as g On r.ProductID =
    g.ProductID
    where Year = 2009 And Month = 6 And BID > 0
    Group By ProductID
    Order By ProductID

    And you really, really should accustom yourself to testing your queries _in
    Access_ before attempting to create them in an external application. If the
    query does not work in Access, there is no hope that it will work when
    executed from ASP.

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #6
  7. Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:

    > Daniel Crichton wrote:
    >> Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:


    >>> The only problem I see is that these are different database files.
    >>> You will not be able to perform a sql join between them given that
    >>> they are in different databases, unless one of the databases has a
    >>> link to the table in the other database.


    >> Bob, something I realised when testing my response to an earlier post
    >> Mary made is that it is possible :)


    >> Jet allows the use of the full path to the mdb file along with the
    >> table name in that file as the table identifier in queries. For
    >> instance,
    >> SELECT * FROM c:\database\mydata.mdb.Products


    >> will return all rows from the Products table in the
    >> c:\database\mydata.mdb file.


    > Really? I knew about using the IN operator to get to an external
    > database but I have never seen this syntax.


    As I said, until Mary's first post title "Select INTO, UNION" I'd never seen
    it before either, but I ran some tests with some Access databases here and
    was amazed to find it worked. I was going to reply to that saying that IN
    should be used but this syntax is actually somewhat easier to read. I'm
    guessing that the mdb full path will need to be enclosed in [] if it
    contains a space, but I didn't test that aspect.

    >> I never realised this was possible myself until recently. It
    >> certainly makes it simpler than using linked tables, especially in a
    >> hosted environment where the path to the external mdb file may change
    >> (site moved to another drive/server without notice).


    > Have you gotten it to work, Daniel? Maybe I'll take a few minutes and
    > give it a try myself this morning.


    Yes, quite a few times. I even tested it on a live ASP site that uses an
    Access database (it's waiting to be migrated to PHP and MySQL, and doesn't
    have access to SQL Server).

    > Mary, if this syntax does indeed work via ADO, and assuming your
    > connection string is pointing at Goods.mdb, your statement should
    > become:


    > Select ProductID, SUM(Qty) As EEE From
    > D:\Sites\Pos\BRecord.mdb.BRecord as r
    > LEFT JOIN Goods as g On r.ProductID =
    > g.ProductID where Year = 2009 And Month = 6 And BID > 0
    > Group By ProductID
    > Order By ProductID


    As ProductID occurs in both r and g, will this work? I can't remember if
    Access requires a column that appears in mutiple tables to be qualified with
    the table name/alias, but SQL Server certainly does (which is where I do
    most of my development).

    > And you really, really should accustom yourself to testing your queries
    > _in Access_ before attempting to create them in an external application.
    > If
    > the query does not work in Access, there is no hope that it will work
    > when executed from ASP.


    I totally agree with this suggestion. It's by far the easiest way to
    prototype and debug SQL for Access.

    --
    Dan
     
    Daniel Crichton, Jun 19, 2009
    #7
  8. Mary

    Bob Barrows Guest

    Mary wrote:
    > Dear Bob,
    >
    > The version of two databases is "Microsoft.Jet.OLEDB.4.0"


    No, that is the name of the provider being used to connect to one of them.
    What version of Access was used to create them?

    >
    > Yes, I am newbie in using "Join", especially join on two different
    > database. Since I search through the internet, most users join two tables
    > within one database, it seems very simple, unfortunately I can't find a
    > sample
    > that join two tables in two different databases, thus I copy some clauses
    > to modify but can't make the way, this my problems.
    >
    > I am using frontpage 2003 to write the program, and back to IE to see
    > the result . Each time if the SQL statement failed, the browser will only
    > tell me "Here needs an object", no hints what going wrong. That make me
    > crazy .


    That's because you are not creating and testing your sql statement _in
    Access_ using the query builder. This is extremely important to your future
    sanity :)

    > Fine, by using "Response.write SQL" , I get the output as below :
    >
    > Select ProductID, SUM(Qty) As EEE From (Select ProductID From
    > D:\Sites\Pos\BRecord.mdb.BRecord where Year = 2009 And Month = 6 And
    > BID > 0 LEFT JOIN Name On D:\Sites\Pos\BRecord2004.mdb.BRecord.ProductID =
    > D:\Sites\Pos\Goods.mdb.Goods.ProductID) Group By ProductID Order By
    > ProductID
    >
    > The only thing I need is to catch the name from Goods.mdb to the left
    > side of ProductID in Record.mdb, but the difficult is they are two
    > different databases.
    >


    See my other responses in reply to Daniel's message.

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #8
  9. Mary

    Bob Barrows Guest

    Daniel Crichton wrote:
    > Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:
    >
    >> Daniel Crichton wrote:
    >>> Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:

    >
    >>>> The only problem I see is that these are different database files.
    >>>> You will not be able to perform a sql join between them given that
    >>>> they are in different databases, unless one of the databases has a
    >>>> link to the table in the other database.

    >
    >>> Bob, something I realised when testing my response to an earlier
    >>> post Mary made is that it is possible :)

    >
    >>> Jet allows the use of the full path to the mdb file along with the
    >>> table name in that file as the table identifier in queries. For
    >>> instance,
    >>> SELECT * FROM c:\database\mydata.mdb.Products

    >
    >>> will return all rows from the Products table in the
    >>> c:\database\mydata.mdb file.

    >
    >> Really? I knew about using the IN operator to get to an external
    >> database but I have never seen this syntax.

    >
    > As I said, until Mary's first post title "Select INTO, UNION" I'd
    > never seen it before either, but I ran some tests with some Access
    > databases here and was amazed to find it worked. I was going to reply
    > to that saying that IN should be used but this syntax is actually
    > somewhat easier to read. I'm guessing that the mdb full path will
    > need to be enclosed in [] if it contains a space, but I didn't test
    > that aspect.


    I'm having trouble getting it to work.
    >
    >>> I never realised this was possible myself until recently. It
    >>> certainly makes it simpler than using linked tables, especially in a
    >>> hosted environment where the path to the external mdb file may
    >>> change (site moved to another drive/server without notice).

    >
    >> Have you gotten it to work, Daniel? Maybe I'll take a few minutes and
    >> give it a try myself this morning.

    >
    > Yes, quite a few times. I even tested it on a live ASP site that uses
    > an Access database (it's waiting to be migrated to PHP and MySQL, and
    > doesn't have access to SQL Server).
    >
    >> Mary, if this syntax does indeed work via ADO, and assuming your
    >> connection string is pointing at Goods.mdb, your statement should
    >> become:

    >
    >> Select ProductID, SUM(Qty) As EEE From
    >> D:\Sites\Pos\BRecord.mdb.BRecord as r
    >> LEFT JOIN Goods as g On r.ProductID =
    >> g.ProductID where Year = 2009 And Month = 6 And BID > 0
    >> Group By ProductID
    >> Order By ProductID

    >
    > As ProductID occurs in both r and g, will this work?



    Duh! Of course not! the ProductID field has to be explictly qualified using
    the alias.

    Select r.ProductID, SUM(Qty) As EEE From
    D:\Sites\Pos\BRecord.mdb.BRecord as r
    LEFT JOIN Goods as g On r.ProductID =
    g.ProductID
    where Year = 2009 And Month = 6 And BID > 0
    Group By r.ProductID
    Order By r.ProductID

    Again, the importance of testing in the database environment becomes
    evident.

    And Mary, I'm still puzzled why you think you need a LEFT join. Using your
    sample data, an INNER join will produce the same results:
    Select r.ProductID, SUM(Qty) As EEE From
    D:\Sites\Pos\BRecord.mdb.BRecord as r
    JOIN Goods as g On r.ProductID =
    g.ProductID
    where Year = 2009 And Month = 6 And BID > 0
    Group By r.ProductID
    Order By r.ProductID


    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #9
  10. Mary

    Bob Barrows Guest

    Daniel Crichton wrote:
    > Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:
    >
    >> The only problem I see is that these are different database files.
    >> You will not be able to perform a sql join between them given that
    >> they are in different databases, unless one of the databases has a link
    >> to the table in the other database.

    >
    > Bob, something I realised when testing my response to an earlier post
    > Mary made is that it is possible :)
    >
    > Jet allows the use of the full path to the mdb file along with the
    > table name in that file as the table identifier in queries. For
    > instance,
    > SELECT * FROM c:\database\mydata.mdb.Products
    >
    > will return all rows from the Products table in the
    > c:\database\mydata.mdb file.
    >
    > I never realised this was possible myself until recently. It
    > certainly makes it simpler than using linked tables, especially in a
    > hosted environment where the path to the external mdb file may change
    > (site moved to another drive/server without notice).


    Your suggested syntax did not work. I had to resort to using the IN clause,
    like this:

    SELECT * from stations in "C:\Docume~1\Bob\My Documents\mdb_files\db1.mdb"

    Testing this in a vbscript file using this code was successful:

    dim cn,rs
    set cn=createobject("adodb.connection")
    cn.open "provider=microsoft.jet.oledb.4.0;" & _
    "data source=C:\Docume~1\Bob\MyDocu~1\mdb_files\db3.mdb"
    sql="SELECT * from stations in " & _
    """C:\Docume~1\Bob\MyDocu~1\mdb_files\db1.mdb"""
    set rs=cn.execute( ,1)
    msgbox rs.getstring

    So there is a good possibility that this will work in ASP given that
    permissions are set correctly.
    Note: Mary, the quotation marks must be "escaped" by doubling them when
    using them as literals in te string you are building. This is one of the
    reasons I strongly recommend using saved queries in your database instead of
    dynamic sql.

    From Access online help:
    Notes
    For improved performance and ease of use, use a linked table instead of IN.



    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #10
  11. Mary

    Bob Barrows Guest

    Daniel Crichton wrote:
    > Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:
    >
    >> Daniel Crichton wrote:
    >>> Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:

    >
    >> Have you gotten it to work, Daniel? Maybe I'll take a few minutes and
    >> give it a try myself this morning.

    >
    > Yes, quite a few times. I even tested it on a live ASP site that uses
    > an Access database (it's waiting to be migrated to PHP and MySQL, and
    > doesn't have access to SQL Server).
    >

    OK, I found the secret:
    SELECT *
    FROM [C:\Docume~1\Bob\MyDocu~1\mdb_files\db1.mdb].stations

    The path to the database _file_ needs to be enclosed in brackets. Not the
    entire table expression.

    Hmm, online help gives no clue that this is possible ...

    Mary, assuming you are bent on using dynamic sql, and assuming you don't
    really need a LEFT join, your code should become:

    SQL="Select r.ProductID, SUM(Qty) As EEE,g.[Name] From " & _
    "[D:\Sites\Pos\BRecord.mdb].BRecord as r " & _
    "JOIN Goods as g On r.ProductID =g.ProductID " & _
    "where Year = 2009 And Month = 6 And BID > 0 " & _
    "Group By r.ProductID,g.[Name] " & _
    "Order By r.ProductID"

    If your sample data is incomplete, and you really do need a left join, just
    modify the above accordingly.

    Wait ... your sample data contains only one record per ProductID - why are
    you grouping and using SUM? Is your sample data incomplete?

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #11
  12. Bob wrote on Fri, 19 Jun 2009 07:40:33 -0400:

    > Daniel Crichton wrote:
    >> Bob wrote on Fri, 19 Jun 2009 06:54:41 -0400:


    >>> Daniel Crichton wrote:
    >>>> Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:


    >>> Have you gotten it to work, Daniel? Maybe I'll take a few minutes
    >>> and give it a try myself this morning.


    >> Yes, quite a few times. I even tested it on a live ASP site that uses
    >> an Access database (it's waiting to be migrated to PHP and MySQL, and
    >> doesn't have access to SQL Server).


    > OK, I found the secret:
    > SELECT *
    > FROM [C:\Docume~1\Bob\MyDocu~1\mdb_files\db1.mdb].stations


    > The path to the database _file_ needs to be enclosed in brackets. Not
    > the entire table expression.


    So far all my tests have been without spaces, so I've just left the [] out,
    eg.

    select * from e:\mo\data\mo.mdb.products

    which gives me all the rows from the products table in my external database
    :)

    > Hmm, online help gives no clue that this is possible ...


    It was only by trying Mary's SQL that I found out it was possible. I wonder
    where she found it ...

    --
    Dan
     
    Daniel Crichton, Jun 19, 2009
    #12
  13. Bob wrote on Fri, 19 Jun 2009 07:30:10 -0400:

    > Daniel Crichton wrote:
    >> Bob wrote on Thu, 18 Jun 2009 19:41:12 -0400:


    > From Access online help:
    > Notes
    > For improved performance and ease of use, use a linked table instead of
    > IN.


    I'm guessing that's because Access stores the table definition in the local
    mdb file for a linked table and so will assume it's correct and won't have
    to determine the structure first. Of course, if like me during prototyping
    the structure changes often linked tables can be a pain if you forget to
    relink ...

    For most applications I'd assume that the performance impact will be pretty
    small - if it becomes a problem then it'll likely be worth moving to SQL
    Server Express instead anyway.

    --
    Dan
     
    Daniel Crichton, Jun 19, 2009
    #13
  14. Mary

    Mary Guest

    I think my life has got a great leap today, thanks for Daniel and Bob .

    I totally sucess using "JOIN" and "UNION" across two different databases,
    now I get the result very easy and also, very fast.

    I would like to give publicity to these statements, to those who hope to
    understand how to make a clause across two different database.

    JOIN
    Goods = Server.MapPath("Goods.mdb")
    BRecord = Server.MapPath("BRecord.mdb")
    SQL = "Select b.ProductID, SUM(b.Qty) as Qty, g.Name From " & BRecord &
    ".BRecord as b"
    SQL = SQL & " INNER JOIN " & Goods & ".Goods as g On b.ProductID =
    g.ProductID"
    SQL = SQL & " Where b.Year = 2009 And b.Month = 6"
    SQL = SQL & " Group By b.ProductID, g.Name"
    SQL = SQL & " Order By b.ProductID"
    Set rs = GetMdbRecordset( "BRecord.mdb" , SQL)

    By using "Response.write SQL" return the following statement :
    Select b.ProductID, SUM(b.Qty) as Qty, g.Name From
    D:\Sites\Pos\BRecord.mdb.BRecord as b INNER JOIN
    D:\Sites\Pos\Goods.mdb.Goods as g On b.ProductID = g.ProductID Where b.Year
    = 2009 And b.Month = 6 Group By b.ProductID, g.Name Order By b.ProductID

    >I have two databases and want to combine the data like below:
    >
    > BRecord.mdb.BRecord
    > ProductID Qty Year Month
    > A101 20 2009 6
    > A102 30 2009 6
    >
    > Goods.mdb.Goods
    > ProductID Name
    > A101 Flower
    > A102 Dog
    >
    > I want to get the result as below
    > ProductID Qty Name
    > A101 20 Flower
    > A102 30 Dog


    ----------------------------------------------------------
    UNION

    DMonth = 2009
    DYear = 6

    SAC = Server.MapPath("MDSAC.mdb")
    Hope = Server.MapPath("MDHope.mdb")
    Unhcr = Server.MapPath("MDUnhcr.mdb")

    DIM SQL, rs
    Set objconn = GetMdbConnection( "Total.mdb") ' This mdb is no use, no table
    inside, however must exist
    SQL = "Select FNo, FName, SUM(DAmount) As EEE From ("
    SQL = SQL & "Select FNo, FName, DAmount From " & SAC & ".MData Where DYear
    = " & DYear & " And DMonth = " & DMonth & " And IsNull(Void) = True"
    SQL = SQL & " UNION ALL "
    SQL = SQL & "Select FNo, FName, DAmount From " & Hope & ".MData Where DYear
    = " & DYear & " And DMonth = " & DMonth & " And IsNull(Void) = True"
    SQL = SQL & " UNION ALL "
    SQL = SQL & "Select FNo, FName, DAmount From " & Unhcr & ".MData Where DYear
    = " & DYear & " And DMonth = " & DMonth & " And IsNull(Void) = True"
    SQL = SQL & ") Group By FNo, FName Order By SUM(DAmount) desc"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open SQL, objConn
    While Not rs.EOF
    .....rs("FNo") & "-" & rs("FName") & "-" & rs("EEE")
    rs.MoveNext
    Wend

    By using "Response.write SQL" return the following statement :

    Select FNo, FName, SUM(DAmount) As EEE From (Select FNo, FName, DAmount From
    D:\Sites\fund\MDSAC.mdb.MData Where DYear = 2009 And DMonth = 6 And
    IsNull(Void) = True UNION ALL Select FNo, FName, DAmount From
    D:\Sites\fund\MDHope.mdb.MData Where DYear = 2009 And DMonth = 6 And
    IsNull(Void) = True UNION ALL Select FNo, FName, DAmount From
    D:\Sites\fund\MDUnhcr.mdb.MData Where DYear = 2009 And DMonth = 6 And
    IsNull(Void) = True) Group By FNo, FName Order By SUM(DAmount) desc

    Hope this help to other people !!
     
    Mary, Jun 19, 2009
    #14
  15. Mary

    Bob Barrows Guest

    Mary wrote:
    > Goods = Server.MapPath("Goods.mdb")
    > BRecord = Server.MapPath("BRecord.mdb")
    > SQL = "Select b.ProductID, SUM(b.Qty) as Qty, g.Name From " & BRecord
    > & ".BRecord as b"
    > SQL = SQL & " INNER JOIN " & Goods & ".Goods as g On b.ProductID =
    > g.ProductID"
    > SQL = SQL & " Where b.Year = 2009 And b.Month = 6"
    > SQL = SQL & " Group By b.ProductID, g.Name"
    > SQL = SQL & " Order By b.ProductID"
    > Set rs = GetMdbRecordset( "BRecord.mdb" , SQL)
    >


    I totally neglected to mention the reserved keywords you (or the database
    developer) are using for your fieldnames. "Name", "Year" and "Month" are all
    reserved keywords ("Name" is a very common property name in VBA, and Year()
    and Month() are the names of VBA functions) and, even if they are not
    causing you grief at the moment. it is almost guaranteed that someday you
    will run into a very hard-to-debug problem caused by their use. You can
    mitigate the danger by always remembering to surround them with brackets ("
    .... g.[Name] ... b.[Year] ... b.[Month] ...) in your sql statements, but the
    best course by far is to give them more meaningful names to avoid using
    reserved keywords. For example, what does that Name field contain? The name
    of the product? Wouldn't it make more sense to call it "ProductName"? Two
    problems solved in a single stroke: no more reserved keyword, and no more
    need to explain to someone what the field contains. I would guess that
    YearOfSale and MonthOfSale would be the appropriate names of the other
    fields ... or not ... maybe they contain the year and month that the record
    was entered ...

    Here is a list of reserved keywords to avoid:

    http://www.aspfaq.com/show.asp?id=2080

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Jun 19, 2009
    #15
  16. Mary

    Mary Guest

    Thanks for you reminder. In fact, I am using BMonth and BYear in my clause.

    "Bob Barrows" <> ¼¶¼g©ó¶l¥ó·s»D:...
    > Mary wrote:
    >> Goods = Server.MapPath("Goods.mdb")
    >> BRecord = Server.MapPath("BRecord.mdb")
    >> SQL = "Select b.ProductID, SUM(b.Qty) as Qty, g.Name From " & BRecord
    >> & ".BRecord as b"
    >> SQL = SQL & " INNER JOIN " & Goods & ".Goods as g On b.ProductID =
    >> g.ProductID"
    >> SQL = SQL & " Where b.Year = 2009 And b.Month = 6"
    >> SQL = SQL & " Group By b.ProductID, g.Name"
    >> SQL = SQL & " Order By b.ProductID"
    >> Set rs = GetMdbRecordset( "BRecord.mdb" , SQL)
    >>

    >
    > I totally neglected to mention the reserved keywords you (or the database
    > developer) are using for your fieldnames. "Name", "Year" and "Month" are
    > all reserved keywords ("Name" is a very common property name in VBA, and
    > Year() and Month() are the names of VBA functions) and, even if they are
    > not causing you grief at the moment. it is almost guaranteed that someday
    > you will run into a very hard-to-debug problem caused by their use. You
    > can mitigate the danger by always remembering to surround them with
    > brackets (" ... g.[Name] ... b.[Year] ... b.[Month] ...) in your sql
    > statements, but the best course by far is to give them more meaningful
    > names to avoid using reserved keywords. For example, what does that Name
    > field contain? The name of the product? Wouldn't it make more sense to
    > call it "ProductName"? Two problems solved in a single stroke: no more
    > reserved keyword, and no more need to explain to someone what the field
    > contains. I would guess that YearOfSale and MonthOfSale would be the
    > appropriate names of the other fields ... or not ... maybe they contain
    > the year and month that the record was entered ...
    >
    > Here is a list of reserved keywords to avoid:
    >
    > http://www.aspfaq.com/show.asp?id=2080
    >
    > --
    > Microsoft MVP - ASP/ASP.NET - 2004-2007
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
     
    Mary, Jun 19, 2009
    #16
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alan Silver
    Replies:
    0
    Views:
    917
    Alan Silver
    Jun 5, 2006
  2. googleboy
    Replies:
    1
    Views:
    956
    Benji York
    Oct 1, 2005
  3. John Thomas

    LINQ left outer join

    John Thomas, Sep 1, 2009, in forum: ASP .Net
    Replies:
    1
    Views:
    891
    Patrice
    Sep 1, 2009
  4. Jeff Uchtman

    HELP! Left, Right, Inner Join

    Jeff Uchtman, Apr 7, 2004, in forum: ASP General
    Replies:
    0
    Views:
    152
    Jeff Uchtman
    Apr 7, 2004
  5. Jeff Uchtman

    2nd try, left join

    Jeff Uchtman, Apr 8, 2004, in forum: ASP General
    Replies:
    11
    Views:
    260
    Jeff Uchtman
    Apr 12, 2004
Loading...

Share This Page