Cannot enter value into blank field on 'one' side of outer join

[cnstarz]

My goal is to create a form that allows me easily edit what State each City is assigned to, and edit what Country each State is assigned to. I have 3 tables:

tbCities
CityID (Primary Key)
StateID (Foreign Key from tbStates)
City

tbStates
StateID (Primary Key)
CountryID (Foreign Key from tbCountries)
State

tbCountries
CountryID (Primary Key)
Country

For right now, I want to focus on just States and Cities... so I also have this query:
quCity_to_State
Field: City
Table: tbCities

Field: State
Table: tbStates

The join type for the relationship between tbCities and tbStates is: Include ALL records from 'tbCities' and only those records from 'tbStates' where the joined fields are equal. That way I can see all the cities and the state they are assigned to.

However, when I view the query in datasheet view and try to type anything into the State field of this query, I keep getting this error: "Cannot enter value into blank field on 'one' side of outer join". I figured the source of the form should be this query instead of the tables directly, but if I can't input data into the query then I wont be able to input data into the form.

What am I doing wrong? Thanks for your insight!

 

[plog]

I think the whole idea is wrong. Sounds like you just read up on normalization and decided to overdose on it. I see it frequently.

First though, here's what you did wrong in implementing this--you inverted the relationships. tbCountries shouldn't have an ID to tbStates, tbStates should have an ID to tbCountries. tbStates shouldn't have an ID to tbCities, tbCities should have an ID to tbStates. Many cities can be in a state, many states can be in a country. Your structure doesn't achieve this because you have the wrong IDs in the wrong tables.


That said, I think you are overdoing it. What's the end game of this? Is storing Countries/States/Cities the purpose of this database, or is it just a part and its main purpose is something else? What will storing these entities like this help you achieve?

 

[cnstarz]

I think the whole idea is wrong. Sounds like you just read up on normalization and decided to overdose on it. I see it frequently.

First though, here's what you did wrong in implementing this--you inverted the relationships. tbCountries shouldn't have an ID to tbStates, tbStates should have an ID to tbCountries. tbStates shouldn't have an ID to tbCities, tbCities should have an ID to tbStates. Many cities can be in a state, many states can be in a country. Your structure doesn't achieve this because you have the wrong IDs in the wrong tables.


That said, I think you are overdoing it. What's the end game of this? Is storing Countries/States/Cities the purpose of this database, or is it just a part and its main purpose is something else? What will storing these entities like this help you achieve?

Ahh! I borked up my post, thanks for pointing out the way my tables were structured! The way you posted is the way that I actually have it -- tbCities has FK to tbStates, and tbStates has a FK to tbCountries. I just brainfarted when posting this, I fixed my OP.

In short, what we have is a database that ranks locations around the world so that we can prioritize all of our tasks that we perform in those locations. For example: our headquarters are in San Antonio. San Antonio is ranked #1, and therefore all tasks that are in San Antonio get prioritized above all other tasks that occur in all the other cities. (Note: They're not really called cities and states in the database, but I'm using the terms "cities" and "states" for the purpose of this post to make my request a bit easier to understand and hopefully relatable for others.)

When users are inputting tasks, they have to input what city, state, and country the task is being performed in. I don't want them typing any of that in -- I want them selecting it from a combo-box to reduce human-error (hence the tables for each category). But what I'm really getting at with this request is making it easier for others to add/modify/remove states or cities in the future without requiring much Access knowledge or expertise. So, I'd like to create a form where someone can choose a City and change what State it belongs to; another form would allow one to choose a State and change what Country it belongs to.

For the time being, I'm able to make a main form called "fmCity_to_State" that has a subform called "subfmState". On the main form, someone can choose the City and the subform displays the State it's currently assigned to via a combobox. The main form is tied to tbCities (remember, with a foreign key pointing to the tbState primary key) while the subform is tied to tbState. This works, but from what I've read, forms should be tied to queries, not tables. That's why I created the quCity_to_State query mentioned in OP where I experienced the error.

 

[plog]

from what I've read, forms should be tied to queries, not tables.

Where did you read that and what was the reason? I believe forms should be based on tables.

The main form is tied to tbCities (remember, with a foreign key pointing to the tbState primary key) while the subform is tied to tbState

