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 wrote:
Type and version of database please? It is almost always relevant.

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

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.
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? :-)
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.

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 !


Show quoteHide quote

Mary wrote:
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?

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 :-)

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

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

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).

Daniel Crichton wrote:
Show quoteHide quoteReally? I knew about using the IN operator to get to an external database
but I have never seen this syntax.

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.

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

Show quoteHide quoteAs 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.

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).

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).

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

Daniel Crichton wrote:
Show quoteHide quoteI'm having trouble getting it to work.
Show quoteHide quoteDuh! 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

Daniel Crichton wrote:
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?

Bob wrote  on Fri, 19 Jun 2009 07:40:33 -0400:

Show quoteHide quoteSo 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
It was only by trying Mary's SQL that I found out it was possible. I wonder
where she found it ...

Daniel Crichton wrote:
Show quoteHide quoteYour 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.

Bob wrote  on Fri, 19 Jun 2009 07:30:10 -0400:

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.

Mary wrote  on Fri, 19 Jun 2009 05:37:48 +0800:

Show quoteHide quoteYour 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.

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

Show quoteHide quote----------------------------------------------------------
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 wrote:
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

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

Show quoteHide quote

How can I type in the combo box <select> ?
How to change all relative paths in a website???
What is the real IP address of the site visitor?
Pinpoint unspecified error for objXMLHttp.send objXML statement
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
ASP 0126
Select INTO, UNION
Classic ASP, <SELECT> postback, Browser Navigation
Problem with Instr to find a space
ClientScript working in IE but not in Mozilla