If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
HOWTO: Use conditions in FROM clause in Access sintax.
Hello all,
I am triying to use condition wtih some fields in the FROM clause of an Access query. But I get an error saying that "the conbination expresion is not valid". I know that this kind of sintax is valid in SQL Server but I ignore if there is another valid way to write this condition in the FROM clause. Does anybody know how to do it? I am using 2 simple tables donde in Access. Table1 and Table2. Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are related to the first 2 fields in Table2. I need to make a LEFT JOIN to get all records in Table1 but only the record that matches the condition in Table2. This is the query I'm triying to execute. SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3'); This are the data I am using. Table1 Ref RefTwo ref1 ref11 ref2 ref21 ref3 ref31 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 ref8 ref81 ref9 ref91 Table2 Ref RefExt RefTwo Data 1 ref2 ref21 data1 2 ref2 ref21 data2 3 ref3 ref31 data3 4 ref4 ref41 data4 5 ref5 ref51 data5 6 ref5 ref51 data6 7 ref5 ref51 data7 I need to get this result: Only teh record in Table2 that matches the condition. The rest are not in the result because are NULL. Result RefExt RefTwo Data ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 This is the result I get in SQL Server if I execute that query. Is it possible to do it in Access with the conditions in FROM? TIA, Nekane. |
#2
|
|||
|
|||
NKN wrote:
Hello all, I am triying to use condition wtih some fields in the FROM clause of an Access query. But I get an error saying that "the conbination expresion is not valid". I know that this kind of sintax is valid in SQL Server but I ignore if there is another valid way to write this condition in the FROM clause. Does anybody know how to do it? I am using 2 simple tables donde in Access. Table1 and Table2. Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are related to the first 2 fields in Table2. I need to make a LEFT JOIN to get all records in Table1 but only the record that matches the condition in Table2. This is the query I'm triying to execute. SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3'); This are the data I am using. Table1 Ref RefTwo ref1 ref11 ref2 ref21 ref3 ref31 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 ref8 ref81 ref9 ref91 Table2 Ref RefExt RefTwo Data 1 ref2 ref21 data1 2 ref2 ref21 data2 3 ref3 ref31 data3 4 ref4 ref41 data4 5 ref5 ref51 data5 6 ref5 ref51 data6 7 ref5 ref51 data7 I need to get this result: Only teh record in Table2 that matches the condition. The rest are not in the result because are NULL. Result RefExt RefTwo Data ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 This is the result I get in SQL Server if I execute that query. Is it possible to do it in Access with the conditions in FROM? Like this: SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) WHERE (Table2.Data = 'data3') -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) |
#3
|
|||
|
|||
Hi,
Thanks for your quick answer but I am afraid that the select you have wrote doesn't give the same results as the one that I wrote with the condition of Table2.Data='data3' in FROM clause. If you execute them in SQL Server I mean. If you execute your select with the condition in the WHERE clause you only get one row, that woth Table2.Data = 'data3' . But it is a LEFT OUTER JOIN and that means that I need all rows in Table1 and only matching one in Table2, so I would get 7 rows. 6 of them with null in Table2.Data field and another one with data3. This is the result I get in SQL Server, but I can't in Access because of some kind of syntactic problem. Any ideas? Thank you again, Nekane. "MGFoster" escribió en el mensaje ink.net... NKN wrote: Hello all, I am triying to use condition wtih some fields in the FROM clause of an Access query. But I get an error saying that "the conbination expresion is not valid". I know that this kind of sintax is valid in SQL Server but I ignore if there is another valid way to write this condition in the FROM clause. Does anybody know how to do it? I am using 2 simple tables donde in Access. Table1 and Table2. Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are related to the first 2 fields in Table2. I need to make a LEFT JOIN to get all records in Table1 but only the record that matches the condition in Table2. This is the query I'm triying to execute. SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3'); This are the data I am using. Table1 Ref RefTwo ref1 ref11 ref2 ref21 ref3 ref31 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 ref8 ref81 ref9 ref91 Table2 Ref RefExt RefTwo Data 1 ref2 ref21 data1 2 ref2 ref21 data2 3 ref3 ref31 data3 4 ref4 ref41 data4 5 ref5 ref51 data5 6 ref5 ref51 data6 7 ref5 ref51 data7 I need to get this result: Only teh record in Table2 that matches the condition. The rest are not in the result because are NULL. Result RefExt RefTwo Data ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 This is the result I get in SQL Server if I execute that query. Is it possible to do it in Access with the conditions in FROM? Like this: SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) WHERE (Table2.Data = 'data3') -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) |
#4
|
|||
|
|||
NKN wrote:
Hi, Thanks for your quick answer but I am afraid that the select you have wrote doesn't give the same results as the one that I wrote with the condition of Table2.Data='data3' in FROM clause. If you execute them in SQL Server I mean. If you execute your select with the condition in the WHERE clause you only get one row, that woth Table2.Data = 'data3' . But it is a LEFT OUTER JOIN and that means that I need all rows in Table1 and only matching one in Table2, so I would get 7 rows. 6 of them with null in Table2.Data field and another one with data3. This is the result I get in SQL Server, but I can't in Access because of some kind of syntactic problem. There's more than just a syntactic problem (see below). [...] This is the query I'm triying to execute. SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3'); This are the data I am using. Table1 Ref RefTwo ref1 ref11 ref2 ref21 ref3 ref31 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 ref8 ref81 ref9 ref91 Table2 Ref RefExt RefTwo Data 1 ref2 ref21 data1 2 ref2 ref21 data2 3 ref3 ref31 data3 4 ref4 ref41 data4 5 ref5 ref51 data5 6 ref5 ref51 data6 7 ref5 ref51 data7 I need to get this result: Only teh record in Table2 that matches the condition. The rest are not in the result because are NULL. Result RefExt RefTwo Data ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 This is the result I get in SQL Server if I execute that query. Is it possible to do it in Access with the conditions in FROM? Like this: SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) WHERE (Table2.Data = 'data3') -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) I don't see why you get that result in SQL Server, because to me it just looks wrong. The way you have your query set up, there is NO value in Table2 that matches either of the fields in the first record of Table1, and there are no fields in any other records in either table which match either of those values. So my guess is that one of your files got corrupted. You might try running the query again on SQL Server. Attempting to develop a query that would come close to what you say you got, I wrote one to transform the [Table2].[Ref] values to match the [Table1].[Ref] values and called it [Q_Table2]; it looks like this: SELECT 'ref' & [Table2]![Ref] AS Ref, Table2.RefTwo FROM Table2; I can then INNER JOIN this to [Table1] and [Table2] as follows: SELECT Table1.Ref, Q_Table2.RefTwo, Table2.Data FROM (Q_Table2 INNER JOIN Table1 ON Q_Table2.Ref = Table1.Ref) INNER JOIN Table2 ON Q_Table2.RefTwo = Table2.RefTwo WHERE ((Table2.Data)='data3') UNION SELECT DISTINCT Table1.Ref, Q_Table2.RefTwo, Null AS Data1 FROM (Q_Table2 INNER JOIN Table1 ON Q_Table2.Ref = Table1.Ref) INNER JOIN Table2 ON Q_Table2.RefTwo = Table2.RefTwo WHERE ((Table2.Data)'data3') ORDER BY Table1.Ref; Even with all this finagling (technical term), the result isn't what you say SQL Server gave you; the result of this query was the following, in which the last 2 records didn't match yours: Ref RefTwo Data ---- ------ ----- ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref51 ref7 ref51 This is about as close as I could come to the spirit of what you say you did, without doing something like defining a new table containing your values and doing a "SELECT * FROM [Table]", which I figure would come under the category of cheating (not to mention that it would violate Occam's Razor). -- Vincent Johns Please feel free to quote anything I say here. |
#5
|
|||
|
|||
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 You have to run my query in Access. I know it doesn't work in SQL Server. The microsoft.public.sqlserver.programming newsgroup showed me your OUTER JOIN example, when I asked them why my workable Access query didn't run in SQL Server. Therefore, run the example I gave on Access, only. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQyCeI4echKqOuFEgEQIVKACgsQpOMMSzTNRlU48q0D+4vV MI/AkAn2U1 bzjaMbUNbGHeuodA/Vr14wMB =KDf0 -----END PGP SIGNATURE----- NKN wrote: Hi, Thanks for your quick answer but I am afraid that the select you have wrote doesn't give the same results as the one that I wrote with the condition of Table2.Data='data3' in FROM clause. If you execute them in SQL Server I mean. If you execute your select with the condition in the WHERE clause you only get one row, that woth Table2.Data = 'data3' . But it is a LEFT OUTER JOIN and that means that I need all rows in Table1 and only matching one in Table2, so I would get 7 rows. 6 of them with null in Table2.Data field and another one with data3. This is the result I get in SQL Server, but I can't in Access because of some kind of syntactic problem. Any ideas? Thank you again, Nekane. "MGFoster" escribió en el mensaje ink.net... NKN wrote: Hello all, I am triying to use condition wtih some fields in the FROM clause of an Access query. But I get an error saying that "the conbination expresion is not valid". I know that this kind of sintax is valid in SQL Server but I ignore if there is another valid way to write this condition in the FROM clause. Does anybody know how to do it? I am using 2 simple tables donde in Access. Table1 and Table2. Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are related to the first 2 fields in Table2. I need to make a LEFT JOIN to get all records in Table1 but only the record that matches the condition in Table2. This is the query I'm triying to execute. SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3'); This are the data I am using. Table1 Ref RefTwo ref1 ref11 ref2 ref21 ref3 ref31 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 ref8 ref81 ref9 ref91 Table2 Ref RefExt RefTwo Data 1 ref2 ref21 data1 2 ref2 ref21 data2 3 ref3 ref31 data3 4 ref4 ref41 data4 5 ref5 ref51 data5 6 ref5 ref51 data6 7 ref5 ref51 data7 I need to get this result: Only teh record in Table2 that matches the condition. The rest are not in the result because are NULL. Result RefExt RefTwo Data ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 This is the result I get in SQL Server if I execute that query. Is it possible to do it in Access with the conditions in FROM? Like this: SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) WHERE (Table2.Data = 'data3') -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) |
#6
|
|||
|
|||
How about?
SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) WHERE (Table2.Data = 'data3' OR Table2.Data Is Null) MGFoster wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 You have to run my query in Access. I know it doesn't work in SQL Server. The microsoft.public.sqlserver.programming newsgroup showed me your OUTER JOIN example, when I asked them why my workable Access query didn't run in SQL Server. Therefore, run the example I gave on Access, only. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBQyCeI4echKqOuFEgEQIVKACgsQpOMMSzTNRlU48q0D+4vV MI/AkAn2U1 bzjaMbUNbGHeuodA/Vr14wMB =KDf0 -----END PGP SIGNATURE----- NKN wrote: Hi, Thanks for your quick answer but I am afraid that the select you have wrote doesn't give the same results as the one that I wrote with the condition of Table2.Data='data3' in FROM clause. If you execute them in SQL Server I mean. If you execute your select with the condition in the WHERE clause you only get one row, that woth Table2.Data = 'data3' . But it is a LEFT OUTER JOIN and that means that I need all rows in Table1 and only matching one in Table2, so I would get 7 rows. 6 of them with null in Table2.Data field and another one with data3. This is the result I get in SQL Server, but I can't in Access because of some kind of syntactic problem. Any ideas? Thank you again, Nekane. "MGFoster" escribió en el mensaje ink.net... NKN wrote: Hello all, I am triying to use condition wtih some fields in the FROM clause of an Access query. But I get an error saying that "the conbination expresion is not valid". I know that this kind of sintax is valid in SQL Server but I ignore if there is another valid way to write this condition in the FROM clause. Does anybody know how to do it? I am using 2 simple tables donde in Access. Table1 and Table2. Table1 has 2 fields. Table2 has 3 fields. The 2 fields in Table1 are related to the first 2 fields in Table2. I need to make a LEFT JOIN to get all records in Table1 but only the record that matches the condition in Table2. This is the query I'm triying to execute. SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT OUTER JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) AND (Table2.Data = 'data3'); This are the data I am using. Table1 Ref RefTwo ref1 ref11 ref2 ref21 ref3 ref31 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 ref8 ref81 ref9 ref91 Table2 Ref RefExt RefTwo Data 1 ref2 ref21 data1 2 ref2 ref21 data2 3 ref3 ref31 data3 4 ref4 ref41 data4 5 ref5 ref51 data5 6 ref5 ref51 data6 7 ref5 ref51 data7 I need to get this result: Only teh record in Table2 that matches the condition. The rest are not in the result because are NULL. Result RefExt RefTwo Data ref1 ref21 ref2 ref21 ref3 ref31 data3 ref4 ref41 ref5 ref51 ref6 ref61 ref7 ref71 This is the result I get in SQL Server if I execute that query. Is it possible to do it in Access with the conditions in FROM? Like this: SELECT Table1.Ref, Table1.RefTwo, Table2.Data FROM Table1 LEFT JOIN Table2 ON (Table1.RefTwo= Table2.RefTwo) AND (Table1.Ref = Table2.RefExt) WHERE (Table2.Data = 'data3') -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Access or Visual Studio? | Jerome | General Discussion | 61 | April 7th, 2005 07:14 PM |
Access Error Message when opening database | eah | General Discussion | 3 | January 26th, 2005 10:04 AM |
Hiding Access | Mark A. Sam | Using Forms | 4 | December 1st, 2004 09:09 PM |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
MICROSOFT INVESTING HEAVILY IN ACCESS | Mike Painter | General Discussion | 39 | October 15th, 2004 03:56 PM |