| Author |
 Topic  |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
 Posted - 03 December 2003 : 12:24:07
|
Coming from a SQL Server background and now working with MS Access, I'm having a bit of trouble using multiple joins in my SQL statements:
SELECT Model, CategoryName, ManfName, Details
FROM tblCars
INNER JOIN tblCategory on tblCategory.CategoryID = tblCars.CategoryID
INNER JOIN tblCarManfs on tblCarManfs.CarManfID = tblCars.CarManfID
ORDER BY Model asc
Can I not do this with MS Access? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - ruirib on 03 December 2003 12:34:36 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 December 2003 : 12:35:53
|
Just use parenthesis:
SELECT Model, CategoryName, ManfName, Details
FROM (( tblCars
INNER JOIN tblCategory on tblCategory.CategoryID = tblCars.CategoryID)
INNER JOIN tblCarManfs on tblCarManfs.CarManfID = tblCars.CarManfID)
ORDER BY Model asc
|
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 03 December 2003 12:38:43 |
 |
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 03 December 2003 : 12:42:01
|
Another easy method is to do it through the Access GUI andthen view the SQL it creates. I remember going back to that while I was learing sql(as if one ever stops  ) |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 03 December 2003 : 13:40:07
|
| Excellent. thanks guys. I'll give it a go. Hopefully the transition between the two sets of syntax will be nice and smooth. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 December 2003 : 13:46:41
|
quote:
Originally posted by D3mon
Excellent. thanks guys. I'll give it a go. Hopefully the transition between the two sets of syntax will be nice and smooth.
Most things are similar, there isn't such a big difference. Anyway, use snaayk's suggestion, it's a good way to quickly learn SQL in its Access variant. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 03 December 2003 : 16:37:30
|
One other thing, can I use Queries in the same way as Stored Procedures like:
strSQL = "All Car Details"
Where All Car Details is the name of a query designed in the Access GUI? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 04 December 2003 : 04:15:53
|
I'm using code like this:
strSQL = "AllCarDetails"
Set RS = DB.Execute(strSQL)
How to add the adCmdStoredProc part to that? (Oddly, it does seem to work without it!)
At the moment, I don't have the ADO constants file included. |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
Edited by - D3mon on 04 December 2003 04:19:09 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
Posted - 04 December 2003 : 06:17:33
|
Info on devGuru suggests this to be correct:
Const adCmdStoredProc = &H0004
strSQL = "qryAllCars"
Set objRS = objDB.Execute(strSQL, ,adCmdStoredProc)
can anyone confirm? |
Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won" |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
| |
Topic |
|
Topic Locked
