One To Many Relationship

[connerlowen]

HI,

I have a very basic and general question. I have a database that has some one-to-many relationships. Is it possible for me to have no related record in the many side?

I have a table "NewQuotationT" that is linked to table "NewQuotationPartT" and that table is linked to table "NewMetalT". I have many queries that take the data entered through forms to do calculations. These work fine for when there is a part and at least one metal. There are some instances where a part will not have a metal. My Queries are not doing any calculations with these records and I cannot figure out why. If there is a problem in my queries I will work to fix them and optimize all of it, however if this is not even possible I do not want to waste my time.

Thanks,

Conner Owen

 

[plog]

Yes its possible. To accomodate this in your queries you use a LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp).

Basically, in the Design View of Access, you right click on the line joining your datasources, select the Join Properties option and then check the box beside the option that gives you all records from the One side of the relationship.

 

[connerlowen]

I will try this and let you know how it turns out. Thanks so much for your help.

 

[Minty]

It sounds like the joins in the queries are not set up correctly. You would need to post up queries or a stripped down version of the DB with the queries and sample data in to offer nay further advice.

 

[connerlowen]

In my first query I tried it on this solution worked fine, however in the next one I received an error "The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs that first join and then include that in your SQL statement." I am going to investigate this and try to come up with a solution.

 

[plog]

Sounds like your JOINS aren't kosher--you can't mix them down the line. Let's say you have 3 tables (A, B & C) and let's represent your JOINS with these symbols - (INNER JOIN) and -> (LEFT JOIN).

Your initial query looked like this:

A-B-C

But then you made a LEFT JOIN between A & B:

A->B-C

That won't work, because anything downstream of that LEFT JOIN needs to be a LEFT JOIN as well. From B to C needs to be a LEFT JOIN:

A->B->C


So, wherever you made a LEFT JOIN, you need to go back and make sure all downstream joins are LEFT JOINS as well.

 

[connerlowen]

I am learning Queries and currently all of my calculations are working properly. I fixed the problem previously mentioned.