ms access sql much harder than tsql

[oaishm]

Actually, I'm a mysql transplant. I'm forced to use ms access because I can't join two tables from different datasets in ssrs.
I went through the query building function and got this:

I turned it into something realistic for humans to consume, and got:

SELECT *
FROM (dbo_GL00100 INNER JOIN dbo_GL30000 ON dbo_GL00100.ACTINDX = dbo_GL30000.ACTINDX) INNER JOIN dbo_AcctTransactions ON dbo_GL30000.ORDOCNUM = dbo_AcctTransactions.DocumentNumber;

which, of course no human can program. I changed it into something reasonable like:

SELECT *
FROM
dbo_GL30000
left JOIN dbo_GL00100 ON dbo_GL30000.ACTINDX = dbo_GL00100.ACTINDX
left JOIN dbo_AcctTransactions ON dbo_GL30000.ORDOCNUM = dbo_AcctTransactions.DocumentNumber;

now access won't accept it and says there's an error.
Do I really have to nest each join an a parenthesis, never mind that there isn't an alias for each parenthesis

 

[boblarson]

Yes, for joins you do need the parens.

 

[oaishm]

Wow, how do you guys use this application professionally. I'm joining 10 different tables with joins such as

left join larry on case larry.field when x then y when z then p when q then r else t end = harry.field

except that access uses switch

also, when I link excel with access, it insists on selecting the datatype itself rather then letting me select it. I have a field I want to come in as an integer, but access insists on bringing it in as double. How can I change it?

 

[Norie]

oaishm

Isn't SRSS SQL Server rather than MySQL?

Or is it that you are trying to do something in SQL Server/SRSS via Access that you can do in MySQL?

Sorry if that's a stupid question.

 

[xenou]

also, when I link excel with access, it insists on selecting the datatype itself rather then letting me select it. I have a field I want to come in as an integer, but access insists on bringing it in as double. How can I change it?

Access is weakened by Excel when it comes to data typing. Prefer not to link to spreadsheets - import the data, or use Access as the primary data source.

You shouldn't expect to push Access as hard as MySQL or SQLServer. Nor can you. If you instead take time to discover it's potential (and learn to avoid its pitfalls) it is a very useful and reliable application, but it's not for everything. Although as far as "Wow, how do you guys use this application professionally" I'm rather tempted to say the same about your SRSS, which apparently can't handle a join on two tables/databases (or whatever you mean by "datasets").

 

[james_lankford]

I agree that all the parenthesis suck

it is SO much cleaner to write "real" sql

and of course having real stored procedures, triggers, rules and everything else is great too

 

[SydneyGeek]

True. But Access is not intended to replace server-class databases. I also dislike the parentheses, lack of comments, and a bunch of other useful features. But there are workarounds for a lot of these.

I use VBA fairly extensively with SQL; both to write dynamic queries and to control execution.
I tend to write SQL only when I need something SQL-specific, such as a subquery. Because of that I ignore the parentheses; they don't get in the way if you aren't trying to write SQL from scratch.
For pass-through queries written against SQL Server, you can use the SQL Server syntax as long as you update the table references appropriately (I use Replace in NotePad).
And, when you move to 2010 you will find that the Data Macro functionality gives table-level triggers to Access for the first time.

It's not perfect. But it's not just a toy either.

Denis

 

[oaishm]

Don't get me started on SSRS or sql server report service. I'm new to that too, I like MySQL, but that's another matter.

Anyway, SSRS can't join two tables from two different datasets, each dataset being a connection. Theoretically, you can use Analysis services to build a cube and join the data that way, but alas, the data I want to join is a simple table that I put into Excel so anyone can easily edit it. So I went to access which can join an excel sheet with a link from mssql. Access CAN take two different tables from two different data sources with two different datatypes and join them. However, the sql is crazy. I don't understand how you would write dynamic multi parameter sql strings with all those parenthesis. For instance in phpmysql and I imagine vb with mssql you can do

foreach ($x as $y){
$sql .='left join '.$x['table'].' on '.$x['id'].' = '.$y['y_id']
}
or some such if you named keys in a consistent manner

How would you do this is access? But yes, SRSS is no peach either. The SSMS query analyzer from 2008 is WORSE than the access query tool from access 2000. How does that happen? Don't the two group so of programmers eat at the same cafeteria? The more expensive tool has the worse query analyzer. Amazing. Well, I've got to find yet another solution.

 

[xenou]

The closest thing in VBA to a php associative array is a dictionary object available in the scripting library. So the analogous construction to your php would be (or might be) something like this:

