I wanted to create a field for city (for address) in my form but that could lead to multiple people having the same city which according to normalization means a new table to be created for the cities. So I created a new table with a primary key as its autonumber and created a relationship of the primary key from cities to foreign key of cities in the main table. So far so good but now my main form won't accept a record without first having entry of a new city in the cities table so i removed referential integrity. Now when I created a form based on these two tables, it adds every city that I enter a new entry in the cities table even for previously-stored city.
How can we fix things? it has puzzled me a lot and I have thought a lot like inner joins etc but nothing seems to work except that I put the city back in the main form and let it repeat itself (so breaking one rule of normalization).
14 2631 NeoPa 32,554
Expert Mod 16PB Usman:
So far so good but now my main form won't accept a record without first having entry of a new city in the cities table so i removed referential integrity.
When you have a problem it's important that you fix it rather than simply make it go away. Fixing it makes it work. Making it go away simply shifts the problem elsewhere.
Let me stress first that an understanding of Database Normalisation and Table Structures is critical to working with databases. That takes more work than simply reading the words. You need to grasp the ideas themselves. If you can understand why they make sense then that's even better.
I'm afraid I'm struggling to understand what you've created from your description. Certainly one possibility, if you want to ensure any city used in an address is a valid one, is to link it to a table of valid cities that you maintain with only valid cities in. Let's refer to that as [tblCity] for now.
When you have that, and have set up referential integrity properly between any tables that use cities in their addresses, you will use a One-to-Many link between these tables and you should find that any time you add a city to your address it will only allow one from the list of valid cities.
My apologies if i haven't put things the right way; i'm trying to fix things my way but sometimes i just can't figure them out and then turn to internet or other resources because i truly want to learn my way so that i have a strong grasp of the database.
So let me put this again, i don't have a complete list of cities but i want that whenever i enter a city name it gets stored and the next time i enter same name then that should not be repeated (data integrity so that i may not have same city with different spellings). If i create a new tblCity then the main table wants an entry in tblcity before that city can be entered in main form which would mean i need to edit city table all the time which i don't want to do.
I hope that clarifies and hope to hear something that will clarify my concepts.
thanks
There is a saying...Normalise until it hurts, denormalise till it works.
Whilst you could have a separate table for each city, if you continue with this idea for handling addresses, you would also need a separate table for each district, village, street name, building name and postcode (etc). Personally, I think that is inappropriate and unhelpful for most if not all those address fields. Up to you, but I don't do this when handling addresses in any of my databases
NeoPa 32,554
Expert Mod 16PB
That's a fair bit more complicated. Before you even start though, how do you plan to handle the difference between a new city that hasn't been used before and a new city that is the same as an existing city but just spelled wrong?
The way you describe it there is no way for the computer to tell the difference so you need to know what your thinking is - what your exact requirements are - before you have any hope of telling the computer how to make it happen.
I hope that helps to clarify your thinking.
NB. There are ways to handle most things but before we handle what your problem is we need to understand it clearly. For us to understand it clearly YOU need to understand it clearly first.
NeoPa 32,554
Expert Mod 16PB IslaDogs:
Whilst you could have a separate table for each city
I suspect there's some misunderstanding here. I believe Usman is trying to explain something a lot more straightforward - but is losing something in the translation. My interpretation is that when he talks of City tables he really means records.
Hi @NeoPa
You may be correct but that's not my interpretation from his two posts
Great! i got the idea.
My sole purpose of this thing was to ensure that i don't repeat same data with different names and now i understand that not everything needs to be "nomalized" like you can't create a new table for gender so that you avoid repeating male/female. that's just not needed and is an overkill.
NeoPa 32,554
Expert Mod 16PB IslaDogs:
Normalise until it hurts, denormalise till it works.
That makes sense. Unfortunately, many people simply don't see the first part or understand why it's there and what the normalisation part does for them.
On the other hand, if I may mix my metaphors horribly, I see my role simply as leading the horses to the water.
@NeoPa wrote:
if I may mix my metaphors horribly, I see my role simply as leading the horses to the water.
...but you can't make them drink! (to quote the old proverb)
If anyone is totally baffled by the relevance of this quote, see here
@usman4575
Actually, these days with many people self identifying as a whole range of other genders rather than M/F, that example isn't necessarily still completely true. See How many genders are there in 2021?
...but you can't make them drink! (to quote the old proverb)
However, you CAN hold it's head under water until it quits moving.
Hopefully a 'nautical gent' wouldn't dream of doing so 😏
I think something have been overlooked here. The poster reported " So far so good but now my main form won't accept a record without first having entry of a new city in the cities table so i removed referential integrity. "
What was going on was that you had no default city established or the default did not exist in the city table. Putting aside the question of whether the city table should actually be a city/state table, or even a city/state/zip table, you need to be sure that your default on new records satisfies the referential integrity. You might consider including a "Unknown City" in your city table and then set that to your default value for the city. That is what will prevent the original problem. Then you might add some logic to prevent acceptance of the record until the city is something other than "Unknown City".
Jim
NeoPa 32,554
Expert Mod 16PB
Hi Jim.
I think that horse stopped drinking a while ago I'm afraid.
That said of course, it's always helpful for others that such explanations can also be found in these threads so it is worth the explanation :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Muzamil |
last post by:
hello
I've a denormalized table PRODUCTS with following fields:
ProductNo ,
OrderNo ,
SerialNo ,
OrderDate ,
PromiseDate ,
|
by: Josh Renaud |
last post by:
I'm still trying to solve a problem I have experienced in Safari. This is my
third post on the subject. I'm hoping someone can shed some light.
The problem is that, in Safari, a table with no...
|
by: Thomas Scheiderich |
last post by:
I have a table that has 3 image slices. The middle slice changes size based
on size of browser window. This works great.
My client wants the date to show over the image on the right. So what I...
|
by: murray |
last post by:
Large table performance problem with MYSQL performance with ASP.NET
I am doing some benchmarking of an application using MYSQL under
Windows and I confused about the results.
Environment...
|
by: OctoTools |
last post by:
Large table performance problem with MYSQL performance with ASP.NET
I am doing some benchmarking of an application using MYSQL under
Windows and I confused about the results.
Environment...
|
by: Kamal |
last post by:
Hello all,
I have a very simple html table with collapsible rows and sorting
capabilities. The collapsible row is hidden with css rule
(display:none). When one clicks in the left of the...
|
by: selimzairi |
last post by:
hi to all
would any body help please on this cross table report problem
yrs header -for years grouping
mnt header -for monthes grouping
state_id -for states
columns 1->30 is for branches id;...
|
by: John Roberts |
last post by:
Hi guys,
My database is accessed by several users. When the user opens my report it first makes a table of temporary data from one query, then uses another query on that table to populate my...
|
by: Dani |
last post by:
Hi,
I'm developing and HR database with Access 2010. I've hit a snag with designing a Performance Evaluation table.
I have various lookup tables for:
EmployeeID
SupervisorID...
|
by: sman |
last post by:
i wish to now know how to normalize a table which have duplicate names and number repeated severally and i tried normalizing but could not
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |