(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/
