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

WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?

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

Queries :: JOIN Expression Not Supported

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

Join Expression Not Supported Error

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

Queries :: Access 2010 / Join Expression Not Supported

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

Type Mismatch In JOIN Expression

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

Queries :: How To Convert Text Case To Compare In Join Expression

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

INSERT ALL INTO - Supported Or Not?

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

Selected Collating Sequence Not Supported By The Operating System

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

Selected Collating Sequence Not Supported With Specified File Format

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

Create A JOIN Of Different Tables Called Join A Variable And List

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

"Data Type Not Supported" Plight

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

Inner Join And Left Join

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

Writing An Expression In The Expression Builder

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

Join

Nov 27, 2007

I have two queries containing this information (example):

View 3 Replies View Related

Right Join?

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

Join Where Contains ?????

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

Need Help With INNER JOIN

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

Only Get Top 3 From The Join

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

Join Twice???

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

Can I Use One More Join

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

Join Help

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

Self-join Not 100% Working - Please Help

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

Complex Join

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

How To Join 3 Tables?

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

Join Tables

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







Copyrights 2005-15 www.BigResource.com, All rights reserved