Question about Creating a Project for reporting only

[Guest]

Hopefully this makes some sense. What I have is one large table of data for
each year, for each client. Within each year’s table has all of their
production records, soil fertility, soil types, field names, crop yields, and
so on. I have the same table structure for each client. I would like to make
an access project that allows me to query through the data and create a
report such as, “What is the average fertility of each land parcel?†And get
a report of it and so on. I would like to filter the query to include only
one client, or all 50 on the report.

Am I better off having a separate table for each client, or combining all
clients into one huge table? Is there a way to link the tables together for
querying across all clients if they are not in the same table?

If so, once I have a query designed, is there a way I can filter it to only
show which client(s) to include on the report?

I do have office '07 if that affects any options.

Thanks much!

 

[Norman Yuan]

There is not point to have tables of the same structure for each client and
each year, unless the data for each client of each year is so much that
makes the *.mdb file reaches its limits (2GB, which could hold huge amount
of record, millions, to say the least). How large the table you consider it
is "large", for a client and a year? If you have that huge data (each client
of each year for 2GB), you may consider use other type of database, SQL
Server/MySQL,Oracle..., rather than Access, before you going ahead to
considering reporting.

 

[Guest]

Thanks for the reply. Is the limit in Access 2007 still 2 million records, or
is it now based on the size (2GB) of the tables?

 

[Norman Yuan]

Not sure for Access2007. 2GB is the *.mdb file size (Access2000-2003). There
is no limit on how many records a table could contain, nor is there a limit
on the size of table, as long as the file size is leass than 2GB.

 

[John W. Vinson]

Is the limit in Access 2007 still 2 million records, or
is it now based on the size (2GB) of the tables?

There has never been a 2 million record limit; the limit has always been the
size of the database. A single table will hold more records under the 2Gbyte
limit than multiple tables (because there'll only be one set of table-overhead
space used).

John W. Vinson [MVP]