General discussion

Locked

Add multiple record to Access database

By jarodl9 ·
I have set up a basic Access 2000 database for our company for recording serial numbers of the products that we sell. We record what product was sold, who it was sold to, etc. We also create a record for each repair done to a unit when it is returned for repairs.
Many times we sell 50 to 100 of one product to the same client with a sequential range of serial numbers. Currently, I have to enter a separate record for each serial number individually, when all the information for the group of units is the same (except for serial number). Is there a way I can enter multiple records at the same time, for a range of serial numbers? I realize this would probably take some VBA programming, which I am not terribly familiar with, so if your solution involves VBA, please include as much detail as possible. Thanks.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by John Gordon In reply to Add multiple record to Ac ...

This is not an elegant solution but it works and it is non-VBA.

Type the data into an Excel spreadsheet then copy it into Access as follows:

1. Type the first set of data into a spreadsheet. Make sure that the number of columns corresponds to the number of fields in your destination table in the database, including any autonumber field, and that you type in tha data in the same order as it appears in Access.

2. Use copy and paste to fill in the fields that are repeated - use autofill after you have pasted 3 rows and the rest can be filled quickly.

3. Check that each row is the same and that no numbers or dates have been accidentally incremented during the autofill. (check out Excel's help on autofill if necessary).

4. Type in the first three serial numbers and then use autofill for the rest. Check that the numbers have incremented and that the last serial number is as expected.

5. Select all of the cells in the spreadsheet that you have just filled in and copy them.

6. Open the table in Access, select a whole blank record (Click on the record selector at the left hand side) and paste the data from the spreadsheet.

7. Check to see that the data has been copied correctly.

Post back here if you have any problems and I'll try to help.

Collapse -

by mvegas In reply to Add multiple record to Ac ...

Reconstruct the database with unique data stored in separate tables. Your database would be larger but much more scalable. You would also eliminate redundancies in your database. For data analysis your could just run a query across the whole database or joined tables. For example, each client has their own unique data so create a table to store only client data. Client ID, Company name, contact name, address info?etc. Then have a separate table specifically for products. Product name, product price, product description?etc. Perhaps you could even include a table only for Serial Numbers. The table would have foreign keys linked to the ClientID and product ID. Yet it could also include a registration date?This would allow you to go into your table and simply enter a serial number, assign it a productID, and attach it to a ClientID. My point is that you can dramatically enhance your productivity by taking the time to logically map out your business processes. Aim to avoid duplicating steps and create a single-data source with unique data in its own table. This will make your data management much more scalable for future ideas you might not yet think of. It also optimizes your database for analysis.

I guess there might be another solution, simple initially. Yet reconstructing your database would save you time and money in the long run. Weight the Pros vs. Cons and decide which solution would best meet your business objectives.

Collapse -

by jdlp In reply to Add multiple record to Ac ...

I just completed the same process, follow this:

1. Open your DB
2. export the table that your trying to fill, save it as an Excel(.xls) file
3. Using Excel populate this Spreadsheet with all the info you need, making sure it applies to any rules you set in Access,
4. Save this as a .csv file
5. Within Access, use File>Get External data>Import to add the info
6. follow the on-screen commands, set the first line as the row names, and then just choose to add data into the correct Table

Job done!! Hope this helps

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums