Join expression not supported

[Bschmale]

Hello all,

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.



In searching this error online it got complicated real quick. Everyone lists vba code when discussing this issue but I can't seem to find that code. Again very new to access and I appreciate the help!

[John_G]

Where are you getting the error - in a query, in VBA that you are writing, or in the relationships screen?

In either case, please post the SQL that is causing the problem, or a screenshot of the Relationships diagram.

Either your structure is not right, or you didn't describe it accurately - it's hard to tell without seeing it.

[Bschmale]

Where are you getting the error - in a query, in VBA that you are writing, or in the relationships screen?

In either case, please post the SQL that is causing the problem, or a screenshot of the Relationships diagram.

Either your structure is not right, or you didn't describe it accurately - it's hard to tell without seeing it.

I am getting this error when opening a form. I believe it is something I am doing with my relationships. I went from having a ton of repeating data in two tables to five tables with little understanding of relationships.

[orange]

Your tables don't appear to be normalized. Many of your tables do not have Primary Keys.

It might be helpful to readers if you could describe WHAT your database is about in plain English -- no jargon, no Access.

[Bschmale]

The goal of this database is to have a form that provides a snapshot of a publicly traded company. This form will have a financial summary, operational/production summary, areas of focus as well as general comment and notes fields.

CapExProductionAnnual is not normalized. I changed it back to this format so that on the form I could have a CapEx12 CapEx13 etc listed on the form in a list.

I was under the impression that a primary key was only used in one table and then the rest of the tables have foreign keys that draw back to the primary key. This is why each table has an Id field that links to the customerinformation table.

[June7]

Are those ID fields regular number (long) type? They cannot be autonumber.

Primary key in these related tables is not an issue because they do not have related dependent tables.

[orange]

What is data type of your ID fields?

FK refers to the PK value in the associated table. But table with FK can have a PK of its own for records in that table.

Customer                             Order
CustID PK                              OrderID  PK
CustFirstName                        CustId  FK points to Customer 
CustLastName                         OrderDate

[Bschmale]

All of my ID fields (except CompanyID in customer Information) are long numbers. Do I need to have a primary key in the tables with foreign key? All of the ID fields refer to a small list of companies.

[Bschmale]

NormalizationAttempt - Copy.zip

Did you see my post #6?

If you want to provide db for analysis, follow instructions at bottom of my post.

Attached is my database. Any suggestions in general or help you guys can provide on the join expression not supported error would be great.

[June7]

A form can enter/edit data for only one table. The form RecordSource should not include all 5 tables. Build form/subforms arrangement.

[Bschmale]

Ok, thanks June. So if I need the form to display all of this data should I have sub forms (which will have to be a whole new avenue of learning) or put all of the data back into two tables? The form needs to have all of this information or it defeats the purpose which is having a quick summary of a company.

[June7]

To allow data entry/edit use form/subforms.

The CapExProductionAnnual table is still not a normalized structure.

What is the relationship type for each table? Can the 4 related tables have multiple records associated with same record in CustomerInformation - 1-to-many?

If you do want to view all 5 tables in same query, must be LEFT JOIN which is the opposite of what is set in Relationships builder:

SELECT CustomerInformation.CompanyID, CustomerInformation.CompanyName
FROM (((CustomerInformation LEFT JOIN CapExProductionAnnual ON CustomerInformation.CompanyID = CapExProductionAnnual.ID) LEFT JOIN CustomerDisposal ON CustomerInformation.CompanyID = CustomerDisposal.ID) LEFT JOIN CustomerFinancials ON CustomerInformation.CompanyID = CustomerFinancials.ID) LEFT JOIN PriorityAreas ON CustomerInformation.CompanyID = PriorityAreas.ID;

[Bschmale]

Ok, I will start doing my research about subforms.

After we talked and I started normalizing I had cap ex normalized like this:

ID	Period	CapEx	Production
1	2012	100	101
1	2013	200	201
1	2014	300	301
2	2012	400	401
2	2013	500	501
2	2014	600	601
etc	etc

The issue I had with this, is on my form under financial summary I wanted to list the capex for the previous years. When I tried to enter the "period" into the form it would only display one of those. My admittedly very limited access logic told me to put them all back to non normalized structure so that I could manipulate it how I wanted.

This join expression not supported error is keeping me from doing much with my forms. Now that I know that a form can only edit data for one table, maybe this is part of the issue.

It would be nice if I can figure this out because I don't think starting over for a third time will actually solve any of my problems.

[Bschmale]

CustomerDisposal and Priority Areas both have multiple entries for each ID.

[June7]

You may have read my previous post before I edited. Might look at again.

You should now be able to normalize the table.

https://support.office.com/en-US/Art...rs=en-US&ad=US

If CustomerFinancials will have only one record for each CustomerID and there will always be a record for each CustomerID, could just merge the two tables.

Did you see my post #6?