Automatically assigning specimens to queries/tables?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

park.walter@mayo.edu - 27 May 2008 20:25 GMT

Hello all-

I use Access more for it's data handling capabilities than it's
database capabilities so here goes.

In one table I have specimen information that can be characterized
into ~30 different study groups. Each specimen can be in one or more
study groups. I also have a data table that has ~60,000 records for
each specimen. I would like to analyze data from the samples in each
group and examine it across other members of the same group. Here's a
simple design:

SpecTbl columns:
Spec
Group
Time
etc.

DataTbl columns:
Spec1
Spec2
etc

I would like to capture the data for the Specs in each group so that I
can manipulate the data. However, currently I have to manually select
the Specs to add to each group. Is there a way to do this
automatically since I've already recorded the information in the
SpecTbl ? Part of my problem has been the lack of Access GB capacity
- Tables that contain 60,000 records and are 200+ Spec wide are quite
difficult to work with.

Can anyone offer a suggestion to alleviate the bottleneck?

wdp

ps. I am not a programmer and do not work with SQL....

Reply to this Message

John W. Vinson - 28 May 2008 01:47 GMT

>Tables that contain 60,000 records and are 200+ Spec wide are quite
>difficult to work with.

And they are also incorrectly normalized and incorrectly structured. A table
with more than 30 fields is suspicious right there.

"Fields are expensive, records are cheap". Your data table is NOT a table;
it's a spreadsheet! If you have one *FIELD* per specimen, you're storing data
in fieldnames, never a good idea.

I'm confused though: is a "spec" a specimen, or a specification, or a value
from a measurement? What's actually in the data table?

Signature

John W. Vinson [MVP]

Reply to this Message

park.walter@mayo.edu - 28 May 2008 13:51 GMT

John-

Thanks for the reply.

Each specimen/sample requires a separate field. I have ~200 specimens
so that means the data table is ~200 columns wide (column header is
the sample name). I can't flip it as the # of data rows for each
sample is ~60,000 numerical records. For each sample the record is
aligned/associated with a unique data source field that is the first
column in the table.

I guess I'd agree that the data is organized in a spreadsheet type
manner - it's larger than any spreadsheet program can handle though,
which is why I use access as the data handling program. I know it
doesn't follow standard Access design ( you guys always rip on me for
that) but for nearly every issue that has come up I have been able to
adapt Access to work quite nicely.

So far I have been able to flip the SpecTbl so that it reads left to
right for all samples, depending on the group/time that the sample is
in. This works great - there are 28 groups/times. I then put this
crosstab into a new mk-tbl query and add in the DataTbl. I then merge
the two for all samples by an IIF() statement that asks if the sample
is in a group/time and if it is it returns the DataTbl information.
The problem is that doing so many IIF()'s is difficult for Access and
it tops out just over 120 samples. I end up having to do two mk-tbl
queries but the results are so large (28 x 60,000= 1.8 million lines
each) that I can't merge the two tbls into a single table that is
needed for my downstream queries - tops out >2 GB.

This is why I am looking for a quicker way for Access to recognize a
new sample has been added to the DataTbl and associated with a certain
group/time in the SpecTbl. Currently for all samples it is looking to
see if the sample belongs to a group/time and if it does it gives
data, if not, it leaves a blank but I still end up with massively
large tables at the end.

Sorry for the long post, I hope that makes some sense.....

wdp

SpecTbl columns:
Spec (rows: 1-200)
Group
Time
etc.

DataTbl columns:
Source of data
Spec1
Spec2....
etc

Reply to this Message

John W. Vinson - 28 May 2008 22:07 GMT

>John-
>
[quoted text clipped - 6 lines]
>aligned/associated with a unique data source field that is the first
>column in the table.

If you are consciously and intentionally rejecting the relational paradigm, I
really can't help much. Of course the normalized way to do this is with three
tables:

Specimens
SpecimenID
Description
<other information about the specimen itself>

This table would have 200 rows.

Datasources
DatasourceID
<information about the datasource>

Results
DatasourceID
SpecimenID
DataValue

If you have 60000 datasources x 200 specimens this does indeed give you
12,000,000 rows; but if you're storing just three numbers in each row, that's
nowhere near the 2GByte limit.

>I guess I'd agree that the data is organized in a spreadsheet type
>manner - it's larger than any spreadsheet program can handle though,
>which is why I use access as the data handling program. I know it
>doesn't follow standard Access design ( you guys always rip on me for
>that) but for nearly every issue that has come up I have been able to
>adapt Access to work quite nicely.

Procrustes was able to get all of his guests to fit in his bed, too (see the
Odessey by Homer).

>So far I have been able to flip the SpecTbl so that it reads left to
>right for all samples, depending on the group/time that the sample is
[quoted text clipped - 30 lines]
>Spec2....
>etc

Signature

John W. Vinson [MVP]

Reply to this Message

park.walter@mayo.edu - 29 May 2008 13:56 GMT

John-

Thanks for the insights. I reason the DataTbl is 200 columns wide is
that this is how I receive the data. I could manually linearize the
table to fit with the standard convention as you suggest but this will
take lots of time and I'm not sure it would actually solve my problem
since the "data" is a single letter of text. Basically I would like
to fuse the letter associated with each of the 60,000 datapoints for
each sample in a group/time. Then calculate a frequency.

Ie:
Say for example samples 1, 2 and 3 belong to the same group/time
sample 1; data 1: F
sample 2; data 1: C
sample 3; data 1: C
As I increase or decrease the # of samples I want Access to
automatically count how many samples are in the group (FFC=3) and how
many "C"s are represented (2/3).

As you know Access is flexible enough to have many uses. I have more
than twenty MDBs and most of them ignore the strict concept of
referential integrity. That concept may work for you but recognize
that people like me who use Access to manage GB of data from multiple
other non-access databases don't always get that luxury. The beauty
of Access is it's flexibility, not simply what fits some definition of
"the relational paradigm".

wdp

Reply to this Message

John W. Vinson - 30 May 2008 06:08 GMT

>John-
>
[quoted text clipped - 14 lines]
>automatically count how many samples are in the group (FFC=3) and how
>many "C"s are represented (2/3).

I'm sorry. I simply don't understand the term "fuse" or what is meant by a
"group/time" or what "FFC=3" means. Your sample has F, C and C; how does that
parse to FFC?

I expect that a VBA function to parse the 200-field record will be the way to
go, but since I do not understand your data model or the desired result, I'm
at a loss. Maybe I need some more Ginko or some more sleep!

I suggest that you repost the question as a new thread.

Signature

John W. Vinson [MVP]

Reply to this Message