How can I match up records from two tables and add information to

[Kerry]

I have two tables in my database. One keeps track of employees who have
special insurance enrollments. The other is a listing of all employees whose
insurance coverage has ended (that information is run off of our system, and
I import it to Access).

What I need to do is for each employee in the first table who has a coverage
end date in the other table, add that coverage end date to his record, if
possible, and list the record in a new query so I can run a mail merge letter
off of it.

Would this be possible? I'm trying to automate this process as much as
possible so someone else can take it over.

A couple of notations... I must have both tables. I cannot upload employee
information from our system into my database. I also know my way around
Access, but I am definitely not a guru... so please be gentle with your
response!

Thanks very much for any help.

Kerry

 

[Vincent Johns]

I hope I'm not missing something here, but my guess is that in both
Tables you have a field (such as a badge number) that uniquely
identifies each employee. (Names might not be unique.)

If so, all that I think you need is to write a Query in which you JOIN
your two Tables, linking their identification numbers, and include the
fields you need from the two Tables. Use that Query as the basis for
your mail-merge.

Since this seems pretty straightforward, perhaps I'm missing something.
For example, you won't need to "add that ... date to his record",
since it will already be available in your Query as a field.

If you'd like a specific example, or if the Query I suggest won't work,
perhaps you could list a couple of matching records (sanitized to alter
sensitive personal information) from each of the two Tables, along with
an example of what you'd like the output to look like.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Kerry]

Actually, I had not thought of that... my management may not like that
solution, but it will work. Less work for everyone involved...

Thanks very much for your help!

 

[Vincent Johns]

Kerry,

Well, sometimes management has a good reason for not liking
something. Assuming that my suggested Query will work, why would your
boss dislike it? Perhaps there's a way to improve the Query so that it
will work AND be acceptable to management. (... AND be easy for your
successor to maintain. That would be good, too, if it's feasible.)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Kerry]

Hi Vincent!

I think probably because everyone is used to having all the data in one
place, i.e. one record for each instance of special enrollment. That's all.
We're all used to working off of a massive Excel spreadsheet. And I mean
MASSIVE. (Over 4000 records) So all they have to do is go search out the
record and there it is, with everything.

It's just a matter of shifting paradigms.

K

 

[John Vinson]

Hi Vincent!

I think probably because everyone is used to having all the data in one
place, i.e. one record for each instance of special enrollment. That's all.
We're all used to working off of a massive Excel spreadsheet. And I mean
MASSIVE. (Over 4000 records) So all they have to do is go search out the
record and there it is, with everything.

It's just a matter of shifting paradigms.

Certainly. 4000 rows is a pretty big spreadsheet. It's a TINY database
table. 4,000,000 rows is getting to be largish for an Access table!

Excel is a spreadsheet; Access is a database. You can use Excel as if
it were a database... but you can drive nails with a crescent wrench,
too. Doesn't make it a hammer!

John W. Vinson[MVP]