Why are my table joins not sticking while designing this select query?

[ShovelinFishHeads]

Hi everyone,

I'm having some trouble with a select query. I want it to function like a "products", or an "orders" query. So I have relationships between the "subject table" and other tables like Company and Employee and States and Status and ShipVia.

I'm pretty sure I have the tables designed right and the relationships are all correct. That all looks good except when I design the query, the relationship "joins" don't stay "One to Many" for three or four of the tables in the query when using the design tool after saving the query. The "join lines" just go all simple like.

What the heck could be causing that? While all of the joins are fine under Relationships? Please save me loads of time and explain this.

 

[jdraw]

Need more info. Show us a jpg of your relationships window.
Show us the query SQL.
Did you try some simple queries? --like just 1 table? Then 2....

Post a copy of your database. zip format

 

[sneuberg]

One thing that will make them simple is no primary or unique key in the parent table.

 

[ShovelinFishHeads]

Thanks for the input, guys.

I did try to make the query work with two tables and then three and so on. The same set of tables caused the same problems regardless of "complexity" while the subject table and the Company and Employee tables worked well together.

The fields that I am joining on in these tables were copies of fields that I have been using in other parts of this project and they functioned perfectly while part of other tables.

I also did a compact and repair but that had no effect.

over the next couple of hours I will have to go to work and will have to put this project aside. I will try to get to this later tho.

thanks again

 

[ShovelinFishHeads]

ok just tried an experiment by putting six of the tables involved in the problem into a new db by themselves. After putting the relationship joins together, I tied to replicate my query. Saved the query and then reopened it. The join lines for the two State tables went weak and simple on me. The sql is:

SELECT
FROM Status INNER JOIN ((Companies INNER JOIN Employees ON Companies.BrokerID = Employees.BrokerID) INNER JOIN ([Origination States] INNER JOIN ([Destination States] INNER JOIN [Service Description] ON [Destination States].[Destination StateID] = [Service Description].[Destination StateID]) ON [Origination States].[Origination StateID] = [Service Description].[Origination StateID]) ON Companies.BrokerID = [Service Description].BrokerID) ON Status.StatusID = [Service Description].StatusID;

Access informed me that there is a reserved word or an argument name misspelled, or missing, or the punctuation is incorrect.

I don't see anything wrong here. What am I missing? Need another set of eyes on this.

 

[jdraw]

SELECT
FROM STATUS
INNER JOIN (
	(
		Companies INNER JOIN Employees ON Companies.BrokerID = Employees.BrokerID
		) INNER JOIN (
		[Origination States] INNER JOIN (
			[Destination States] INNER JOIN [Service Description] ON [Destination States].[Destination StateID] = [Service Description].[Destination StateID]
			) ON [Origination States].[Origination StateID] = [Service Description].[Origination StateID]
		) ON Companies.BrokerID = [Service Description].BrokerID
	) ON STATUS.StatusID = [Service Description].StatusID;

You have not identified any fields in your SELECT.

 

[ShovelinFishHeads]

ok here goes....

SELECT Employees.PersonnelID, Companies.BrokerID, [Service Description].ServiceID, [Service Description].BrokerID, [Service Description].[Gross Fee], [Service Description].Weight, [Service Description].Distance, [Service Description].[Origination Address1], [Service Description].[Origination Address2], [Service Description].[Origination City], [Service Description].[Origination StateID], [Origination States].State, [Service Description].[Origination ZIP/Postal Code], [Service Description].[Start Date], [Service Description].[Start Time Description], [Service Description].[Destination Address1], [Service Description].[Destination Address2], [Service Description].[Destination City], [Service Description].[Destination StateID], [Destination States].State, [Service Description].[Destination ZIP/Postal Code], [Service Description].[End Date], [Service Description].[End Time Description], [Service Description].StatusID, Status.Status
FROM Status INNER JOIN ((Companies INNER JOIN Employees ON Companies.BrokerID = Employees.BrokerID) INNER JOIN ([Origination States] INNER JOIN ([Destination States] INNER JOIN [Service Description] ON [Destination States].[Destination StateID] = [Service Description].[Destination StateID]) ON [Origination States].[Origination StateID] = [Service Description].[Origination StateID]) ON Companies.BrokerID = [Service Description].BrokerID) ON Status.StatusID = [Service Description].StatusID;

the join lines for the two "State" tables still do not show 1 to many. Just simple lines.

I'll try to get the time to upload this db later today or post some jpegs.

 

[sneuberg]

What happens if you try to change them to the outer join you want in in the join properties in the query designer?

 

[ShovelinFishHeads]

sneuberg,

In 2016, I have in the query design tool under Join Properties three options.

For example, the third option is 'Include ALL records from "Service Description" and only those records from 'Destination States' where the join fields are equal.

I'm guessing this is what you are suggesting?

When selecting this option, the join line becomes an arrow pointing to the Destination StateID field in the destination states table. I'm thinking that that is not what I want.

I will put some effort into getting this example db uploaded so people to get inside it and figure this out.

 

[ShovelinFishHeads]

attached one file

 

[sneuberg]

When selecting this option, the join line becomes an arrow pointing to the Destination StateID field in the destination states table. I'm thinking that that is not what I want.

Probably not. I didn't know what you meant by simple. I thought it was changing outer joins to inner joins on you but now I see that your complaint is that it's not showing the 1 and infinity sign on the join lines. I don't have an answer for you on that yet but I keep trying to figure it out. I'm pretty sure it doesn't effect the way the join functions.

I have a question. Why do you have two tables for states; Destination States and Origination States. They appear to be identical. Why not just one table?

 

[The_Doc_Man]

The distinction between the 1/infinity and the other type of visualization is whether you have enabled relational integrity.

 

[sneuberg]

The distinction between the 1/infinity and the other type of visualization is whether you have enabled relational integrity.

If you look at the database the OP uploaded you will see that relational integrity has been enable for the relations in question. The 1/infinity show up in the relationships and initially when you create a query with the tables but when you save the query and reopen it the join lines no longer have the 1/infinity.

 

[sneuberg]

This is weird but it appears the spaces in the field names is the cause of this problem. If you take out the spaces let's say change OriginationState ID to OriginationStateID in both parent and child tables the 1/infinity will stick. I'm hoping some other forum member can explain this.

In any case here's another reason not to put spaces in your field names.

 

[ShovelinFishHeads]

thanks again, sneuberg.

If taking all of the spaces out of all of the indexed fields in this project is all that I will need to do to get around this issue, than that is no big deal. Just really glad we might have our solution.

I will make "repairs" throughout the file, and then see how that helps things going foreward.

If anything else comes up regarding this, I will get back here and post about it.

I do find it rather interesting that when using v2016, the templates Microsoft publishes for 2016 have lots and lots of fields with spaces in them. well anywazz.........