Question How To Get A Sample Of A Database

[Hanna]

Evening!
We have a database which has around 500,000 records on it, around a dozen tables, and half a dozen forms.

I've been asked to get a sample of the database (comprising 100 or so records) that includes all of the structure from the tables, forms & records.

Is there any way to do this??!!! :banghead:

Many thanks

 

[MarkK]

Yeah, copy the database. Then delete records from the copy until there are only 100 or so records left.

 

[Hanna]

Hi many thanks for coming back...
But my problem is how to pick the 100 records that are going to still make the forms and reports make sense and have data to pick up rather the there just being blank after blank.
(There's a ton of reports, forms and data, and I haven't a clue how to cherry pick which records to pick to make them all have data to display - rather than having a load of blanks!)
Is there a way of doing this?
Many thanks (again)

 

[MarkK]

But my problem is how to pick the 100 records that are going to still make the forms and reports make sense

So you are posting about it on a forum, but I ask you: How can anyone who hasn't seen the data help you with that?

Open the forms and reports and if they show useful data, make a note of what data they show. Then, don't delete that data. Maybe you see certain customers and their orders. Note those customers, note those order numbers, and don't delete the ones you noted.

Makes sense?

 

[gemma-the-husky]

why not create a few records to give you what you want?

 

[jdraw]

Hanna,
I think Mark and Dave have given you some options. Can you tell us what the database is about (subject matter Customers, Orders --Students, Classes....) in general terms? If you have a data model, ( database relationships diagram) perhaps you could make a jpg of it and post (in zip format).

As Mark said - note the customers, note the order numbers and don't delete them.

The reality is that there are relationships between/among tables and you need to keep the related records in those related tables.

 

[Hanna]

Many thanks one and all!

(The database itself is a mixture of quotes in from suppliers, quotes out to customers, and all of the alternative part references used in quoting out the parts. Altogether it's a real mish mash of tables, reports and forms - which would take ages to try and get across)

A colleague just emailed to say run half a dozen records that do actually populate the fields in the main reports.
Then delete all other records off the tables.
This sounds like it man be worth giving it a go!?

 

[Mihail]

A well designed database has a lot of relationships that maintain the Referential Integrity and allow to Cascade Delete Related Records.
If this is your case, you are a lucky man because you have to remove records from the most left table and Access will automatically remove (in cascade) the related records from your database.
No worry for Forms, Reports or Queries. This objects have nothing to do with the records.
You will find it in your database even if you remove all the records.

How to choose the records to be deleted ?
That is another story.

For a certain table:
Design a SELECT query, based on that table.
In the Criteria row write conditions in order to select those records you wish to remove/keep.
Run this query until the result satisfied you.
Save the query under a suggestive name.
If you have designated the query to show you what records to keep, invert all conditions from the Criteria row. (Ex: <> "Smith" change to ="Smith" .
Transform this query into a Delete Query.
Run it.

CAUTION !!!!!!!!!! This action can't be undone !!!!!!!!

My advice is that before you start to do anything, copy your database on an external device (CD, DVD) or/and on another computer.

Good luck !

 

[Mihail]

More:
Keep tracks: Save your database under a new name (perhaps using the date and time in the name) after each deletion that satisfy your need.
This way you avoid to start from scratch if something is going wrong at step... 7 (to say).

 

[Hanna]

That's brilliant one and all, many thanks for your help!