Help needed with constructing a referral tracker

[Larry]

I am constructing a data base for tracking referrals with in my department
and have hit a little bit of a wall.

Here's what i have to work with.

There are 2 areas operations and phones. Our operations area processes
applications received by phone and from other areas. They use an access db to
track account opening progress and reporting.

I am making a new db for the phone area to track referrals sent to the
operations area and their progress once they are received there.

So I have linked the table from the operations db to my new one and have a
second table setup for associates to enter in their referrals on.

what i want to have access do is match up the names entered in the referral
table to the operations table and update a field in the referrals table as to
what the current status is on the application.

ex

rep a send john smith to officer b in the ops area for an application.

Rep a then logs john smith into the referral table
Officer B also starts their application entry in the operations table and
marks it as wip.

I need that wip status to then be reflected back onto Rep A's entry in the
referral table.

I would like to run it like this because not all referrals turn into an app
and thus would not be reflected on the operations table to be shown in a
query linking the names feilds of each table together as i have it now. So
i'm not getting a true number to output to excel for indepth reporting.

Any thoughts would be appreciated.

 

[Fred]

Larry,

You didn't give any specifics on your table structure or intended reporting,
so any answer would not be dealing much with your current databases.

If your data quality is equally good in your two areas, I would recommend
that these be recorded in a single table in a single database. It would have
fields that differentiate them in any way that you require. (i.e. just a
referral vs. one that has been processed in operations). Your reporting
(exporting as Excel or whatever) can be set up via filtering etc. to show
anthing that you want it to and only what you want it to.

If your data quality is different in the two areas, then you are probably
going to want two sets of data for each person, linked together, such as you
are saying that you want. Trying to automatically match them by their
names is probably a bad idea; there are too many ways that that could go
wrong. The most common wil be that if people don't enter the names exactly
the same there will be not match. You're probably best off with using (e.g.
social security #) or creating (e.g. applicant #) a unique identifier for
each person and then link based on that.

BTW you'll probably also have to clarify to yourself whether your tables are
recording people or instances of people applying. The acid test of that
would be how you handle it if the same person applies a second time later.


Fred

 

[Larry]

Thanks Fred for your reply.

Yes the data is not of equal quality and application numbers are not
generated until they hit the operations area and there can be no personal
data in this new data base so ssn is out of the question nore can we modify
the setup of the other. so linking by name is the only way it can be done and
country referred is the only way it can be done.

So this brings me back to my initial quandry. how can i run a query to
hopefully match the names and countries of the referral table to that of the
ops table and then update the status of that referral back to the referral
table.

I realize i can just export a query to excel for my reporting needs but that
would only fill half of the puzzle. There is additional information that will
need to be manually updated on the referral table. This additional
information needs to exported to excel at the same time for my pivot tables
to work correctly and everyones stats to be reflected properly.

 

[Fred]

My "Plan "B" was to assign a number to the perso/referral and do all linking
on that number.

Trying to link on names, countries etc. is going to be hit or miss. Small
variations in typing would caus the link to not occur.

Now we're getting to where other people would know this better than me. But
you could try creating a composite field (e.g.
[FirstName]&[Lastname]&[Country] in a query in each database and then link
the two composite fields together.