• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

coding for employer's name

Hi Experts,
I have a question about to print a fprm base on the employer's name.  currently we have a employer table, and users enter the employer sometime different even it's the same name.  for example, some users enter AT & T, some enter as AT&T, some enter AT & T Corp, some enter AT&T corp etc.  What I need to do is if the employer is AT & T then only the form prints out no envelope, if the employer is not AT & T then prints out the form and envelopes.  Here is my code:

if Empl = left ([Empl],6) = "AT & T" or Left([Empl],4) = "AT&T" then
   forms
else
  forms
  envelope
end if

if Empl = left ([Empl],6) = "AT & T" or Left([Empl],4) = "AT&T" then (I want to pull anthing that has AT& T in the begining, is this correct?   because when I use this, even the employer is not AT & T, the envelope did not print)

Thanks,
0
urjudo
Asked:
urjudo
  • 8
  • 5
  • 2
  • +1
3 Solutions
 
Rey Obrero (Capricorn1)Commented:
try this

if left([Empl],6) = "AT & T" or Left([Empl],4) = "AT&T" then
 
' forms

else
 ' forms
  'envelope
end if
0
 
PatHartmanCommented:
Why not change the way you do data entry to use a combo instead?  That way you get rid of the variations.
0
 
urjudoAuthor Commented:
Sorry Rey Obrero, I tried your suggest, for some reason, it still not printing out the envelope if I have two employers in the same record, one is At & T and other one is different employer, the form prints out fine, but the envelope is not print out for the one that is not AT & T.  this is really strange.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rey Obrero (Capricorn1)Commented:
<if I have two employers in the same record, one is At & T and other one is different employer,>

how did it happen that you have two employer in the same record?

how are the data recorded in the [Empl] field?
0
 
urjudoAuthor Commented:
some time the guy has two different job, one is full time job, one is part time job, we have a special# like the SSN for each client, we also have a employer table that has each client's #, for example, John Doe, his special# store in client table is 012567892, on the employer table, we have his special#012567892, has employer code 225, 312  (225 is the auto# of the employer that we enter in a empolyer code for At& T, and 312 is the auto# for Mcdonald's)
I hope I explain clear
0
 
Rey Obrero (Capricorn1)Commented:
you should have a single unique record  for an  employee, employer combination.
0
 
urjudoAuthor Commented:
I have to show the employer's name and address on the form, so if the client has two employers, the there will be two forms print out, one for each employer
0
 
urjudoAuthor Commented:
This works fine until now we have to skip the envelope if the employer is At & T
0
 
Rey Obrero (Capricorn1)Commented:
Now, you are starting to see the effect of not having a unique record for employee, employer combination.
0
 
urjudoAuthor Commented:
how do I have a single unique record  for an  employee, employer combination?
0
 
urjudoAuthor Commented:
Do you think I should use "Loop" option (But I don't know how).  I check the query, the AT& T is the last update, so it show on the first  and the other employer show second on the query, that why the system sees the first is the At& T and did not see the second employer, so the second employer envelope did not print
0
 
Rey Obrero (Capricorn1)Commented:
SSN                 employer code
012567892    225      ' this is one unique record for 012567892 and AT & T
012567892    312      'this is another unique record for  012567892 and Mcdonald
0
 
urjudoAuthor Commented:
That's how we store in the emplyer table
0
 
Helen FeddemaCommented:
You should have an ID field for the employer, and instruct users to not add a new record when there is a change -- update the current employer record instead.  You clearly have a lot of near-duplicates, which need to be cleaned up.  Then you can use a combo box to select the employer, with the ID in column 0 (usually not displayed), and the name in column 1.  When there is a reference to the employer in another table, it should use a foreign key field containing the employer ID, not the name.
0
 
urjudoAuthor Commented:
we do, here is the designed:
SPTable: SP#(PK), SPLN, SPFN, SPSSN, SPDOB
Client Table: clientID(auto#), case#, ClientSP#
EmployerCodeTable:  EmployerCode(Auto#), Employer name, employer address
Employer Table: EmployerID(Auto#), ClientSP#, EmployerCode
so the relationship is
ClientSP# ---> SP# (Pull client)ame)
to pull all informations:
ClientSP#--->SP#
ClientSP#(client Table)--->ClientSP#(Employer Table) --->EmployerCode(Employer Table)--->EmployerCode(EmployerCode Table).

One of the problem that I notice is when users enter the employer, they did not enter in a same format, for example, some enter as AT&T, some enter as A t & T etc, I think I should fix them and make them the same format
0
 
Helen FeddemaCommented:
They should only have to enter the employer name once -- in the Employers table (or whatever is it called in your app).  Anywhere else the name is needed, it should be pulled from the Employers table via the ID link, and displayed in a locked control on a form.  That way it will always be the same, and if it was entered incorrectly, it only needs to be changed once, in the Employers table.

If you are seeing different versions of the employer name, that means that the name itself is stored somewhere other than the Employers table.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now