tables with lots of zeroes

External Since: May 25, 2007 Posts: 953

"Nonsense" says it all.

"Steve" <nonsense DeleteThis @nomsense.com> wrote in message
news:R5idnfW1L_v4bCjVnZ2dnUVZ_tTinZ2d@earthlink.com...
> Did you read my post first then plagarize it?
>
> Steve
>
>
> "Jeff Boyce" <nonsense DeleteThis @nonsense.com> wrote in message
> news:eD9Vf6JCJHA.5656@TK2MSFTNGP04.phx.gbl...
>> Using "repeating columns" is a very spreadsheetly way to handle your
>> situation.
>>
>> If you want to get the best use of Access' relationally-oriented
>> features/functions, though, you'll need to brush up on "relational" and
>> "normalization".
>>
>> For example, with your design, you'd have to modify your table structure,
>> your query(ies), your form(s), your report(s), your code procedures,
>> everything if you decided to add just one more category/taxa.
>>
>> With a more-normalized design, you'd have one table of taxa, one table of
>> "entities", and one table to show valid combinations of "individual" and
>> category/taxa. Your table structure might look something like:
>>
>> tblEntities
>> EntityID
>> EntityTitle (what DO you call bugs, anyway?<g>)
>> EntityDescription
>> (... any other entity-specific info)
>>
>> tlkpTaxa
>> TaxaID
>> TaxaTitle
>> TaxaDescription
>>
>> trelEntityTaxa
>> EntityTaxaID
>> EntityID
>> TaxaID
>>
>> Note that using this third table would mean you would ONLY need to add as
>> many records (for a given entity) as it had applicable taxa. No need to
>> add the "empty" (zero) records.
>>
>> Good luck!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "buggirl" <buggirl DeleteThis @discussions.microsoft.com> wrote in message
>> news:AD04FF42-4D9F-4068-83F4-D35F00BA3D32@microsoft.com...
>>> Hi everybody,
>>>
>>> I'm trying to design a table for my invertebrate data. I have many
>>> samples
>>> and over 70 taxa identified. However, most of these taxa are rare and
>>> only
>>> occur in one or two samples - therefore I end up with a table that
>>> contains
>>> many, many zeroes, (this is a common issue in ecology).
>>>
>>> In Excel, I always store my samples as ROWS and my taxa as COLUMNS.
>>>
>>> I'm looking for a more efficient way of storing this data. I want to
>>> avoid
>>> all of those zeroes!!
>>>
>>> Any suggestions?
>>>
>>> I would also like to be able to link this DATA table to a table
>>> containing
>>> taxonomic information (hierarchical classification, making it easier to
>>> group
>>> organisms as, say, 'beetles' or 'flies'). Hopefully, if I set up the
>>> DATA
>>> table correctly then the TAXONOMIC table will be a piece of cake!
>>>
>>> Thanks,
>>>
>>> buggirl
>>
>>
>
>

External Since: Apr 06, 2005 Posts: 2923

Ignore Steve.
This time he gave you about 40% of a correct answer which is better than
average. Usually, when he tries to answer a question without offering to do
it for you for a reasonable fee, his answer is incorrect.
--
Dave Hargis, Microsoft Access MVP

"buggirl" wrote:

> Hi everybody,
>
> I'm trying to design a table for my invertebrate data. I have many samples
> and over 70 taxa identified. However, most of these taxa are rare and only
> occur in one or two samples - therefore I end up with a table that contains
> many, many zeroes, (this is a common issue in ecology).
>
> In Excel, I always store my samples as ROWS and my taxa as COLUMNS.
>
> I'm looking for a more efficient way of storing this data. I want to avoid
> all of those zeroes!!
>
> Any suggestions?
>
> I would also like to be able to link this DATA table to a table containing
> taxonomic information (hierarchical classification, making it easier to group
> organisms as, say, 'beetles' or 'flies'). Hopefully, if I set up the DATA
> table correctly then the TAXONOMIC table will be a piece of cake!
>
> Thanks,
>
> buggirl