Re: Map a person to an Account

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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
"tina" <nospam@xxxxxxxxxxx> wrote in message
news:_z2nf.266961$zb5.43264@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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" <Frankie69_98@xxxxxxxxx> wrote in message
> news:ycqdnejSRZEJFQHenZ2dnUVZ_sCdnZ2d@xxxxxxxxxxxxxx
>> 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??
>>
>>
>
>


.

Relevant Pages

  • Re: Map a person to an Account
    ... >> respecitve accounts and / or territories. ... > How are you deciding what a sales person's accounts or territories ... >> There are three levels of mapping, the first level is the Account ... >> To summarize, Map a Sales Person to an Account Name, ...
    (microsoft.public.access.queries)
  • Re: Map a person to an Account
    ... How are you deciding what a sales person's accounts or territories ... > There are three levels of mapping, the first level is the Account ... > To summarize, Map a Sales Person to an Account Name, ...
    (microsoft.public.access.queries)
  • Re: Map a person to an Account
    ... relationship defined that assigns each account to a rep. ... which includes a column for the key to the sales rep table. ... the "map" I'm referring to means (and I forgot to include this in my ... >> key field in the accounts table also, which would link to the primary key ...
    (microsoft.public.access.queries)
  • Re: Map a person to an Account
    ... The Orders database unfortunately does not have Sales Rep tied ... Part of my job is to map or link ... > assigned Sales Reps, both by Account Number, Account Name and Zip Codes. ...
    (microsoft.public.access.queries)
  • Auditing a Domain from a Guest Account
    ... Ecora to whip through the system and print out a nice map in Visio. ... and browse through User Manager for Domains and Server ... the account it uses an account with only user/guest access. ...
    (comp.os.ms-windows.nt.admin.security)