I have a test data DB with data from 4 searate part testers. They all have
fields with the date, the time, which tester it is, and whether it passed or
not. The main tester just also does ~100 other tests and outputs that data.
I therefore get 4 spreadsheets everyday. Because of that I went ahead and
combined all the data into in one table so when I analyzed it I could get for
instance the total amount of failures for the day/week/month. Of course this
leaves 3/4 of the table being blank. Doing this with one month is fine, the
database is only about 40mb and it doesnt take too long to bring up various
graphs to look at the data. Now that I want to look at the data from the
past year, with a pivot chart it takes 30mins+ with every step meaning to get
the chart I want would probably take over 2 hours! Even though there are
about 100 tests Im only usually graphing 1 yet it looks like my computer is
trying to squeeze the entire db into memory.

At first I tried separating tables by months and doing union querys to
combine months if I wanted, but that was a litte tedious. Ive also
compacted/repaired.

Is there anything I can do to get things to go a little quicker?

TWilson wrote:
> I therefore get 4 spreadsheets everyday. Because of that I went ahead and
> combined all the data into in one table so when I analyzed it I could get for
> instance the total amount of failures for the day/week/month. Of course this
> leaves 3/4 of the table being blank.

That means you put the columns side by side instead of appending new rows
into the existing table structure where there's only *one* column each for
date, time, tester and pass/fail, not four of each. You can use the import
wizard to append the rows of each subsequent spreadsheet after you import the
first one to get the table structure. You'll find your queries run much
faster with a normalized table structure.

--
Message posted via http://www.accessmonster.com

No, I made sure there is only one column for each type of data they share. I
use a macro in excel that combines the data before I import it to access. My
headings are basically: Date - Time - Tester - Pass/Fail - Tests (~100 of
which are only filled by the first tester).

"Granny Spitz via AccessMonster.com" wrote:

> TWilson wrote:
> > I therefore get 4 spreadsheets everyday. Because of that I went ahead and
> > combined all the data into in one table so when I analyzed it I could get for
> > instance the total amount of failures for the day/week/month. Of course this
> > leaves 3/4 of the table being blank.
>
> That means you put the columns side by side instead of appending new rows
> into the existing table structure where there's only *one* column each for
> date, time, tester and pass/fail, not four of each. You can use the import
> wizard to append the rows of each subsequent spreadsheet after you import the
> first one to get the table structure. You'll find your queries run much
> faster with a normalized table structure.
>
> --
> Message posted via http://www.accessmonster.com
>
>

ok...I'll ask the obvious question...have you compacted the db?
Tools>>Database Utilities>>Compact Database

--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com


"TWilson" <(E-Mail Removed)> wrote in message
news:A0091392-BA3B-4674-85A5-(E-Mail Removed)...
> No, I made sure there is only one column for each type of data they share.
> I
> use a macro in excel that combines the data before I import it to access.
> My
> headings are basically: Date - Time - Tester - Pass/Fail - Tests (~100 of
> which are only filled by the first tester).
>
> "Granny Spitz via AccessMonster.com" wrote:
>
>> TWilson wrote:
>> > I therefore get 4 spreadsheets everyday. Because of that I went ahead
>> > and
>> > combined all the data into in one table so when I analyzed it I could
>> > get for
>> > instance the total amount of failures for the day/week/month. Of
>> > course this
>> > leaves 3/4 of the table being blank.
>>
>> That means you put the columns side by side instead of appending new rows
>> into the existing table structure where there's only *one* column each
>> for
>> date, time, tester and pass/fail, not four of each. You can use the
>> import
>> wizard to append the rows of each subsequent spreadsheet after you import
>> the
>> first one to get the table structure. You'll find your queries run much
>> faster with a normalized table structure.
>>
>> --
>> Message posted via http://www.accessmonster.com
>>
>>

TWilson wrote:
> No, I made sure there is only one column for each type of data they share.

I'm confused. Your description of *combining all the data* of four
spreadsheets leaving *3/4 of the table blank* suggests only 1/4 of the
columns have data for any given row. If you're saying that's not the case I
must have misunderstood you because I can't really picture 3/4 of the rows
being blank when the columns are normalized. What is making the table 3/4
blank after you've combined the four spreadsheets into one?

If your columns are already normalized put indexes on relevant columns to
make queries run as fast as possible.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Yup. Said so in my first post.

"Kevin3NF" wrote:

