500mb+ DB - Help!

[John Vinson]

I want to do what you reccomended for me to do.

Just to be sure we're on the same page, here's what I have so far.

1. I get 4 separate excel spreadsheets of data from the 4 testers.
2. I combine them into 1 excel spreadsheet. (Im now done with excel)

Any reason to do so, rather than import them individually? Do all the
spreadsheets have the same structure, or does each tester have a
different suite of tests?

3. I import that excel spreadsheet into Access into a flat wide table with a
test per field.

That could be done using import, or you could link to the spreadsheet
(or sheets). If you're worried about database bloat, the link would be
preferable, I'd expect.

4. I created 3 queries (to avoid the query too complex error) which
converted and separated the wide table into 3 tall thin queries putting all
the tests and test results into thier own single fields. (Instead of ~107
fields, Im down to 7)

Now how do I combine the 3 queries into a single table?

Create the new Table with appropriate fields, indexing, and datatypes,
and base an Append query on each of the three normalizing UNION
queries.

Also, please keep in mind I will be importing new data to this db daily.

Will the spreadsheets contain only *new* data? or will they contain
data which is already in the normalized table?

I would also like everything to automatically go where it needs to after I
import the spreadsheet.

Then you'll need a Macro, or (much better) VBA code to run the various
imports and queries in the appropriate sequence.

When this is all set up I would like for it to be -
1. Import excel spreadsheet 2. I can now analyze the data in line/bar graphs
(eg. - test data vs some time period). Does this completely change the way I
have been setting this up?

Since I don't know how you've been setting it up, all I can say is
"probably so". You'ld use a Query selecting a particular test from the
tall-thin table, probably with a date range. This wouldn't be all that
different than selecting a fieldname from your wide-flat table, but
it's certainly not identical.

John W. Vinson[MVP]

 

[Guest]

Alright so I have the db setup the way you reccomended. I also went ahead
and linked spreadsheets when I import data.

I have this:

Runs
RunID
Date and Time
Fixture Code
Status Code
Part
Pallet

Tests
TestID
TestDescription

Status
Status Code
Status Description

Fixture
Fixture Code
Fixture Description

Results
RunID
TestID
Result

Everything was going good. Reduced the db size by 25% and everything. Ive
got another problem now though. When I run various queries to graph the data
it still takes forever to process. My graphs are usually line or bar graphs
where Ill have:
Date/Time on the x-axis
Result on the y-axis
Status Description, Test Description, Fixture Description, Part, Pallet as
filters.

Here's the SQL for it:

SELECT Runs.[Date and Time], Fixture.Fixture, Status.Status, Runs.Part,
Runs.Pallet, Tests.Test, Results.Result
FROM Tests INNER JOIN (Status INNER JOIN ((Fixture INNER JOIN Runs ON
Fixture.[Fixture Code] = Runs.Fixture) INNER JOIN Results ON Runs.ID =
Results.ID) ON Status.[Status Code] = Runs.[Status Code]) ON Tests.TestID =
Results.TestID;

Am I not doing my query correctly or something? Right now I only have 1
month in the database (at 22mb right now) yet access completely depletes my
512mb memory and my page file goes upwards of 1.5gb? What's going on?

Maybe if I had a better understanding on how access works with data, I could
be proactive in maybe coming up with my own solution. When running a query
what does access put in memory? Will attempting to split everything up
further into more tables help? Something like a table for every test? Is
there a basic rule for storing data so that access can work as quickly and
efficiently as possible?

Thanks again for putting up with me.

 

[John Vinson]

Here's the SQL for it:

SELECT Runs.[Date and Time], Fixture.Fixture, Status.Status, Runs.Part,
Runs.Pallet, Tests.Test, Results.Result
FROM Tests INNER JOIN (Status INNER JOIN ((Fixture INNER JOIN Runs ON
Fixture.[Fixture Code] = Runs.Fixture) INNER JOIN Results ON Runs.ID =
Results.ID) ON Status.[Status Code] = Runs.[Status Code]) ON Tests.TestID =
Results.TestID;

Am I not doing my query correctly or something? Right now I only have 1
month in the database (at 22mb right now) yet access completely depletes my
512mb memory and my page file goes upwards of 1.5gb? What's going on?

Probably you need to optimise the indexing on your tables. If you have
Primary Keys on the tables ([Fixture Code] in Fixture, ID in Runs,
[Status Code] in Status, [Test] in Tests), and have defined the
Relationships with referential integrity enforced, this query should
be OK. Any fields that you'll be using for search criteria or for
sorting should also be indexed.

Check out the performance FAQ at

http://www.granite.ab.ca/access/performancefaq.htm

for suggestions.

John W. Vinson[MVP]