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
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