Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

strings in a query

Discussion in 'Information Technology' started by jan, Jul 28, 2006.

Tags:
  1. jan

    jan
    Expand Collapse
    Guest

    I am running Access 2003 on WIN XP.

    I have a query that shows all my current employees, their titles, and other
    information. The query is used to create an Organization Chart with Visio.

    In some cases, it is appropriate to abbreviate their job title, in some
    cases it is not. For example, abbreviating Manger would give me an M;
    abbreviating Maintenance Worker 2 would give me MW2. The later is fine, the
    former is not.

    How do I, in my Access query, show that Jack Jones is a Manager and Joe
    Johnson is an MW2?

    I'm fairly certain this involves a formula. I stink at formulas.

    Thanks in advance,

    Jan
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    If you have lots of titles a formula isn't the best answer. The simplest
    (which is normally the best) method would be to create a new table [tblNew]
    TitleID AutoNumber
    Job Text
    Abbreviation Text

    Job titles would be Manager, Assistant, Tea Maker (or in the USA Coffee
    maker), etc

    Abbreviation would be M, As, TM (or CM), etc.

    Then simple link this new table with the primary and insert a lookup field
    in the employees record. You can refer to columns, etc

    Hope this helps (it's much better than loads of IIF's or ='s)



    --
    Wayne
    Manchester, England.



    "jan" wrote:

    > I am running Access 2003 on WIN XP.
    >
    > I have a query that shows all my current employees, their titles, and other
    > information. The query is used to create an Organization Chart with Visio.
    >
    > In some cases, it is appropriate to abbreviate their job title, in some
    > cases it is not. For example, abbreviating Manger would give me an M;
    > abbreviating Maintenance Worker 2 would give me MW2. The later is fine, the
    > former is not.
    >
    > How do I, in my Access query, show that Jack Jones is a Manager and Joe
    > Johnson is an MW2?
    >
    > I'm fairly certain this involves a formula. I stink at formulas.
    >
    > Thanks in advance,
    >
    > Jan
    >
    >
    >
    >
    >
    >
     
  4. jan

    jan
    Expand Collapse
    Guest

    Wayne,

    Thanks for the advice. I have a "titles table" with about twelve records of
    two fields each (job code and title). I will add an abbreviation field and
    see how that works.

    Jan

    "Wayne-I-M" <WayneIM@discussions.microsoft.com> wrote in message
    news:2DE1544A-FEAF-404F-8382-06C6AFE12839@microsoft.com...
    > If you have lots of titles a formula isn't the best answer. The simplest
    > (which is normally the best) method would be to create a new table
    > [tblNew]
    > TitleID AutoNumber
    > Job Text
    > Abbreviation Text
    >
    > Job titles would be Manager, Assistant, Tea Maker (or in the USA Coffee
    > maker), etc
    >
    > Abbreviation would be M, As, TM (or CM), etc.
    >
    > Then simple link this new table with the primary and insert a lookup field
    > in the employees record. You can refer to columns, etc
    >
    > Hope this helps (it's much better than loads of IIF's or ='s)
    >
    >
    >
    > --
    > Wayne
    > Manchester, England.
    >
    >
    >
    > "jan" wrote:
    >
    >> I am running Access 2003 on WIN XP.
    >>
    >> I have a query that shows all my current employees, their titles, and
    >> other
    >> information. The query is used to create an Organization Chart with
    >> Visio.
    >>
    >> In some cases, it is appropriate to abbreviate their job title, in some
    >> cases it is not. For example, abbreviating Manger would give me an M;
    >> abbreviating Maintenance Worker 2 would give me MW2. The later is fine,
    >> the
    >> former is not.
    >>
    >> How do I, in my Access query, show that Jack Jones is a Manager and Joe
    >> Johnson is an MW2?
    >>
    >> I'm fairly certain this involves a formula. I stink at formulas.
    >>
    >> Thanks in advance,
    >>
    >> Jan
    >>
    >>
    >>
    >>
    >>
    >>
     
  5. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    If would be best to create the new table first and then simply open the
    orginal table in design and create a new field as a lookup in [tblTitles]
    and then select the new table as the source.

    --
    Wayne
    Manchester, England.



    "jan" wrote:

    > Wayne,
    >
    > Thanks for the advice. I have a "titles table" with about twelve records of
    > two fields each (job code and title). I will add an abbreviation field and
    > see how that works.
    >
    > Jan
    >
    > "Wayne-I-M" <WayneIM@discussions.microsoft.com> wrote in message
    > news:2DE1544A-FEAF-404F-8382-06C6AFE12839@microsoft.com...
    > > If you have lots of titles a formula isn't the best answer. The simplest
    > > (which is normally the best) method would be to create a new table
    > > [tblNew]
    > > TitleID AutoNumber
    > > Job Text
    > > Abbreviation Text
    > >
    > > Job titles would be Manager, Assistant, Tea Maker (or in the USA Coffee
    > > maker), etc
    > >
    > > Abbreviation would be M, As, TM (or CM), etc.
    > >
    > > Then simple link this new table with the primary and insert a lookup field
    > > in the employees record. You can refer to columns, etc
    > >
    > > Hope this helps (it's much better than loads of IIF's or ='s)
    > >
    > >
    > >
    > > --
    > > Wayne
    > > Manchester, England.
    > >
    > >
    > >
    > > "jan" wrote:
    > >
    > >> I am running Access 2003 on WIN XP.
    > >>
    > >> I have a query that shows all my current employees, their titles, and
    > >> other
    > >> information. The query is used to create an Organization Chart with
    > >> Visio.
    > >>
    > >> In some cases, it is appropriate to abbreviate their job title, in some
    > >> cases it is not. For example, abbreviating Manger would give me an M;
    > >> abbreviating Maintenance Worker 2 would give me MW2. The later is fine,
    > >> the
    > >> former is not.
    > >>
    > >> How do I, in my Access query, show that Jack Jones is a Manager and Joe
    > >> Johnson is an MW2?
    > >>
    > >> I'm fairly certain this involves a formula. I stink at formulas.
    > >>
    > >> Thanks in advance,
    > >>
    > >> Jan
    > >>
    > >>
    > >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Jan,.

    I agree with Wayne, except in 3 respects:
    1. There is no valid purpose served by an Autonumber field (TitleID or
    whatever), since the name of the job title in this table will already be
    unique.
    2. He seems to be recommending the use if a Lookup field. I agree with
    the advice here...
    http://www.mvps.org/access/lookupfields.htm
    3. He obviously hasn't ever tried to get a decent cup of coffee in the USA.

    --
    Steve Schapel, Microsoft Access MVP

    Wayne-I-M wrote:
    > If would be best to create the new table first and then simply open the
    > orginal table in design and create a new field as a lookup in [tblTitles]
    > and then select the new table as the source.
    >
     
  7. Wayne-I-M

    Wayne-I-M
    Expand Collapse
    Guest

    MMMmmm. Maybe,

    If the job title is unique then yes you could as Steve rightly says do
    without the auto number and use the title as the primary key. But on the d
    bases I look after one of them is for interview results and many people have
    the title of Manager or (morel likely) their job title is simply a notation
    of the area of work - i.e. reception, stores, etc and you "may" want to
    distinguish individual jobs that may have the same general title. This is
    why I recommended using the auto number. So you will need to look at your
    specific requirements and make a choice.

    I looked at the link Steve provided and I think that "some" of the points
    are merely self-justifications. (You make your mind up you don’t like
    something and then come up with “valid†reasons for why you don’t like it).

    I would suggest you take any advice that is given by an MVP as I have looked
    around this forum and the advice they give is always top rate. All I can say
    is that in “this†case and with your specific requirements “I†would use a
    lookup field.


    As for the USA - I went there (once) and 5 things stick in my mind.
    1. It is very big - as big as the whole of Europe.
    2. No-one drinks Vodka and Redbull mixers (they should it makes the
    evening much better fun)
    3. The in the USA the are more shops in the airports
    4. They drive on the wrong side of the road.
    5. It is VERY hard to get a descent cup of tea (not as bad is some counties
    in of Europe where they put lumps of fruit in instead of
    milk).
    o So - French, Italians, German and Swiss ski resorts please note.
    When someone asks for a cup of Tea they don’t want a glass of warm water
    with a bit of lemon in it and a Tea Bag in a paper wrapper on a plate. Oh and
    fruit flavored tea is NOT the norm ?? try Earl Grey and Standard English
    Breafast - much better.
    So, please supply a “cup†(which should be warmed beforehand) of boiling
    water (put the tea bag in first) and a small jug of semi-skimmed milk. I
    understand that that many people will argue the milk should go in first BUT
    I’m sure that somewhere on the web there will be a forum to discuss this so I
    won’t go into it here.

    --
    Wayne
    Manchester, England.



    "Steve Schapel" wrote:

    > Jan,.
    >
    > I agree with Wayne, except in 3 respects:
    > 1. There is no valid purpose served by an Autonumber field (TitleID or
    > whatever), since the name of the job title in this table will already be
    > unique.
    > 2. He seems to be recommending the use if a Lookup field. I agree with
    > the advice here...
    > http://www.mvps.org/access/lookupfields.htm
    > 3. He obviously hasn't ever tried to get a decent cup of coffee in the USA.
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Wayne-I-M wrote:
    > > If would be best to create the new table first and then simply open the
    > > orginal table in design and create a new field as a lookup in [tblTitles]
    > > and then select the new table as the source.
    > >

    >
     
  8. jan

    jan
    Expand Collapse
    Guest

    Problem solved!

    Due to the way my database is constructed and used, adding an extra field to
    the job codes table did the trick. (the codes work as the unique identifier)
    No change of associations.
    Just had to add the new field to the query and re-run the Visio wizard.
    If any of the abbreviations turn out to be inappropriate, all I have to do
    is change it in the table and re-run the query.

    Much better than a dozen IIF statements.

    I do love introducing visitors to iced tea. But, people say I have a twisted
    sense of humor.

    Thanks!
    Jan

    "Wayne-I-M" <WayneIM@discussions.microsoft.com> wrote in message
    news:1B9B5A22-CB20-4107-914F-7CF6F716E06F@microsoft.com...
    > MMMmmm. Maybe,
    >
    > If the job title is unique then yes you could as Steve rightly says do
    > without the auto number and use the title as the primary key. But on the
    > d
    > bases I look after one of them is for interview results and many people
    > have
    > the title of Manager or (morel likely) their job title is simply a
    > notation
    > of the area of work - i.e. reception, stores, etc and you "may" want to
    > distinguish individual jobs that may have the same general title. This is
    > why I recommended using the auto number. So you will need to look at your
    > specific requirements and make a choice.
    >
    > I looked at the link Steve provided and I think that "some" of the points
    > are merely self-justifications. (You make your mind up you don't like
    > something and then come up with "valid" reasons for why you don't like
    > it).
    >
    > I would suggest you take any advice that is given by an MVP as I have
    > looked
    > around this forum and the advice they give is always top rate. All I can
    > say
    > is that in "this" case and with your specific requirements "I" would use a
    > lookup field.
    >
    >
    > As for the USA - I went there (once) and 5 things stick in my mind.
    > 1. It is very big - as big as the whole of Europe.
    > 2. No-one drinks Vodka and Redbull mixers (they should it makes the
    > evening much better fun)
    > 3. The in the USA the are more shops in the airports
    > 4. They drive on the wrong side of the road.
    > 5. It is VERY hard to get a descent cup of tea (not as bad is some
    > counties
    > in of Europe where they put lumps of fruit in instead of
    > milk).
    > o So - French, Italians, German and Swiss ski resorts please note.
    > When someone asks for a cup of Tea they don't want a glass of warm water
    > with a bit of lemon in it and a Tea Bag in a paper wrapper on a plate. Oh
    > and
    > fruit flavored tea is NOT the norm ?? try Earl Grey and Standard English
    > Breafast - much better.
    > So, please supply a "cup" (which should be warmed beforehand) of boiling
    > water (put the tea bag in first) and a small jug of semi-skimmed milk. I
    > understand that that many people will argue the milk should go in first
    > BUT
    > I'm sure that somewhere on the web there will be a forum to discuss this
    > so I
    > won't go into it here.
    >
    > --
    > Wayne
    > Manchester, England.
    >
    >
    >
    > "Steve Schapel" wrote:
    >
    >> Jan,.
    >>
    >> I agree with Wayne, except in 3 respects:
    >> 1. There is no valid purpose served by an Autonumber field (TitleID or
    >> whatever), since the name of the job title in this table will already be
    >> unique.
    >> 2. He seems to be recommending the use if a Lookup field. I agree with
    >> the advice here...
    >> http://www.mvps.org/access/lookupfields.htm
    >> 3. He obviously hasn't ever tried to get a decent cup of coffee in the
    >> USA.
    >>
    >> --
    >> Steve Schapel, Microsoft Access MVP
    >>
    >> Wayne-I-M wrote:
    >> > If would be best to create the new table first and then simply open the
    >> > orginal table in design and create a new field as a lookup in
    >> > [tblTitles]
    >> > and then select the new table as the source.
    >> >

    >>
     

Share This Page