Using Right and Left Joins in Access 2000

[Rmck87]

Whenever i try to use a right and left join in Microsoft Access 2000. The access message box comes up saying. 'Join Expression Not Supported' Does this mean that i cannot use right and left joins? If so, what else can i use? Because i need to use it to connect specific tables and fields together.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[DrSimon]

You certainly can use left and right joins. Give us the SQL and maybe someone can help.

 

[Rmck87]

Ok i was running this in SQl Server, SQL Query Analyzer and it works perfectly. It joins the tables and columns exactly how i want them to. But whenever i transfer over to Microsoft Access and try running it, That error i mentioned before comes up. So, i thougth maybe i cant use right and left joins, but since you say i can. here is my SQL....


SELECT DISTINCT member.Member_ID, company.Company_Name, pm_project.Project_ID, te_period.Date_Start, te_period.Date_End, time_entry.hours_actual, te_charge_code.description
From te_period, time_sheet, member, company, pm_project right join time_entry on pm_project.pm_project_recid = time_entry.pm_project_recid, te_charge_code
Where te_period.te_period_recid = time_sheet.te_period_recid and time_sheet.time_sheet_recid = time_entry.time_sheet_recid and company.company_recid = time_entry.company_recid and member.member_recid = time_entry.member_recid and te_charge_code.te_charge_code_recid = time_entry.te_charge_code_recid and pm_project.pm_project_recid = time_entry.pm_project_recid
Union
SELECT DISTINCT member.Member_ID, company.Company_Name, pm_project.Project_ID, te_period.Date_Start, te_period.Date_End, time_entry.hours_actual, te_charge_code.description
From te_period, time_sheet, pm_project, member, company, time_entry left Join te_charge_code on time_entry.te_charge_code_recid = te_charge_code.te_charge_code_recid
Where te_period.te_period_recid = time_sheet.te_period_recid and time_sheet.time_sheet_recid = time_entry.time_sheet_recid and company.company_recid = time_entry.company_recid and pm_project.pm_project_recid = time_entry.pm_project_recid and member.member_recid = time_entry.member_recid





This joins first two tables, coming up with nulls for a specific table, then joins two other tables, doing the same, and unions the two joins together. Thanks for the help.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[DrSimon]

Rmck87
How exactly are you trying to use this SQL? There are certain circumstances when Access has a maximum length of 256 characters.

 

[Rmck87]

Well, i want to bring up all of the records that will show the member_ID, and Date_Start, and Date_end, Regardless. But, it will only show the company_name, and Project_id whenever there is a time_entry that equals the time_entry, for that member and date. Then it will only show the Description, whenever there is a time_entry that equals the time_entry for that member and date. Meaning it should bring up stuff like this....

member.member_id company.company_name pm_project.project_id
-------------------------------------------------------------------
name coname project
name null null
name coname project
name coname project
name null null
name coname project


and continuing the query....

te_period.date_start te_period.date_end te_charge_code.description
------------------------------------------------------------------------
sdate edate null
sdate edate description
sdate edate null
sdate edate null
sdate edate description
sdate edate null



so whenever description is null, project and company have values, and vice versa..... I hope you get the idea!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

And if i have gone over my limit of characters wouldn't it say that? or would it say something about 'Joins' ?

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[DrSimon]

It's OK Rmck87, I wasn't asking what the SQL call does, I was asking Access module etc you were using it in. i.e. Macro, VBA routine, Query ...

 

[Rmck87]

OH, i was running it in Query.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

I am sorry i misunderstood you.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

Well, im using it in Query right now but im just trying to get it to work, then i am going to transfer it over into a VBA Routine, to run from a form.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[DrSimon]

Did you paste your code into the SQL window of Query? I tried that and got the same response as you.
To make this work you need to ensure that the tables either exist in the database or that there is a link.

 

[Rmck87]

yes i pasted it, and i made sure that the tables exist, and i know that there is a link, because the same code works in SQL Query Analyzer.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[DrSimon]

It's interesting. When I removed all but time_entry from the phrase "te_period, time_sheet, pm_project, member, company, time_entry" before LEFT JOIN, Access stopped complaining about the join and just told me that the input table didn't exist.
I must admit that I don't normally do complicated joins like yours, but I suspect that Acces syntax may differ. How about trying to use the query tools provided by access in the design view to start building up the query and then check the syntax of the SQL script it creates.

 

[Rmck87]

I could try that. I have done that for most of it, but i havent quite done it for the joins that i am trying. But i will go for it. Doesn't hurt either way.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[Rmck87]

I fixed it, i took out all of the joins, and finished it off in my where clause it works perfectly now. Thanks for all the help DrSimon!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.