CODEWITHSUNDEEP
sqlms-accessjoinleft-join
user5792975
user5792975

Reputation: 19

SQL Join Incorrectly

I'm attempting to select values from the databases pictured below so that I may insert into a new table called Desired Table. Data and debugging is in a Microsoft Access database and I continue to receive the error:

syntax error in query expression.

What is wrong with this query? The joins seem correct and so does the FROM clause. Please let me know if you need more information. Don't worry about the INSERT clause.

Query:

SELECT vicdescriptions.vid, 
       vicdescriptions.make, 
       vicdescriptions.vic_year, 
       vicdescriptions.optiontable, 
       vacdescriptions.accessory, 
       vacvalues.value, 
       vicvalues.valuetype, 
       vicvalues.value 
FROM   vicdescriptions 
       JOIN vicvalues 
         ON ( vicdescriptions.vic_make = vicvalues.vic_make 
              AND vicdescriptions.vic_year = vicvalues.vic_year );

Database Structure:

DATABASE SCHEMA

Desired table for insertion:

enter image description here

Upvotes: 1

Views: 93

Answers (5)

prenesh u
prenesh u

Reputation: 122

SELECT VicDescriptions.VID, 
       VicDescriptions.Make, 
       VicDescriptions.VIC_Year, 
       VicDescriptions.OptionTable, 
       VacDescriptions.accessory, 
       VacValues.value, 
       VacValues.valuetype, 
       VacValues.value --(No such table as VicValues available in the database, you only have VacValues)
FROM   VicDescriptions 
       JOIN VacValues --(No such table available in the database, you only have VacValues) 
         ON ( VicDescriptions.VIC_Make = VacValues.VIC_Make 
              AND VicDescriptions.VIC_Year = VacValues.VIC_Year )
       JOIN VacDescriptions
         ON ( VacDescriptions.Period = VacValues.Period
               AND VacDescriptions.VAC = VacValues.VAC);

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Access does not support JOIN as a synonym for INNER JOIN. You must always specify the type of JOIN:

FROM   vicdescriptions 
       INNER JOIN vicvalues 
         ON ( vicdescriptions.vic_make = vicvalues.vic_make 
              AND vicdescriptions.vic_year = vicvalues.vic_year )

If there is not a table named vicvalues, Access will give you a different error message after you have changed JOIN to INNER JOIN.

Upvotes: 4

Dave Barker
Dave Barker

Reputation: 6447

Is it a typo for vacdescriptions and vacvalues?

SELECT vicdescriptions.vid, 
       vicdescriptions.make, 
       vicdescriptions.vic_year, 
       vicdescriptions.optiontable, 
       **vicdescriptions.accessory, 
       vicvalues.value,** 
       vicvalues.valuetype, 
       vicvalues.value 
FROM   vicdescriptions 
       JOIN vicvalues 
         ON ( vicdescriptions.vic_make = vicvalues.vic_make 
              AND vicdescriptions.vic_year = vicvalues.vic_year );

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

I guess you should use parenthesis only when you join more than two tables in Ms-Access plus it should start before first table name. Try removing it.

.....
FROM   vicdescriptions 
       JOIN vicvalues 
         ON vicdescriptions.vic_make = vicvalues.vic_make 
             AND vicdescriptions.vic_year = vicvalues.vic_year

Upvotes: 1

eass
eass

Reputation: 11

SELECT vicdescriptions.vid, 
       vicdescriptions.make, 
       vicdescriptions.vic_year, 
       vicdescriptions.optiontable, 
       vacdescriptions.accessory, 
       vacvalues.value, 
       vicvalues.valuetype, 
       vicvalues.value 
FROM   vicdescriptions 
       JOIN vicvalues 
         ON ( vicdescriptions.vic_make = vicvalues.vic_make 
              AND vicdescriptions.vic_year = vicvalues.vic_year );

I guess line 5, 6 vacdescriptions, vacvalues >> vicdescriptions, vicvalues

Upvotes: 1

Related Questions