> ok...I'll ask the obvious question...have you compacted the db?
> Tools>>Database Utilities>>Compact Database
>
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com
> http://kevin3nf.blogspot.com
>
>
> "TWilson" <(E-Mail Removed)> wrote in message
> news:A0091392-BA3B-4674-85A5-(E-Mail Removed)...
> > No, I made sure there is only one column for each type of data they share.
> > I
> > use a macro in excel that combines the data before I import it to access.
> > My
> > headings are basically: Date - Time - Tester - Pass/Fail - Tests (~100 of
> > which are only filled by the first tester).
> >
> > "Granny Spitz via AccessMonster.com" wrote:
> >
> >> TWilson wrote:
> >> > I therefore get 4 spreadsheets everyday. Because of that I went ahead
> >> > and
> >> > combined all the data into in one table so when I analyzed it I could
> >> > get for
> >> > instance the total amount of failures for the day/week/month. Of
> >> > course this
> >> > leaves 3/4 of the table being blank.
> >>
> >> That means you put the columns side by side instead of appending new rows
> >> into the existing table structure where there's only *one* column each
> >> for
> >> date, time, tester and pass/fail, not four of each. You can use the
> >> import
> >> wizard to append the rows of each subsequent spreadsheet after you import
> >> the
> >> first one to get the table structure. You'll find your queries run much
> >> faster with a normalized table structure.
> >>
> >> --
> >> Message posted via http://www.accessmonster.com
> >>
> >>
>
>
>

On Wed, 25 Oct 2006 12:10:02 -0700, TWilson
<(E-Mail Removed)> wrote:

>No, I made sure there is only one column for each type of data they share. I
>use a macro in excel that combines the data before I import it to access. My
>headings are basically: Date - Time - Tester - Pass/Fail - Tests (~100 of
>which are only filled by the first tester).

Do you mean that you have ~104 Fields in this table - one field for
each test!? If so you're still emphatically NOT normalized.

John W. Vinson[MVP]

Ahhhh, I see. Something else I need to learn to do. Im sorry, Im very new
to access.

How do you guys reccomend I should go about splitting up the table to
normalize it?

"John Vinson" wrote:

> On Wed, 25 Oct 2006 12:10:02 -0700, TWilson
> <(E-Mail Removed)> wrote:
>
> >No, I made sure there is only one column for each type of data they share. I
> >use a macro in excel that combines the data before I import it to access. My
> >headings are basically: Date - Time - Tester - Pass/Fail - Tests (~100 of
> >which are only filled by the first tester).
>
> Do you mean that you have ~104 Fields in this table - one field for
> each test!? If so you're still emphatically NOT normalized.
>
> John W. Vinson[MVP]
>

On Thu, 26 Oct 2006 07:07:01 -0700, TWilson
<(E-Mail Removed)> wrote:

>Ahhhh, I see. Something else I need to learn to do. Im sorry, Im very new
>to access.
>
>How do you guys reccomend I should go about splitting up the table to
>normalize it?

Three tables.

Runs
RunID
RunTime <date and time in one field, don't use two>
Tester
<other info about the test as a whole>

Tests
TestID
TestDescription <what's now your fieldnames>

Results
RunID <link to Runs, who ran the test when
TestID <what they ran
Result < the result of this test

This gives you "tall-thin" tables; if some tests weren't run you'll
simply have no records in the table at all for that test.

You *MIGHT* want a pass/fail field in the Runs table, but strictly
speaking it's derived data - I don't know what constitutes a pass or a
failure so I'm not sure how you'ld derive it.

John W. Vinson[MVP]

Ok. Couple questions.

About the pass/fail stuff, I neglected to mention a detail about that so
things looked simpler. The testers actually report a "status", with pass or
a failure code. There's a failure code depending upon which test the part
failed for. I guess Ill also add that once a part fails, no other tests are
done and the failure code for that certain test is reported. I also have a
list that identifies each failure code. So would I be right to add another
table:

Status
Status ID (failure code)
Failure Name,

then add another field to the "Runs" table for the Status ID?



From before you have a general idea of what my first table looked like.
That is also pretty much the format the testers give me the data. How do I
easily put all this data into their respective tables?


"John Vinson" wrote:

>
> Three tables.
>
> Runs
> RunID
> RunTime <date and time in one field, don't use two>
> Tester
> <other info about the test as a whole>
>
> Tests
> TestID
> TestDescription <what's now your fieldnames>
>
> Results
> RunID <link to Runs, who ran the test when
> TestID <what they ran
> Result < the result of this test
>
> This gives you "tall-thin" tables; if some tests weren't run you'll
> simply have no records in the table at all for that test.
>
> You *MIGHT* want a pass/fail field in the Runs table, but strictly
> speaking it's derived data - I don't know what constitutes a pass or a
> failure so I'm not sure how you'ld derive it.
>
> John W. Vinson[MVP]
>