Megatron-Lives
Board Regular
- Joined
- Jul 7, 2002
- Messages
- 83
Our company just got bought out by a big corporation, and they wanted to start tracking EVERYTHING in Excel.
I talked them into using a db instead, so I went out and bought the SQL Server Dev kit, a book, and went to work. Two weeks later, the db was built, the lookup tables populated, and I was starting work on the ASP forms, when word came down from the corporate big shots that we needed to be using Access instead of SQL Server. (We already had free Access 2000, and they would have had to pay to license SQL Server.)
So, I am trying to convert everything I learned about SQL Server over to Access.
I started with a fairly easy db, three little tables, and one quick query. Try as I might, I couldn't get the query to work. So I created the exact same db in SQL Server, and, sure enough, the query runs fine.
So I designed the query really crappily in the Access Wizard, then checked the SQL behind it, and noticed the code is a bit different.
Here's how I entered the query in the QA
SELECT Author, Title
FROM tblAuthors AS A
INNER JOIN tblTitleAuthor AS TA
ON A.AuthorID = TA.AuthorID
INNER JOIN tblTitles AS T
ON TA.TitleID = T.TitleID
and this is the way the Access Wizard designed it
SELECT [tblAuthors].[Author], [tblTitles].[Title]
FROM tblTitles
INNER JOIN (tblAuthors INNER JOIN tblTitleAuthor ON [tblAuthors].[AuthorID] =[tblTitleAuthor].[AuthorID]) ON [tblTitles].[TitleID] =[tblTitleAuthor].[TitleID];
WTF? Do you have to nest JOINS in access SQL?
I talked them into using a db instead, so I went out and bought the SQL Server Dev kit, a book, and went to work. Two weeks later, the db was built, the lookup tables populated, and I was starting work on the ASP forms, when word came down from the corporate big shots that we needed to be using Access instead of SQL Server. (We already had free Access 2000, and they would have had to pay to license SQL Server.)
So, I am trying to convert everything I learned about SQL Server over to Access.
I started with a fairly easy db, three little tables, and one quick query. Try as I might, I couldn't get the query to work. So I created the exact same db in SQL Server, and, sure enough, the query runs fine.
So I designed the query really crappily in the Access Wizard, then checked the SQL behind it, and noticed the code is a bit different.
Here's how I entered the query in the QA
SELECT Author, Title
FROM tblAuthors AS A
INNER JOIN tblTitleAuthor AS TA
ON A.AuthorID = TA.AuthorID
INNER JOIN tblTitles AS T
ON TA.TitleID = T.TitleID
and this is the way the Access Wizard designed it
SELECT [tblAuthors].[Author], [tblTitles].[Title]
FROM tblTitles
INNER JOIN (tblAuthors INNER JOIN tblTitleAuthor ON [tblAuthors].[AuthorID] =[tblTitleAuthor].[AuthorID]) ON [tblTitles].[TitleID] =[tblTitleAuthor].[TitleID];
WTF? Do you have to nest JOINS in access SQL?