Creating Macros in Database for Data Entry

[obladi]

We have an Access Database where we store client info on the main form, and use a subform for referral information. There is much redundant information that has to be input for a number of clients. How can I write a macro to enter this info into each clients subform. I am somewhere around an intermediate user, so please keep that in mind, and thanks for any help you can provide. -John

 

[David R]

Depending on why you're storing 'redundant' information, there are a lot of possible solutions. The lowest tech one is to use Ctrl-' to draw down the value from the previous record.

If you're looking for something else, post back.

 

[obladi]

Thanks, David for the reply. In essence, this is my dilemna. I do employment services for a non-profit and send out weekly lists of available jobs (information and referral) to my clients. For purposes of reporting to our funders, I must track all referrals. We set up the database to do
this. Each week, I send a list of 75-100 jobs to 75-100 clients.. listing each job referral for each client (can be a few thousand data entry items per week) is taking time that should be better spent elsewhere, so I am looking for a better way. Maybe a different type of database is a better way, but we don't have a lot of funding, and I can't afford to hire someone to build another, so I thought that a macro might be the way to go!
Ideally, I would like to be able to run a macro which lists the job referral info into the case management record of each client, since I am providing basically the same info/jobs to many of the same folks. For example, I list about 100 white collar jobs and send them to my white collar worker clients. The next week, I send 100 more jobs to the clients.
Referral info is on a subform, which is linked to the main form.
Thanks for taking the time.. guess I should add my email to this page, too!
Have a good day.
johngore@laborscommunityagency.org

[This message has been edited by obladi (edited 06-06-2002).]

 

[David R]

Ahhh, gotcha! What you've got, if I'm reading correctly, is a many-to-many relationship: Any One Client can have Many Job Listings, and any One Job Listing can be sent to Many Clients.

You'll need three tables: tableClients, tableListings, and tablePostings.

tableClients lists all your client-specific information; company name, address, contact person, fax number, etc.

tableListings lists all of your joblisting-specific information; you know the fields better than I do.

tablePostings is the key that will make your life easier; it should have a field of the same type as your Primary Key from your Client table (Long Integer for Autonumber, Text for Text) and a field of the same type as your Primary Key for the Listings table. These are called Foreign Keys. Additionally because of your needs, this table should probably have at least two other fields: An autonumber field (Primary Key) of its own, and a date field that stores the date you mailed out that job listing to that business.

Now, you'll still be making 5000-10000 entries a week, but they will be one field entries, and we can probably even speed that up if you're sending every listing to every client, or at least a set of listings to a set of clients. That will take more time though. I would recommend you include a field in each of your 'main' tables that lists the 'type'; if you've got White-Collar clients and White-Collar jobs, Technical clients and Technical Jobs, Semiskilled clients and Semiskilled jobs, etc.

Right now you should spend some of that time you're going to save on data entry to read this article on "database normalization": http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100139

Post back if you have further questions. Subforms can be a little weird in a many-to-many relationship, basically you will be making a query that holds the Postings table and (whatever the side of the relationship is that is not on the main form), and using that as the record source for your subform.

 

[obladi]

Thank you, David, I'll look into that and if you hear muffled grumblings for the next few weeks, they're probably me.. buried under a mountain of new things to learn! I do appreciate your help.

 

[David R]

No problem, post back if you get thoroughly confused. I think I can show you how to get multiple entries done at once, but I want to make sure you have your data structured correctly first.