[COLOR="Navy"]Sub[/COLOR] foo()

[COLOR="Navy"]Dim[/COLOR] d [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a
[COLOR="Navy"]Dim[/COLOR] s(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="Navy"]Set[/COLOR] d = CreateObject("Scripting.Dictionary")

d.Add "Table1", "Table2" [COLOR="SeaGreen"]'//parent table, child table[/COLOR]
d.Add "Table2", "Table3"
d.Add "Table3", "Table4"

a = d.Keys

s(0) = s(0) & " SELECT X,Y,Z FROM " [COLOR="SeaGreen"]'//Field List[/COLOR]
s(0) = s(0) & String(d.Count, "(") [COLOR="SeaGreen"]'//Initial Parentheses[/COLOR]
s(0) = s(0) & a(0) [COLOR="SeaGreen"]'//First Table in Dictionary ("associative array")[/COLOR]

[COLOR="SeaGreen"]'//add joins based on remaining tables in the dictionary[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(a)
    s(1) = s(1) & vbNewLine & " LEFT JOIN " & d.Item(a(i)) & " ON " & a(i) & ".[ID] = " & d.Item(a(i)) & ".[ID])"
[COLOR="Navy"]Next[/COLOR] i

[COLOR="SeaGreen"]'//Final SQL string[/COLOR]
s(0) = s(0) & s(1)

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Output:

 SELECT X,Y,Z FROM (((Table1
 LEFT JOIN Table2 ON Table1.[ID] = Table2.[ID])
 LEFT JOIN Table3 ON Table2.[ID] = Table3.[ID])
 LEFT JOIN Table4 ON Table3.[ID] = Table4.[ID])

If you are working with SRSS you might find access to the .NET library more efficient. They may even have PHP plugins for all I know. And I would suggest you seek a forum for SRSS rather than an Access forum ... I may be wrong (and probably am) but using Access to mediate between SRSS and Excel, all to create a table that users can interact with, seems like a long way around a problem that must have a more direct solution in an SRSS environment. In the meantime, don't kill yourself by assuming that Access will do the same for you as PHP/MySQL. There are too many differences between an enterprise class server platform and a file-based desktop platform. Access's sql engine is not as robust nor are its relational database management services as sophisticated (note, however, that you can also front-end Access as a GUI interface to other databases with the back end services you need).

Just my two cents. Personally I like PHP/MySQL quite a lot but I don't have the pleasure of working with it regularly. So I'm well versed in the ups and downs of Access, and I am able to harness the ups to do quite a lot in my work. I'm afraid you'll probably find VBA string handling and array handling quite pathetic if you come from PHP - so I'm warning you now in case you reach another point where you want to explode because you realize it takes you 10 lines of self-written code to do something that PHP would handle with a single inbuilt function.

 

[xenou]

As a variation on a theme, I thought this morning that the Ansi-89 syntax would also do here (no parentheses needed). This time I used parallel 1-D arrays rather than a dictionary. But as I said, VBA string handling and array processing isn't the cat's pajamas.

[COLOR="Navy"]Sub[/COLOR] bar()
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a(2), b(2)
[COLOR="Navy"]Dim[/COLOR] s(1) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

[COLOR="SeaGreen"]'------------------------------[/COLOR]
a(0) = "Table1" [COLOR="SeaGreen"]'//parent table[/COLOR]
b(0) = "Table2" [COLOR="SeaGreen"]'//child table[/COLOR]

a(1) = "Table2"
b(1) = "Table3"

a(2) = "Table3"
b(2) = "Table4"
[COLOR="SeaGreen"]'------------------------------[/COLOR]

[COLOR="SeaGreen"]'//SQL Part 1[/COLOR]
s(0) = s(0) & "SELECT X,Y,Z " & _
    vbNewLine & "FROM " & _
    vbNewLine & Join(a, ", ") & _
    vbNewLine & "WHERE (1=1)"

[COLOR="SeaGreen"]'//SQL Part 2[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(a)
    s(1) = s(1) & vbNewLine & "AND " & a(i) & ".[ID] = " & b(i) & ".[ID] "
[COLOR="Navy"]Next[/COLOR] i

[COLOR="SeaGreen"]'//Final SQL string[/COLOR]
s(0) = s(0) & s(1)

[COLOR="SeaGreen"]'//View result[/COLOR]
[COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] s(0)

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Output:

SELECT X,Y,Z 
FROM 
Table1, Table2, Table3
WHERE (1=1)
AND Table1.[ID] = Table2.[ID] 
AND Table2.[ID] = Table3.[ID] 
AND Table3.[ID] = Table4.[ID]