Parts of DB

[rob p]

I have a large database. I created a query that runs a report. My report
only displays part of the database because of using criteria parameters in
the query.

Say the overall database has ten groups. If I select and display (in the
report) only group 5, is there a way to create a separate database made up
of only group 5 and still keep the original one too?


thanks.

 

[Gwen H]

There might be, but before I suggest a solution I need to understand why you
want to put Group 5 in a separate database?

 

[rob p]

Sure. That was a random example. Actually it is a county wide voters data
base. My "Group 5" would really be a precinct.

I would want to work on one precinct at a time. Since the whole database is
quite large, it would be beneficial if it could be broken up.

Also there might be other groupings so it wouldn't be as simple as just
segregating precinct numbers. (Townships, districts, etc.) And, it would
still be important to have the original DB available.

thanks
rob

 

[John Vinson]

Sure. That was a random example. Actually it is a county wide voters data
base. My "Group 5" would really be a precinct.

I would want to work on one precinct at a time. Since the whole database is
quite large, it would be beneficial if it could be broken up.

Also there might be other groupings so it wouldn't be as simple as just
segregating precinct numbers. (Townships, districts, etc.) And, it would
still be important to have the original DB available.

It sounds like you're using the term "database" to refer to a Table.
In Access jargon, a Database is the .mdb file "container" for multiple
tables, forms, queries and other objects. It would not be appropriate
(usually) to create another database unless this one is very large -
by which I mean approaching the 2 billion byte (tens of millions of
rows in the largest table) limit of Access databases. Unless you're
talking about Los Angeles County or the New York metropolitan area, I
suspect you're rather far from this limit.

If you have an index on each field that you wish to use to subset the
data (precinct number, township, etc.) then you're really much better
off just using Queries to select the records from the original table.
Storing multiple redundant tables (the main table AND lots of
independent precinct tables, say) is not necessary, will NOT make your
database smaller (much bigger, instead), will not improve efficiency
and is generally a bad idea.

If you ARE dealing with gigabytes of data... perhaps your jurisdiction
can afford a true client-server application such as SQL/Server.


John W. Vinson[MVP]

 

[TC]

Since the whole database is quite large, it would be beneficial if it could be broken up.

Why? What benefit do you perceive?

There is no benefit (that I can see) in "breaking up" a properly
designed database. If you want to restrict the records that you are
looking at, you do that with a filter or query. If the query is
unacceptably slow, this is noirmally due to an eror: tables not indexed
properly, or not primary-keyed correctly, or SQL not been written
efficiently.

HTH,
TC

 

[rob p]

Thanks. You're right: I meant table.
I could maybe create individual queries that themselves would only look at
certain groups of records?
rob

 

[Ed Warren]

By jove, I think you've got it!
That is exactly how a database works, stuff a bunch of data into a set of
(properly designed) tables, then pull out the bits and pieces you need, when
you need them, arrange them into a pleasing format, summarize as required
and wow! we have 'information'.
Cheers
Ed Warren.

 

[John Vinson]

Thanks. You're right: I meant table.
I could maybe create individual queries that themselves would only look at
certain groups of records?

Of course. That's what a query is FOR; such queries are the essential
basis of any Access application.

Do use "Parameter Queries" - for instance rather than a query with a
literal number 6 in the criteria line for PrecinctNumber, use

[Enter precinct number:]

or, better, use an unbound Form (frmCrit let's say) with a Combo Box
(cboPrecinct) selecting all the precincts, with a criterion

=[Forms]![frmCrit]![cboPrecinct]

Base another Form or Report on this query, and put a command button on
frmCrit to open it, and the user need not worry about the query design
window or query datasheets at all.

John W. Vinson[MVP]