normalised too much??

[Kysteratwork]

Hi all,

I have problem:

4 tables:
tblCompanies (with BusinessCity and BusinessCountry - both IDs linked to tblCities and tblCountries)
tblCities (with Country (an ID, linked to tblCountries))
tblCountries (with RegionID linked to tblRegions)
tblRegions

I would like to have in a form the follwoing fields:
tblCompanies.CompanyName
tblCities.BusinessCity
tblCompanies.BusinessCountry
tblRegions.Region

But as soon as I add the tblRegions in the Design View, I cannot add a new company anymore... now, why is that?? Did I break the tables down to too many components (city, country, region)? But, then I thought this is what normalisation is all about...

The SQL looks as follows:

 SELECT tblCompanies.CompanyID, tblCompanies.CompanyName, tblCities.BusinessCity, tblRegions.Region
FROM tblRegions INNER JOIN (tblCountries INNER JOIN (tblCities RIGHT JOIN tblCompanies ON tblCities.CityID = tblCompanies.BusinessCity) ON tblCountries.ID = tblCompanies.BusinessCountry) ON (tblRegions.RegionID = tblCountries.RegionID) AND (tblRegions.RegionID = tblCountries.RegionID);

I would be grateful for any indications you may have to help me out...

Kysteratwork

 

[PHV]

I don't think tblCompanies needs the BusinessCountry field as it should be derived from tblCities.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

Access has some rules about updatable queries. Search the Microsoft site - I haven't got the URL to hand, but it's something like "Using Updatable Queries"

 

[PHV]

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmmrfrds]

You could change the record set type to "dynaset inconsistent update", but it is probably better to investigate why you have an outer join in the query that should be updateable. I believe if all the joins were Inner then Access would allow the query to be updateable.

 

[Kysteratwork]

hmmm... the thing is: I don't have to update the City in this form, nor the country or Region. These should appear as the company is entered in the form (which in turn is selected from an existing list)

Also, some companies have a city and others don't, but they all have a country (and therefore a Region).

In the query, underlying the form, I can set the region to be the name (Europe, North-America, etc), but in the form, it only shows the RegionID (a number).

If I was able to see the Region NAME in the form and not only the ID, then I wouldn't have to include the tblRegion at all.

Is that possible? I hope I am expressing myself clearly...