It might be best to start using your actual objects and not Cities, States, Countries because I believe you are misstating your database still. Either that or you are doing your forms incorrectly. When you have a 1 to many relationship (States->Cities) the 1 should be the main form (States) and the many (Cities) should be the subform.

 

[cnstarz]

Where did you read that and what was the reason? I believe forms should be based on tables.

Well, now that I think about it, I dont know where I read that. I think it was some forum posts either on this forum or on other Access forums. I got that impression from somewhere so I've always based my forms off queries.

When you have a 1 to many relationship (States->Cities) the 1 should be the main form (States) and the many (Cities) should be the subform.

I understand that, but for the sake of allowing easy administration of the database, I want people to be able to easily select a city and modify what state it belongs to (without getting their hands dirty in a table) in the event that the city relocates to another state. Again, in the real-world this scenario would probably never, ever happen, but in my application the possibility exists.

Thanks for your help so far!

 

[Fran Lombard]

If I understand your issue correctly you are attempting to assign a property to an entity. ie Assign the State Property to a City.

This is very simple.
I think you may have over complicated your design by attempting to have your forms based on a query that joins the tables.

You should base your city form on your city table only, or if you want a query use Select tblCity.* from tblCity.

Then your State field on the form will be a combo box with a the following Properties and values:
RowSource: Select tblStates.StateID, tblStates.State from tblStates
Bound Column: 1
Column Count: 2
Column Widths: 0";2"

Hope this helps

 

[cnstarz]

I figured I could keep it simple like that, but from all the articles I've read about one-to-many relationships, the forms created for those relationships involve a main form and a subform based on a parent and child table, respectively. So I figured that was a best practice or general rule of thumb.

So, just for learning sake, going back to the original question: in the quCity_to_State query, why am I not allowed to type anything in the State field? Database attached.

 

[plog]

Honestly, I don't know the technical reason. It's because the query imparts conditions on the 2 tables because they're joined. If it allowed you to enter data, its possible you could insert incongruent data between the two.

 

[Fran Lombard]

So, just for learning sake, going back to the original question: in the quCity_to_State query, why am I not allowed to type anything in the State field? Database attached.

1. In the database you attached you did not have a primary key defined for either table. This prevented the query from being updateable. - Probably my fault - the db I sent you was missing these as I had just imported these tables from another db and did not check the table definition.

Once primary keys were assigned you are able to add new records.

2. However, In your query, you are returning only two fields
City from the tbCityCodes Table
State from the tbStateCodes Table

You are not returning the StateID from the tbCityCodes which is the Foreign Key field you want to maintain. This is the key field to the relationship between the tables.

As a result, if you created a new record using this query, you would end up creating a new record in both the City and State table. This would be the case even if you typed in an existing State.
Access would then update the StateID field in the City table with the StateID from the new record in the State table. I'm sure this is not your intent.

I hope this makes sense to you and helps you understand.
Fran

 

[jdraw]

cnstarz,

Please tell us why you are using a left join.

I also notice there is no state code related to
Barkhamsted, Chester and others.
But these are in Connecticut.

Perhaps you could tell us how you have identified

State contains City or similar.

 

[cnstarz]

2. However, In your query, you are returning only two fields
City from the tbCityCodes Table
State from the tbStateCodes Table

You are not returning the StateID from the tbCityCodes which is the Foreign Key field you want to maintain. This is the key field to the relationship between the tables.

As a result, if you created a new record using this query, you would end up creating a new record in both the City and State table. This would be the case even if you typed in an existing State.
Access would then update the StateID field in the City table with the StateID from the new record in the State table. I'm sure this is not your intent.

I hope this makes sense to you and helps you understand.
Fran

Thanks, Fran. Right now I'm not trying to add a record, I'm just trying to modify records by changing the State for any City using the query. I made the StateID and CityID fields Primary Keys in their respective tables, but I'm still unable to type anything in the State field in the query.

Please tell us why you are using a left join.

Cuz I want the query to show all cities even if they don't have a state assigned to them. Is there another way to do this without doing a left join?

I also notice there is no state code related to
Barkhamsted, Chester and others.
But these are in Connecticut.

I did that so I could have something to play with.