Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-19-2019, 08:17 AM   #31
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 32
Thanks: 12
Thanked 0 Times in 0 Posts
JoeBruce is on a distinguished road
Re: Normalizing (and fixing) a large, messy DB

Thanks for the quick reply, plog. I read it the other day and have been pondering your advice.

To answer your main follow up question, permits relate to the climbers who climbed and which routes they climbed. A permit can relate to a guide company, but only if one was present for the climb (in the context of data, that means a null value should be acceptable here). I might play around with the idea of a guide company relating to tblRouteRecords, especially in the context of your forms advice.

What you wrote about forms makes a lot of sense. I have done something similar, just not in the order you describe. I will experiment a bit and see what I can come up with.

I've realized the issue with forms based on queries; I generally use tables too, but when looking at the record set for the form I guess I turned it into a query. I had been referencing the other database I made, and saw that main form used a query. Now looking closer I do not think that form pulls data from more than one table, so the query there is superfluous. For this database, I've reverted back to the form based on a table; my subforms all seem to be select queries, though.


Last edited by JoeBruce; 11-19-2019 at 08:45 AM.
JoeBruce is offline   Reply With Quote
Old 12-07-2019, 01:07 PM   #32
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Normalizing (and fixing) a large, messy DB

Out of interest, do you know how much your firm spent on the database.
How many forms are there? How many queries?

Re-engineering a large database is an extensive and difficult project. Re-engineering a bad database is hard to justify. You may as well start over, but you have to convince someone that it's worth the effort and cost.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 12-19-2019, 12:17 PM   #33
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,387
Thanks: 15
Thanked 1,623 Times in 1,541 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Normalizing (and fixing) a large, messy DB

I lost track of this thread but binding forms to queries is not a problem. All my forms are bound to queries - mostly so that I can use criteria to limit the record selections but sometimes to make it easy to show "lookup data" -- i.e. the ONE side of a relationship. To show the many-side of a relationship requires the use of a subform. So take an Order. On the order header, I want to show customer data. That is on the 1-side of the relationship so I can join tblOrders and tblCustomers and show customer data on the order form. However, orderDetails is the many-side of the relationship and so I need to use a subform to show data from tblOrderDetails.

In your case, you have multiple many-side relationships coming off the main table. EACH many-side table requires a separate subform. If you try to make a query that joins the three tables, it will be not-updatealble and it will show strangely "duplicated" data. The solution will be to correctly use separate subforms for each set of many-side data. Just because two tables are related to a parent table doesn't mean that they are related to each other.
tblCustomer-->tblOrders-->tblOrderDetails could be displayed in a single form as long as you are OK seeing the duplication of the first two tables for each item in the Details table
But
tblStudents-->tblClasses
tblStudentsr-->tblPets
cannot be shown in one query because student's pets have nothing to do with student's classes. So although you can create this query by joining tblClasses and tblPets each to tblStudents, the results will be a Cartesian Product (and not updateable) and the results will be strangely duplicated since it will show each of the pets attending Algebra and then each of the pets attending Geography, etc.

__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 12-19-2019 at 12:24 PM.
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
JoeBruce (01-06-2020)
Old 01-06-2020, 12:07 PM   #34
JoeBruce
Newly Registered User
 
Join Date: Jan 2017
Posts: 32
Thanks: 12
Thanked 0 Times in 0 Posts
JoeBruce is on a distinguished road
Re: Normalizing (and fixing) a large, messy DB

@Gemma - my "firm" is a land management agency for the US federal government (insert necessary disclaimer about how everything I say and do here does not represent said agency). I'm sure the only money spent on creating the original database was the salary of the employee who did it. Although I've urged people to pay a pro and get a good product (build a normalized, user-friendly DB from scratch and import records into that from the old DB), that likely will not ever happen. I have made the efforts detailed in this thread out of a masochistic interest in MS Access, and because the current DB used for this is atrocious. I doubt it will become a finished product, but I may continue to plug away at the thing.

@Pat - thanks for an alternate perspective! There are always multiple ways to do things in this program. If and when I do start thinking about this, the advice of you, plog, Mark, and all will continue to help guide me.

In the mean time, I am actually brainstorming a different DB; but all of this is certainly helpful. In fact, I logged back in to check the advice given here to apply it to my design efforts. Cheers!

JoeBruce is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Scattered fields make a messy table hilian Tables 16 07-10-2015 11:22 AM
A very messy Count. pr2-eugin Queries 2 01-03-2013 06:22 AM
Import from Excel - Messy maw230 General 10 08-05-2010 10:29 AM
[SOLVED] VBA routine that would organise my messy table kalevi12 Modules & VBA 3 04-03-2006 04:23 AM
messy screen during runtime striker Modules & VBA 1 05-09-2002 01:00 AM




All times are GMT -8. The time now is 10:42 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World