Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Access Relationship help
- Thread starter cmills
- Start date
Look/google for "database normalization"
In general the higher the normalization level you get your data too ( 5 is commonly the highest, but I have seen in some systems level 6.) the more flexible and prepared for long term use the database is, but harder/longer it is to program the system to work with.
A saying (I heared? Made up? forget which as I started using it so long ago) that can help is: "Normalize until it hurts, then denormalize until it works."
For example say an order can have 3 states: Open, Shipped, Paid. Also lets say for the last 10+ years that has been the only 3 states and no one in the company thinks that in the future a new one will be added. So good normilization (e.g. No repeating data in a table field unless it is a foriegn key.) would say put the 3 states into a table and 'link' to that table via Primary/Foreign key relationships. Buttttt.... Maybe that is taking things to far in this case and all one needs in the order table is a 7 character status field.
I.M.O. Normalization/database design is part science, part art, part experiance.
Lion Crest Software Services
Anthony L. Testi
President
In general the higher the normalization level you get your data too ( 5 is commonly the highest, but I have seen in some systems level 6.) the more flexible and prepared for long term use the database is, but harder/longer it is to program the system to work with.
A saying (I heared? Made up? forget which as I started using it so long ago) that can help is: "Normalize until it hurts, then denormalize until it works."
For example say an order can have 3 states: Open, Shipped, Paid. Also lets say for the last 10+ years that has been the only 3 states and no one in the company thinks that in the future a new one will be added. So good normilization (e.g. No repeating data in a table field unless it is a foriegn key.) would say put the 3 states into a table and 'link' to that table via Primary/Foreign key relationships. Buttttt.... Maybe that is taking things to far in this case and all one needs in the order table is a 7 character status field.
I.M.O. Normalization/database design is part science, part art, part experiance.
Lion Crest Software Services
Anthony L. Testi
President
- Thread starter
- #3
You don't need training. You need advice on what the questions is.
A good relationship and join is called SQL. That's all you need.
You clearly have a problem with understanding what you are doing.
Access has concepts like relationships and referential integrity but these are categorically not essential. SQL and the Relational Model does not depend on these. They are optional add-ons that may make certain things easier.
Get your model and queries correct firstly with raw SQL. The principle of the Relational Model is all relationships are solely dependent on data in tables.
If your queries are giving the wrong answers it is because your SQL is wrong.
Give us details of the tables and your SQL and someone will help you.
A good relationship and join is called SQL. That's all you need.
You clearly have a problem with understanding what you are doing.
Access has concepts like relationships and referential integrity but these are categorically not essential. SQL and the Relational Model does not depend on these. They are optional add-ons that may make certain things easier.
Get your model and queries correct firstly with raw SQL. The principle of the Relational Model is all relationships are solely dependent on data in tables.
If your queries are giving the wrong answers it is because your SQL is wrong.
Give us details of the tables and your SQL and someone will help you.
cmills: Well I read the question wrong and therefore gave an answer that was not very helpful to you it seems.
BNPMike, is correct, if you could give us more details on what is happening we might be able to answer better.
Lion Crest Software Services
Anthony L. Testi
President
BNPMike, is correct, if you could give us more details on what is happening we might be able to answer better.
Lion Crest Software Services
Anthony L. Testi
President
I agree with dhookom's suggestion, and the advice on the link that Duane pointed to - print out each chapter, and read it - it makes more sense on hard copy when you have to refer back to a point later.
The other guys here are right too - more details mean we can respond better, but it does us good to be reminded to read what the original poster is asking for, and answer that question.
Your point about getting duplications is something that you will learn about when you understand SQL better. A perfectly good, well-structured database can retrieve duplicate records if the SQL is not tailored to remove them.
"SELECT" can be modified by "DISTINCT" or "DISTINCTROW" in ACCESS SQL statements to remove duplicate records in the query.
The other guys here are right too - more details mean we can respond better, but it does us good to be reminded to read what the original poster is asking for, and answer that question.
Your point about getting duplications is something that you will learn about when you understand SQL better. A perfectly good, well-structured database can retrieve duplicate records if the SQL is not tailored to remove them.
"SELECT" can be modified by "DISTINCT" or "DISTINCTROW" in ACCESS SQL statements to remove duplicate records in the query.
- Thread starter
- #8
thanks for all your help. what happens is, i have 2 tables, in one table, i have a listing of suspended consumers, and the second table has whether that consumer is active in a program. when i just list the suspension data, i get the correct # of suspensions, when i join the active program data i get 2 suspension records for each suspended service. that's what makes me think that it is how it is joined. there is a unique id field that exists in each table for each consumer.
cmills,
If you really want help, please provide your SQL view of the query. Also provide which are the primary and foreign keys. I expect you have duplicates somewhere.
It would also help if you provide a few significant records from each table that displays your issue.
Duane
Hook'D on Access
MS Access MVP
If you really want help, please provide your SQL view of the query. Also provide which are the primary and foreign keys. I expect you have duplicates somewhere.
It would also help if you provide a few significant records from each table that displays your issue.
Duane
Hook'D on Access
MS Access MVP
- Status
Similar threads
- Locked
- Question
- Locked
- Question