Brett - First, thank you for your time. I sincerely appreciate the help. Second, my name is Mike. Third, I've got to run here in a bit, but I wanted to thank you and let you know that I will be getting back to you this evening and hopefully I can fill in some of the blanks.
Mike --- In [email protected], Brett Collings <[EMAIL PROTECTED]> wrote: > > (Your Name?) > > The presentation of the information is not as relevant as the > collection of it. From what I understand below, your Table A and > Table B are really the reporting formats you want. Is that > correct? In fact, tables are never used to present data as a > spreadsheet is. You use a Form or Report to do that based on a > Query. The tables collect the data in such a way as to make later > analysis, sorting and grouping possible in any way you can think of. > > In your case, you've defined your table structure in your three > primary information groups and I would build them as ... > tblSex > - SexID > - SexType > ... but not age if that is a variable value. That would be included > in tblHarvest as defined below > > tblCounty > - CountyID (Primary Key Autonumber) > - CountyName > > tblSeason > - SeasonID (Primary Key Autonumber) > - SeasonName > > Then, I would bring all of those elements together in .. > tblHarvest > - HarvestID (Primary Key Autonumber) > - CountyID (Type Number. Foreign Key) > - dteHarvest (type Date. You can use full date but only > report/extract the Year if you wish - thus getting two pieces of > information for later analysis) > - SeasonID (Type Number. Foreign Key) > - SexID (Type Number. Foreign Key) > > What this allows you to do is build a Query and present the findings > in a Report in the format of either Table A or B ... or indeed any > other analysis that you wish to do. With this structure you can > group your report on County, Year, Sex or Season ... the combinations > are endless. That's why the non-repeating values are so important in > a relational structure. > > I'm sure that raises more questions than it provides answers ... ask away :) > > Brett > > > > > > > > At 22:15 12/09/06, you wrote: > > >Dear NGs, > > > >I recently downloaded and read a bunch of material on normalizing > >your data and db design. Things aren't crystal clear yet! Part of > >the problem is that nearly every thing I read used the same customer > >invoice data as an example. I'm dealing with deer harvest data that > >will never need updating (unlike customer data!). One nagging > >question that I have deals with the 1NF and non-repeating groups. At > >least to me it seems that you have two choices - either repeat groups > >across records or transpose the data. Let me explain. This is a > >sample of my data. When a deer is harvested by a hunter there are 4 > >pieces of information I collect: > > > >Sex/age of deer (Male, Female, Button) > >County of harvest (Adams, Allen, Ashland...) > >Hunting season (Longbow, Crossbow, Gun, SWML, and a few others) > >Year > > > >The raw data are summarized each year and combined with data from > >previous years into a table that looks like the following: > > > >TABLE A > > > >County Year Season Male Female Button > > > >Adams 1980 Crossbow 40 100 67 > >Adams 1981 Gun 45 110 87 > >Allen 1980 Crossbow 50 700 670 > > > >Ignoring for a moment all that is wrong with it, my immediate > >question is, should the "Male", "Female", and "Button" fields be > >transposed to include a SexAge and "Value" field? IOW should the > >above data look like this instead: > > > >TABLE B > > > >County Year Season SexAge Deer > > > >Adams 1980 Crossbow M 40 > >Adams 1980 Crossbow F 100 > >Adams 1980 Crossbow B 67 > >Adams 1981 Gun M 45 > > > > From where I stand, there is at least 1 reason to set it up like > >TABLE B - I'm always in need of total harvest (M+F+B). It would be > >much easier to get total harvest for a county, season, and year with > >Table B. So, how does this relate to "repeating groups" and first > >normal form - SexAge is now repeating across records. I guess the > >solution would now be separate tables! > > > >Any and all feedback is greatly appreciated. > > > > > > > > > > > > > > > >Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > >-- > >No virus found in this incoming message. > >Checked by AVG Free Edition. > >Version: 7.1.405 / Virus Database: 268.12.2/442 - Release Date: 08/09/06 > > > -- > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.1.405 / Virus Database: 268.12.2/442 - Release Date: 08/09/06 > Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/ms_access/join (Yahoo! ID required) <*> To change settings via email: mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
