Map a person to an Account

[FrankTimJr]

I'm attempting to design a database that will map a Sales person to their
respecitve accounts and / or territories. I can perform this action in
Excel, but it takes far too long and many manual steps to get it right and
I'm wondering if Access can help with it.

There are three levels of mapping, the first level is the Account Number,
the second is the Account Name (if there is no match on the Account Number,
and the third is matching against Zip Codes if neither of the above two
steps do not find a matching Sales person.

Is it at all possible to get Access to do this in one query?

To summarize, Map a Sales Person to an Account Name, if no match, map
against the Account Name, if no match, map against Zip Code, and finally, if
all three do not get a match, I'd like to spit that out somehow as well.

Any ideas??

 

[tina]

can you explain what you mean by "map"?
presumably you have a table that lists all salespeople, and a table that
lists all accounts and includes account number and account name fields, and
possibly address fields as well, including a ZipCode field. if each account
may have only one sales person assigned to it, then you would have a foreign
key field in the accounts table also, which would link to the primary key
field of the salespeople table, as

tblSalesPeople
PersonID (primary key)
FirstName
LastName
(other fields that describe a sales person)

tblAccounts
AccountNumber (primary key)
AccountName
PersonID (foreign key from tblSalesPeople)
(other fields that describe a sales person)

the relationship would be tblSalesPeople.PersonID 1:n tblAccounts.PersonID

i'm guessing that the above information does not answer your question, but
hopefully it gives you an idea of how the tables are related in Access.
since Access and Excel handle data *very* differently, can you explain more
fully what you're trying to do? then perhaps we can help you craft a
solution that works in Access.

hth

 

[FrankTimJr]

Yes, the "map" I'm referring to means (and I forgot to include this in my
original message) is, every week I receive a data dump from an Orders
database. The Orders database unfortunately does not have Sales Rep tied to
each order (that would be too logical!). Part of my job is to map or link
each order with a Sales Rep. Some reps manage accounts all over the
country, where other Sales Reps manage a set of Zip Codes. I have both the
SalesPeople table, and the Account mapping table already set up all with the
assigned Sales Reps, both by Account Number, Account Name and Zip Codes.

I just need to figure out how to marry the Account mapping table with the
Orders table, but, using three different lookups as I stated in my original
message.

tbl_AccountMapping
AccountNumber (primary key)
AccountName
<All address fields, inlcuding Zip Code)
PersonID

tbl_Orders
OrderNumber(primary key)
AccountNumber
AccountName
ZipCode
<all other order detail related fields>

I want to first lookup on AccountNumber, then, all "non-matches" (where the
lookup does not locate a SalesRep from tbl_AccountMapping) use AccountName
instead, then, all additional "non-matches", use the Zip Code, and lastly,
there are some zip codes that are not assigned to a Sales Rep, I need to see
those as well, but not sure where to map them yet...perhaps if there was a
default SalesRep (a ficticious SalesRep) that everything else would get
mapped to, that might solve it.

Hope this makes it more clear on what I want to accomplish. Again, I
perform all the above actions in Excel, but it takes me one business day to
finish the entire Order Mapping (and this happens every week).

I appreciate any assistance.
Thanks,
Frank

 

[FrankTimJr]

I honestly know how it is determined. That is done compeltly outside of
what I'm trying to accomplish here.

 

[Chris2]

I'm attempting to design a database that will map a Sales person to their
respecitve accounts and / or territories. I can perform this

action in

FrankTimJr,

How are you deciding what a sales person's accounts or territories
are?

There are three levels of mapping, the first level is the Account Number,
the second is the Account Name (if there is no match on the Account Number,
and the third is matching against Zip Codes if neither of the above two
steps do not find a matching Sales person.

"if neither of the above two steps do not find" . . . is that "not"
a typo? Because if it isn't, it negates the conditions.

Is it at all possible to get Access to do this in one query?

We need to know more (see below).

To summarize, Map a Sales Person to an Account Name,

How? What attribute of a Sales Person matches an Account Name?

if no match, map against the Account Name,

Deja Vu? Or was the first one supposed to be Account Number? I
am not seeing how to match Sales Person to either.

if no match, map against Zip Code, and finally, if

Do you have another table called Territories?

Territories:
SalesPersonID
ZipCode5
ZipCode4

Or something like that?

all three do not get a match, I'd like to spit that out somehow as well.

Any ideas??

Although meant for an SQL Server newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.

Note: Please do not copy and paste information
directly from MS Access' Documenter. It is
virtually unreadable. Please distill down and
legibly format only the relevant table informa-
tion. The ideal is to see actual DDL, and if
you can write it, please do.


Sincerely,

Chris O.

 

[tina]

okay, thanks for the additional info. off the top of my head, and before we
get into trying complex solutions, how about this: add a field to
tbl_Orders to hold the PersonID (unless the field already exists in the
table, of course). write an action query to first match the AccountNumber
fields, something like

UPDATE tbl_Orders INNER JOIN tbl_AccountMapping ON tbl_Orders.AccountNumber
= tbl_AccountMapping.AccountNumber SET tbl_Orders.PersonID =
tbl_AccountMapping.PersonID;

that should take care of the AccountNumber matches. next, write an action
query to handle the AccountName, as

UPDATE tbl_Orders INNER JOIN tbl_AccountMapping ON tbl_Orders.AccountName =
tbl_AccountMapping.AccountName SET tbl_Orders.PersonID =
tbl_AccountMapping.PersonID WHERE tbl_Orders.PersonID Is Null;

that should take care of AccountName matches that weren't already matched by
AccountNumber. next, write another action query to handle ZipCode matches,
as

UPDATE tbl_Orders INNER JOIN tbl_AccountMapping ON tbl_Orders.ZipCode =
tbl_AccountMapping.ZipCode SET tbl_Orders.PersonID =
tbl_AccountMapping.PersonID WHERE tbl_Orders.PersonID Is Null;

finally, write a Select query to pull all "un-mapped" records, as

SELECT * FROM tbl_Orders WHERE PersonID Is Null;

frankly, i can't really figure out how tbl_AccountMapping works, but i'm
assuming it makes more sense to you (who can see the whole picture) than it
does to me. so i suggest you try using the Update queries and Select query
on a COPY of your database, and post back if you don't get the results
you're after.

hth

 

[Tom Ellison]

Dear Frank, Tim, and Jr:

I recommend that, within the finished database, you should have a single
relationship defined that assigns each account to a rep. The rules you
mention should be applied to create that relationship.

When new accounts are imported, they should be placed in a temporary table
which includes a column for the key to the sales rep table. This column may
be left NULL at first. You can then perform all the rules you wish to apply
to assign a sales rep, in whatever order you wish, before appending this
into the finished table. Indeed, there may be accounts which do not receive
a sales rep through the use of the rules, and these can be fixed up manually
or by using new rules you create to perform that task.

Your finished relationships in the working tables of your application should
not suffer from uncertainty, ambiguity, or extensive processing to determine
who the rep is. This should be performed before the data gets into the
finished, working state.

Tom Ellison

 

[Chris2]

I honestly know how it is determined. That is done compeltly outside of
what I'm trying to accomplish here.

FrankTimJr,

Yes, your other post arrived on my server just after I posted mine,
so I know what's up now.


Sincerely,

Chris O.