table size

  • Thread starter Thread starter rudwan
  • Start date Start date
Since Access stores all objects in a single (.mdb) file, what will knowing
the "size" of individual objects help you do?

If you want an approximation, you could make a copy of your .mdb file, check
its size, open it and remove an object, Compact and Repair, close it, and
re-check the size.
 
ok , i compacte it , the size become reduced .
but the question now is : when i can make compact and where
my file is splites to databse and interface , could i
compacte it weekly ? and is copmact
will affect my tables ?
 
When you split your application to front-end (interface) and back-end
(data), each will need to be compacted separately. The purpose of the
compact is to squeeze out unused space.

The Compact & Repair could fail part way through, leaving your application
unusable. Before running it, the general consensus in this 'group appears
to be to first make a backup copy.

It should not affect your tables (but see previous note).
 
look , u told me :
check its size, open it and remove an object, Compact and
Repair,
what do u mean by remove an objects ?
 
If your database has three tables and you wanted to know how large table #1
was, make the copy, check the size of the .mdb file, delete the table from
the copy, compact and repair, and re-check the size of the .mdb file.

Why do you care how large the objects are? What business need are you
trying to meet by knowing about the size?

Jeff Boyce
<Access MVP>
 
because i am dealing payment invoices , but this has to be
as monthly basis , i have 500 ID , each id will have 12
records per year ( as monthlt basis ) , so around 6000
records of table ( transactions ) for one year .
i am planning to make the transaction of each year in one
separate file , but the proplem is , in any time , and
during the current transaction may be i need to view all
transaction for one ID from year 2000 up to year 2004
in one report , not in separated reports .
 
It is unclear, to me, what you are asking for.

I wonder if you meant to say the number of rows in a table as opposed to the
actual filesize.

To get a list of all the Invoices for a particular Client you would run a
select query against the table using the Client Primary Key as a parameter.

You could also get a count by running a totals query with the same
parameter.

If you wanted a list of the number of Invoices for each Client the SQL would
read like this:

SELECT ClientInvoice.ClientReference, Count(ClientInvoice.ClientReference)
AS NumberOfInvoices
FROM ClientInvoice
GROUP BY ClientInvoice.ClientReference;

One for Client A would read:

SELECT ClientInvoice.ClientReference, Count(ClientInvoice.ClientReference)
AS NumberOfInvoices
FROM ClientInvoice
GROUP BY ClientInvoice.ClientReference
HAVING (((ClientInvoice.ClientReference)="A"));

A List for Client A would read:

SELECT ClientInvoice.ClientReference, ClientInvoice.InvoiceNumber
FROM ClientInvoice
WHERE (((ClientInvoice.ClientReference)="A"));

Forget the separate file idea and design a proper relational database, the
ClientReference table in the above example should contain ALL invoices.

If you insist on your approach you will need to understand how to link
tables and use the UNION operator in SQL.
 
Thanks for the clarifications. I agree with Craig's suggestions, now that I
have a better idea of what you are trying to accomplish.

I would point out that Access is quite capable of handling hundreds of
thousands of rows, in a well-normalized design (actual mileage may vary,
depending on the size of your rows -- but a well-normalized database
probably wouldn't need very large rows!).

Craig's suggestion of using a single table, rather than one-per-year, will
let you use Access' strengths as a relational database. If you use your
design, you (and Access) will have to work a lot harder ... a little like
trying to drive nails with a chain saw.
 
HELLO
u miss understand me , i know how to sort and call invoices
by id , but my proplem is as this example ,
ID=8
total transactions for it in 2000 = 12 records
total transactions for it in 2001 = 12 records
total transactions for it in 2002 = 12 records
total transactions for it in 2003 = 12 records
total transactions for it in 2004 = 12 records

as done in accounting systems , they reserves each year in
one separate file to save using memory , i want to save
each year transactions in one separate file , then to call
it through code when requered previous years
 
Without a better description of how your data is structured, I've reached
the limits of my understanding and ability to help.
 
jeff , it is clear ,
forget all about what i post before , now suppose i am
dealing accounting database , there are so many tables
table1,table2,table3,......,etc
now we would like one table only "accounts transactions"
to be stored as yearly basis in one separate file
for example , 2001 transactions must be saved in separate
table called 2001 , also 2002 , 2003 , ...etc
that is all
 
Back
Top