Join Expression Not Supported
Mar 8, 2007
Anyone can help me with this problem
In the following code
Dim qMain As ADODB.Recordset
Set qMain = New ADODB.Recordset
qMain.ActiveConnection = CurrentProject.Connection
qMain.CursorType = adOpenStatic
Dim strSql As String
strSql = "select MainTable!LotNo, MainTable!RollNo, FilmTest!RollNo, FilmTest!Av_56, FilmTest!Av_112,FilmTest!TestDate,FilmTest!TestTim e" + _
" from MainTable LEFT JOIN FilmTest ON (MainTable!RollNo = FilmTest!RollNo) "
qMain.Open (strSql)
When excuted, the error was Join expression not supported
View Replies
ADVERTISEMENT
Nov 10, 2006
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
View 7 Replies
View Related
Dec 18, 2013
I am using vb.Net to connect to an access db. In access 2010 this query works fine, but in vb.Net, it errors with 'JOIN expression not supported'.
Code:
sql = "SELECT Boxes.Box, Boxes.CustRef, Boxes.Customer " &
"FROM (Requests INNER JOIN [Request Boxes] ON Requests.[Request no] = [Request Boxes].[Request no]) INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box" &
"WHERE (Requests.[Request no]) = '" & item & "' " &
"AND ((Boxes.Customer) = '" & customer2 & "'))"
View 3 Replies
View Related
Feb 4, 2015
I have 5 different tables that are all linked with foreign keys. When I made my fifth table and linked it to my primary key like I had with the other tables I got the Join expression not supported error.
View 14 Replies
View Related
Jul 1, 2015
I am trying to execute the query below with multiple left joins because of the data I am trying to get back. The weird thing is sometimes it work and then sometimes it gives me a join expression error. It seems that access strangely removes brackets around the ON clauses. However even when I put those brackets back in this query it isn't working. why this query isn't running or why the brackets disappear in Access 2010.
SELECT Patients.[First Name] & " " & Patients.[Surname] AS Fullname
, [Clinic Patient].[MYMOP ID]
, NZ(MYMOPs.[MYMOP Date], [Clinic Patient].[First Appointment]) AS [MYMOP1 Date]
, MYMOPs.Completed AS [MYMOP1 Completed]
[code]....
View 14 Replies
View Related
Aug 15, 2005
I don't quite understand this error message, and therefore cannot begin to fix it. One of my forms will not open except in design view because of a "Type Mismatch in JOIN expression" error. Can someone please tell me where to look to fix it? Thanks.
View 5 Replies
View Related
Apr 2, 2013
Using Access 2010 and ODBC connections to pull data from 3 sources (SQL Server 2008, a customized Documentum application, and Windows AD accounts). Problem is user_login_name is sometimes recorded as lowercase, sometimes as propercase, and sometimes as uppercase depending on which table or source being used. For example, lowercase would be "abcd123", propercase would be "Abcd123", and uppercase would be "ABCD123". I thought I could just wrap each join comparison in the From stmt in the query(s) with the UCase() function, but Access doesn't like that.
How can I convert or cast the various user_login_names within the FROM join statement to be the same text case so joins will pull correct data?
View 1 Replies
View Related
Apr 26, 2006
Hi, got a slight problem. As part of an ASP website I'm trying to insert into two linked tables in an access database using SQL. Standard SQL syntax woiuld be:
INSERT ALL INTO tblParent(name, Address) Values ('Dave','Home')
INTO tblChild(age, phone) Values ('35','1234567');
Thing is, does Access SQL support the INSERT ALL INTO statement? I keep getting syntax errors and I can only guess it might be this. if so, how do you make multiple table inserts into Access through SQL?
Cheers, Lol
View 2 Replies
View Related
Sep 8, 2006
Any one know what this means?
[Microsoft][ODBC Microsoft Access Driver] Selected collating sequence not supported by the operating system
To give you an idea of whats goin on... I took an excel file used xls converter and made it into a access database... I uploaded the database to the server and this is the error I got in dreamweaver... So I tried to used the MySQL Migration Tool Kit to make this a MySQL database and THis is the error I got when I was trying to execute the bulk transfer... Any Ideas? Please?
View 1 Replies
View Related
Sep 4, 2014
the databases functionally normally with no issue. I have several databases one for each project and they are all having this issue. It doubles the size of the DB after the failure. This seems to tell me that the crash is just after the DB is ready to rewrite the database compacted. I run C&R about every 2 weeks and have a spread sheet tracking DB size prior to and after C&R. The compactions run from about 1% to 15% the norm seams to be 4% to 6%. The size of the DBs run from 65KB to 100KB.
View 1 Replies
View Related
Nov 16, 2013
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
View 6 Replies
View Related
Jan 19, 2006
I have an application that uses Oracle 8.i remotely. The application selects tables from Oracle, opens them, and ultimately uses the selected open table to create a temporary Access table on a PC's hard drive; the user can then manipulate the Access data locally and then save the data back to the remote Oracle database.
This works great on several PCs where I work, however one PC is giving me fits. When I attempt to select an Oracle table to create the local Access table, I get the following message:
"Run-time error '-2147467259 (80004005)': Data type is not supported"
We are using Jet Providers internally and are using Jet 4.0. The PC causing fits is using Access 2003.
Might the problem be caused by incompatibility between Access 2003 and Jet 4.0? Has anyone else encountered this issue? I tried searching this forum and also the Microsoft web site, but did not have luck. This is driving me slightly more nuts.
Thanks in advance to all who reply!
View 2 Replies
View Related
Jan 3, 2007
I am trying to do an inner join with a left join. The only problem is, I want to inner join the table that is being joined. This is how I thought it would work below, but it doesn't work.
SELECT * FROM ((Submissions LEFT JOIN Candidates ON Submissions.`Candidate Code` = Candidates.`Candidate Code`) INNER JOIN `Type Candidate Status` ON Candidate.Status = `Type Candidate Status`.`Status ID`)
WHERE Submissions.Status <> 7 ORDER BY `School Interest` DESC;
I want to get the Candidate.Status to inner join with the `Type Candidate Status`.`Status ID`.
If you can help, thanks in advance.
Dave
View 1 Replies
View Related
Jun 8, 2005
Good day all
Heres what im trying to do:
in one textfield i have =Count(*)
this returns all the records in my db.
now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31
please help
lee
View 1 Replies
View Related
Nov 27, 2007
I have two queries containing this information (example):
View 3 Replies
View Related
Jul 21, 2005
I having a little trouble with a query I was hoping someone could help me with. Basically I have the following SQL statement
[code].....
I would like to show all records for the tblCService.CService field however it won't let me change the join to a RIGHT JOIN. I get an "ambigious outer joins" error message. As far as schema tblEList is the main table and the tblCService is a lookup table. Any ideas?
View 1 Replies
View Related
Aug 23, 2006
hello everyone,Please can someone tell me if it is possible to create a Join on two tables where the value from 1 field is contained within a field from the other table.eg.table1.field = "xyz"table2.field = "the xyz super thingy"This is the SQL it gave me orrigionally but this is an "= to" join which is not what i want. I tried replacing the = with a LIKE but am still getting the same results.SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno = table2.name;SELECT table1.*, table2.*FROM table1 INNER JOIN table2 ON table1.modelno LIKE table2.name;
View 1 Replies
View Related
Sep 10, 2006
I have a database with PCs and installed hardware. An external program scans all PCs, the result of the scan can be saved as Access file.
Now I need a query to select from each PC the installed hardware (Monitor, Printer...) but only with the latest date. I have written a query MaxDatum to select the the lastest date. Here's my query code:
SELECT [Table1.needed fields], [Table2.neede fields]...
FROM (((Table1 INNER JOIN (Table2 INNER JOIN qryMaxDatum ON (Table2.Field1 = qryMaxDatum.Field1) AND (Table2.Field2 = qryMaxDatum.MDate)) ON Telle1.Field1 = Telle2.Field1)
INNER JOIN Table3 ON Table2.Field2 = Table3.Field2)
INNER JOIN Table4 ON Table2.Field2 = Table4.Field2)
INNER JOIN Table5 ON Table2.Field2 = Table5.Field2
ORDER BY Table1.Field1;
My problem: this query mulitply the number of data records so that I have 80 data records with exactly the same content per PC. How do I neesd to change the query to get only one data record per PC?
View 3 Replies
View Related
Sep 25, 2007
How do I only bring back the top 3 from the join in this query?
There will be multiple records in the InvoiceNotes table and many different invoices.
select invoice_no, inv_date, totamnt, projman, invoicenotes.note, invoicenotes.notedate
from invoices
left join invoicenotes on invoicenotes.invno = invoices.invoice_no
The InvoiceNotes tbale has an ID column so I can order by MAX(ID) DESC and then get top if needed
View 3 Replies
View Related
Nov 29, 2007
Not sure if I'm going in the right direction, but am stumped at this one!
I have a few tables:
tblDailyTripLog
ID; TripDate; Departing; ETD; Destination; Depot; Driver
tblDepots
ID; Name
tblDrivers
ID; Name
Now, in tblDailyTripLog.driver, the tblDriverID is stored. Same goes for Destination, Depot & Depart, the tblDepots.ID gets stored which can or will differ....
Now, when do a query, how would I show 3 different joins to the same table (ie. tblDepots) to get the name, instead of the ID for Departing, depot & Destination?
I've tried doing a Left Join and then another LEft Join, but its not working....
View 5 Replies
View Related
Jul 20, 2005
it has already 2 inner joins can i use one more inner join within.
strSQL = "SELECT distinct z.location_id,z.location, y.company_id,y.company_name,w.category_id,w.catego ry FROM (appointment_detail AS x INNER JOIN location_table AS z ON x.location_id = z.location_id) INNER JOIN company AS y ON x.company_id=y.company_id ( INNER JOIN category AS w ON
x.category_id =w.category_id)WHERE x.expiry_date >=date() ORDER BY z.location ,y.company_name"
INNER JOIN category AS w ON
x.category_id =w.category_id
View 3 Replies
View Related
Apr 17, 2007
I have 2 tables
customer
dialcodes
now I need to loop through the whole customer table and pull up all customers where the count of the following statement is more then 1
select count(geographic) as mycount from dialcodes where [phone1]like code + %
or if phone2 is not blank then the count of the following statement
select count(geographic) as mycount from dialcodes where [phone2] like code + %
I want to return all records from the customer table where the phone fields don't match the beginning codes from dialcodes.
can i do this with a join?
View 2 Replies
View Related
Feb 11, 2006
I'm trying to create a database of Publications (instruction manuals) where one publication is a modification to a parent modification. I'm storing all 'publications' in one table for better maintenance. This means that I have to use a Self-Join if I want to make one the parent, and one the child. Fundamentally this is no different than the Employee/Supervisor example I've found in many places:
http://www.databasedev.co.uk/self-join_query.html
I created a query with two copies of the same table with the 2nd one renamed for easier viewing. The query final comes up, but when I choose "ParentPub", it doesn't give me a drop down showing the other pubs so that I can choose one to be the parent.
What did I do wrong? I should be able to open up the table 'tblPubs', and get a drop down menu showing me all the pubs I can assign as a parent.
Thank you for your help,
Enrique
View 3 Replies
View Related
May 17, 2007
Hi,
I was wondering if someone can give me a possible solution to my join below:
select tableA.name
From tableA, tableB
where tableA.id IN (Select id from table c where .......) <--I would like tableB.prodId to make a join with the tableA.id's that are in this select sub query
Thanks
View 1 Replies
View Related
Feb 25, 2005
helooo...
i have 3 tables -Recipes, Ingredients and Products.
Recipes table:
RecipeID -PK
Ingredients table:
IngredientID -PK
IngreRecipeID -Foreign key to Recipes table
IngreProductID -Foreign key to Products table
Table:
ProductID -PK
how do i join them into 1 recordset? :confused:
View 1 Replies
View Related
Jul 11, 2005
HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance
View 1 Replies
View Related
