records size growing bigger and bigger

G

Guest

I have a sample room database [SAMROOM] keeps records of all fabrics rolls,
having fields like Roll-I/D, quantity, Obsolete, etc.

Then I have a sample order database [SAMORDER] keeps records of sample
withdrawal details for each withdrawal, having fields like : Order-I/D,
Sample-I/D, Date, Withdraw-Quantity, etc.

When ever there is new sample role taken to the sample room, a record for
this roll will be entered into [SAMROOM]

To keep knowing balance of each roll in [SAMROOM], I do the following :

Make a query [SAMORDERSUM] base on [SAMRODER] to return Sum of withdrawals
for each Sample-I/D;
Make a query [SAMBAL] base on [SAMROOM] and [SAMORDERSUM] such that for each
SAMPLE-I/D, deduct [SAMROOM][QUANTITY] by [SAMORDERSUM] returns the balance
for each roll.

When there is a Roll-I/D that has a zero balance, I will put "yes" in the
[OBSOLETE] field for this item in the [SAMROOM] database.

Now the number of records in my database is growing fast,
and most of the records in [SAMROOM] already zero in balance;
and for most records in [SAMORDER], the relevant Roll-I/D already fully
withdrawn with zero balance.

The problem is
Everytime I want to know the Roll-I/D balance,
by running the queries above said,
the program will compute on all records in each table,
but in fact most of the rolls might already "obsoleted"
hence much calculation job becomes un-necessary and a matter of waste of time.
which might also hamper the program's efficiency.

Also the number of records [SAMROOM] is growing fast,
but most of them already zero balance

How can I deal this this kind of situation?


Best regards
Simon Wong
 
J

Joseph Meehan

Simon said:
I have a sample room database [SAMROOM] keeps records of all fabrics
rolls, having fields like Roll-I/D, quantity, Obsolete, etc.

Then I have a sample order database [SAMORDER] keeps records of sample
withdrawal details for each withdrawal, having fields like :
Order-I/D, Sample-I/D, Date, Withdraw-Quantity, etc.

When ever there is new sample role taken to the sample room, a record
for this roll will be entered into [SAMROOM]

To keep knowing balance of each roll in [SAMROOM], I do the following
:

Make a query [SAMORDERSUM] base on [SAMRODER] to return Sum of
withdrawals for each Sample-I/D;
Make a query [SAMBAL] base on [SAMROOM] and [SAMORDERSUM] such that
for each SAMPLE-I/D, deduct [SAMROOM][QUANTITY] by [SAMORDERSUM]
returns the balance for each roll.

When there is a Roll-I/D that has a zero balance, I will put "yes" in
the [OBSOLETE] field for this item in the [SAMROOM] database.

Now the number of records in my database is growing fast,
and most of the records in [SAMROOM] already zero in balance;
and for most records in [SAMORDER], the relevant Roll-I/D already
fully withdrawn with zero balance.

The problem is
Everytime I want to know the Roll-I/D balance,
by running the queries above said,
the program will compute on all records in each table,
but in fact most of the rolls might already "obsoleted"
hence much calculation job becomes un-necessary and a matter of waste
of time. which might also hamper the program's efficiency.

Also the number of records [SAMROOM] is growing fast,
but most of them already zero balance

How can I deal this this kind of situation?


Best regards
Simon Wong

Current version of Access can be as large as 2 gig. I doubt if you are
close. Generally I suggest you keep all your data. Is the time to run your
calculations running too long? Maybe that can be improved on.

You might want to make some arrangement to mark records that have zeroed
out to archive. You would do this by adding an archive filed (yes/no type)
to each table and using an update query to set it and then filter out all
those records that are archived from any calculation.

Without knowing more it is difficult to make specific suggestions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top