 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / February 2004Relational vs Flat
I'm trying to develop a db whose primary purpose will be saving values for a report to be printed. In the vein of being a relational devotee I have a Report Table, an Agency Table, an Agent Table. The Agent table saves the primary key of the Agency Table. On the Report form, I have a combobox with the Agencies and their indices; when an Agency is selected, another combobox is populated with the Agents from that Agency. The user selects an Agent and address and phone info from the Agent table is displayed. So in the structure is something like: Report table Report ID fkAgencyID fkAgentID Agent Table AgentID fkAgencyID In order to display the Agent info I was using DLookup but then I have to use DLookup on the form's current event otherwise nothing is displayed. I can get around this by showing the Agent data in a subform but that means I'll have lots of subforms on my Report form. I'm beginning to think it would be better to save the Agent data in the Report Table, not just the key but that feels like one huge table----flat db instead of relational db. At the same time aligning the subforms is really a hassle. Do I take the high road and stick to the relational db or just give in and store the various fields in the Report table instead of the foreign keys.  SignatureKaren > In the vein of being a relational devotee I have a Report Table, an > Agency Table, an Agent Table. ... > In order to display the Agent info I was using DLookup Why not just a join? Base the form on a query that joins the Report to the Agent to the Agency. Include whatever fields you want -- but you need to think quite carefully about which version of the PK/ FK pairs you use. > Do I take the high road and stick to > the relational db or just give in and store the various fields in the > Report table instead of the foreign keys. It's nothing to do with high roads or low roads; it's about business cases. R theory is about being able to guarantee the correctness of the data. If this is more important than development time, then follow that route. On the other hand, if getting it up and running takes priority over the fact that Agent A may have different addresses in two different Reports, then you need a different development route. Hope that helps Tim F Tim, Thanks for the ideas. I'm leaning towards saving the values in the Report table because I could preserve the values at the time of the report that way. We'll see. Karen > > In the vein of being a relational devotee I have a Report Table, an > > Agency Table, an Agent Table. [quoted text clipped - 21 lines] > > Tim F >I'm beginning to think it would be better to save the Agent data in the Report Table, not just the key but that feels like one huge table----flat db instead of relational db. If you assume that you must have all the data in one table in order to generate a Report, you would need to do this. THAT ASSUMPTION IS INCORRECT! Base your Report on a Query joining your Report table (with ID's in it) to the related tables; pull the report data from the report table, and the names from the name table. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public John, I hate to say it but the query route just seems like a monster to me. I use VBA and SQL a lot to build queries programmatically. In this report, for instance, I have what amounts to five sections that draw from the same two tables. In each section, one combo box chooses an Agency and that choice defines what Agents will be available in an associated combo box then depending on the Agent choice 10 other text boxes are populated. I can build a query with query builder to pull all of the data from the two tables but I still have to have a way to refine the data set depending on five sets of combo boxes. I literally just can't get my arms around how to do that with a source query; I know how to do it programmatically. I'm still very much a newbie and self-taught with the considerable help of some books and, thank goodness, these newsgroups. Anyway, am I making any sense? Is there some fundamental program or query device that I could use? Karen > >I'm beginning to think it would be better to save the Agent data in the Report Table, not just the key but that feels like one huge table----flat db instead of relational db. > If you assume that you must have all the data in one table in order to > generate a Report, you would need to do this. [quoted text clipped - 8 lines] > Come for live chats every Tuesday and Thursday > http://go.compuserve.com/msdevapps?loc=us&access=public I've been reading your posts and I'm confused about what you're trying to do. You talk about printing a report, but you also talk about a form and combo boxes and displaying agent data on that form. And you talk as if you have a report that has combo boxes. What is the purpose of the form? Is it merely a mechanism to allow the user to select the agency/agents that are going to be on the printed report? And what is the significance of the five sections? Rick >Subject: Re: Relational vs Flat >From: "Karen" wonderlover@funcity.com [quoted text clipped - 23 lines] > >Karen Rick, Indeed, the form collects the data for a report, actually a series of reports but that is not significant. Basically the report has 5 sections that are similar. In each section an Agency is selected, based on the Agency selection a following combobox is populated with agents associated with that Agency and then based on the agent selection a series of textboxes in that section are populated. These are only sections in the sense that I've put a rectangle around the comboboxes and textboxes in a section to guide the user, nothing more. I could have created five separate tables for each of the Agencies and 5 separate tables for Agents related to each Agency but since the basic structure of the tables would be identical, I chose to have 1 Agency table and 1 Agent table. The AgencyID is saved as a foreign key in the Agent table to designate the Agent's relationship to the Agency. Karen > I've been reading your posts and I'm confused about what you're trying to do. > You talk about printing a report, but you also talk about a form and combo [quoted text clipped - 18 lines] > >instance, I have what amounts to five sections that draw from the same two > >tables. In each section, one combo box chooses an Agency and that choice > >defines what Agents will be available in an associated combo box then > >depending on the Agent choice 10 other text boxes are populated. [quoted text clipped - 12 lines] > > > >Karen See comments/questions intersperced, below. >Rick, > [quoted text clipped - 65 lines] >> > >> >Karen Rick G Sorry about the previous post... hit the enter key by mistake before I had written anything. See comments/questions interspersed, below. >Rick, > >Indeed, the form collects the data for a report, actually a series of >reports but that is not significant. I'm still confused. When you say "collects the data", what does that mean? Does that mean it's being input by the user? Or does it mean the form is collecting existing data from existing database table in order to be printed on the report later? Or what? >Basically the report has 5 sections that are similar. In each section an >Agency is selected, based on the Agency selection a following combobox is [quoted text clipped - 3 lines] >These are only sections in the sense that I've put a rectangle around the >comboboxes and textboxes in a section to guide the user, nothing more. In each section of the form, it looks like you have 3 steps: 1) The AgencyComboBox needs a query associated with it. This would be in the combo box's Rowsource property. The query would look something like the following: "SELECT Agency.AgencyID, Agency.Name FROM Agency ORDERED BY Agency.Name". The combo box would display every agancy. This would be identical for the Agency combo box in each section. 2) Once a selection were made in an AgencyComboBox, you would ReQuery the AgentComboBox in the SAME section so that it would list the Agents associated with that Agency. Each AgentComboBox would also have a query associated with it. The query would be SOMETHING LIKE the following: "SELECT Agent.Name Agent.AgentID FROM Agent ORDERED BY Agent.Name WHERE Agent.AgencyID = AgencyComboBox1.??SelectedItem??.AgencyID". (Please note that off the top of my head I'm not familiar with how to refer to the selected item in the AgencyComboBox.) Note that this query is specifically refering to the selected item in the specific Agency combo box in the same section - AgencyComboBox1. The queries in each section would be a little different in that they would refer to different combo boxes... AgencyComboBox2/3/4/5 (or whatever you named them). 3) Once a selection is made in an AgentComboBox, use that combo box's selected agent (AgentID) in the DLookups to get the information from the database to display on in that section of the form. >I could have created five separate tables for each of the Agencies and 5 >separate tables for Agents related to each Agency but since the basic >structure of the tables would be identical, I chose to have 1 Agency table >and 1 Agent table. The AgencyID is saved as a foreign key in the Agent >table to designate the Agent's relationship to the Agency. That's definately the wat to go - 1 Agency table and 1 Agent table. >Karen > [quoted text clipped - 46 lines] >> > >> >Karen Rick G Thanks Rick, Yes, I'm talking about a form in Access where the user makes selections. Those selections are put in a Report table and later, in Word, those values are merged into a report. I have done everything you suggested although the Agency combos are slightly different as each displays Agencies where the AgencyType is Investigation or Law or Insurance.....that sort of thing. I have using the AgencyID to pull the applicable Agents with a Select query on the Agent's combobox's rowsource. I am using DLookup to populate the textboxes after the Agent selection is made. What piques my interest is John's suggestion that I just use a query to base the Report Access form on that would pull from the Report table and the associated Agency and Agent table. I can figure this out in code per combobox but I just fall down when I try to figure out how to use one reasonable query for the form. Thanks for help. Karen > Sorry about the previous post... hit the enter key by mistake before I had > written anything. [quoted text clipped - 104 lines] > > Rick G This should be easy and the Report wizard should take care of everything for you. However, I'm assuming that, in Access, you have actually defined the relationships between the tables. If you have done that, then all you need to do is run the Report wizard, and specify the fields/tables that you want, and the report will be generated with an appropriate SQL statement in the report's Record Source property. That SQL statement will combine the records in your 3 tables appropriately to create the report. >What piques my interest is John's suggestion that I just use a query to base >the Report Access form on that would pull from the Report table and the >associated Agency and Agent table. I can figure this out in code per >combobox but I just fall down when I try to figure out how to use one >reasonable query for the form. Rick G |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.