strings in a query

[jan]

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

 

[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)

 

[jan]

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

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.

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

 

[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.

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

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.

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

 

[Steve Schapel]

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.

 

[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.

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

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.

 

[jan]

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

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.

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

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.