Well, as you requested, I will now post the query as I do it in SQL (I use MS SQL Server Management Studio) and then the one it creates on Access.
Note that since this is work related and a bit sensitive, I changed the table column names to Table# and Column#. I made sure to check, double check and triple check that I changed them correctly - still there might still be a chance of human error .
Here is the query on SQL Studio:
Code:
SELECT
Table1.Column1,
Table1.Column2,
Table1.Column3,
Table1.Column4,
Table1.Column5,
Table1.Column6,
Table2.Column2,
Table2.Column3,
Table2.Column4,
Table3.Column2,
Table4.Column2,
Table5.Column3,
Table6.Column2,
Table7.Column2
FROM
Table1
INNER JOIN Table5 ON Table1.Column7 = Table5.Column1
INNER JOIN Table8 ON Table1.Column3 = Table8.Column1 AND Table5.Column1 = Table8.Column2
INNER JOIN Table3 ON Table8.Column3 = Table3.Column1
INNER JOIN Table4 ON Table1.Column8 = Table4.Column1
INNER JOIN Table2 ON Table2.Column1 = Table5.Column2
INNER JOIN Table6 ON Table6.Column1 = Table1.Column9
INNER JOIN Table7 ON Table7.Column1 = Table2.Column1
After creating the links to Access, and also setting up the relationships between all the tables, I choose with the Query Wizard the columns and tables exactly as it appears on SQL Studio.
After this is done, I check the query as it appears on Access's SQL query:
Code:
SELECT
Table1.Column1,
Table1.Column2,
Table1.Column3,
Table1.Column4,
Table1.Column5,
Table1.Column6,
Table2.Column2,
Table2.Column3,
Table2.Column4,
Table3.Column2,
Table4.Column2,
Table5.Column3,
Table6.Column2,
Table7.Column2
FROM
Table4
INNER JOIN (((Table7 INNER JOIN Table2 ON Table7.[Column1] = Table2.[Column5]) INNER JOIN Table5 ON Table2.[Column1] = Table5.[Column2]) INNER JOIN (Table3 INNER JOIN (Table6 INNER JOIN Table1 ON Table6.[Column1] = Table1.[Column9]) ON Table3.[Column1] = Table1.[Column10]) ON Table5.[Column1] = Table1.[Column7]) ON (Table4.Column3 = Table3.Column1) AND (Table4.Column1 = Table2.Column6) AND (Table4.Column1 = Table1.Column8);
Then, when I add a filter on one for one of the columns "Table1.Column2", it changes again the "From" part:
Code:
FROM
Table4
INNER JOIN (((Table6 INNER JOIN Table2 ON Table7.[Column1] = Table2.[Column5]) INNER JOIN Table5 ON Table2.[Column1] = Table5.[Column2]) INNER JOIN (Table3 INNER JOIN (Table6 INNER JOIN Table1 ON Table6.[Column1] = Table1.[Column9]) ON Table3.[Column1] = Table1.[Column10]) ON Table5.[Column1] = Table1.[Column7]) ON (Table4.Column3 = Table3.Column1) AND (Table4.Column1 = Table2.Column6) AND (Table4.Column1 = Table1.Column8)
WHERE (((Table1.Column2)='string'));
For your reference, here are the relationships between the tables:
Code:
Table2.Column1 to Table5
Table3.Column1 to Table1, Table8, Table4
Table4.Column1 to Table1, Table2
Table5.Column1 to Table1, Table8
Table6.Column1 to Table1
Table7.Column1 to Table2
Table8.Column1 to Table1
So as you can see (hope I didn't confuse u too much ), from something that looks very simple Access messed it up. Not only that, but the filter I tried to use brings up different information, between SQL Studio and Access.
I really hope you or someone can explain why this happens.
I am been trying to figure this out, but its a long time since I have used the access query designer thingy.
I am wondering if the following syntax is causing the problem
INNER JOIN Table8 ON Table1.Column3 = Table8.Column1 AND Table5.Column1 = Table8
Open the access query designer and select SQL view and paste in the following code and see whether it sorts out the problem
FROM
Table1
INNER JOIN Table5 ON Table1.Column7 = Table5.Column1
INNER JOIN Table8 ON Table1.Column3 = Table8.Column1
INNER JOIN Table3 ON Table8.Column3 = Table3.Column1
INNER JOIN Table4 ON Table1.Column8 = Table4.Column1
INNER JOIN Table2 ON Table2.Column1 = Table5.Column2
INNER JOIN Table6 ON Table6.Column1 = Table1.Column9
INNER JOIN Table7 ON Table7.Column1 = Table2.Column1
WHERE Table5.Column1 = Table8.Column2
One question that springs to mind, is why do you want to convert this query to an access query? it will run much faster as a sql server view or stored procedure
The reason I want to do this on Access, is that many people here (including myself, as I'm going step by step here) don't know SQL. So, when I have this ready, I will be able to design forms, so people will only need to enter the information they are looking, without the need to actually write the code.
As for your response, you actually have something here. I'm working under the assumption that the code I wrote in SQL is correct and the one from Access is wrong. There's a chance that with the relationships I did on Access, that in fact that's doing the correct query and not the SQL Designer.
So, how can I know which gives the correct answer??
I received the query from someone who worked with me, and who I can't contact again.
I use it on the SQL Designer to bring up information, and was working under the assumption that it's giving me the correct info.
Now, since I want to "move" everything to Access, I've set up the relationships manually, since if I try the query without them it says I need to do it before I "join" them.
When I do the relationships and then use the query to bring up the information from the different tables, it auticatically writes in the background the SQL code - you probably know all this.
So, there might be that the relashionships are done differently between the 2 tools.
In any case, I'll try to figure it out myself, since it seems that without someone here actually having the DB itself noone will be able to help.
So thanks again for your help, and if I need anything else regarding this I'll post it here.
.
