How can I downfill data in Tables 2

[Cellarman]

I have a need to repeat a lot of data in a large database. I would like to highlight a field and have this data repeated in the following number of records, sometimes up to 100. In MS Excel you can grab a handle in a cell and drag it down through the required number of records which would be ideal, but Access doesn't do this. Any ideas?

 

[nberryman]

Take a look at Append Queries, if you can find the records you can append data to the required field



Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[willir]

Cellarman

Two thoughts...

Is your database properly normalized? Specifically, why do you have so much repeating data? I realize in some cases, data will repeat itself, but I have seen far too many designs that requried repetitive entries because the design was not properly normalized. A simple example would be the customer address on invoices - since the address would exist in the customer table or an address table, the invoice would only have to reference the invoice via the foreign key vs entering the address on each invoice. This would be akin to referencing a cell in Excel with a formula instead of copying the cell.

Second, you can not cut and paste in Access as in Excel. But you can create an append query, as correctly indicated by Neil, or use VBA programming to populate records. For example, I will use VBA to populate a survey sheet or check list in this manner. How you do this will obviously depend on your data and your needs.

Richard

 

[Cellarman]

Thanks Neil and Richard
I will try append query. Repeated data is necessary as I have a gazateer database where for example there may be 100 town entered at any one time and the country field is the same for each one. There are several instances of this throughout the database

 

[willir]

Cellarman

Good luck with the append query.

By gazateer, I gather you mean longitude and latitude entries -- pretty tedious stuff.

You may want to try something like this...

Have a table for coutries. Use an unbound combo box to select the country. The combo box pulls the data you need to set for the data entry. Then for data entry, your code test if a value is in the combo box. If so, the data is used to populate the relavent fields and set the focus to the next field. If no data is loaded in the country combo box, then no entries are made, and data entry starts at the country field.

This way, you can control the default settings regardless of fiver or fifty entries.

You could use the same apporach for cities with a second unbound combo box.

Richard