Possible to sort by 3rd word?

[Qury]

Hi,

I have a Contacts table which holds some data I need to query. The fields I
need are Name, Phone, Extension, Email, Municipality, Facility, and Date. The
Municipality field has data in the following formats: City of...,
Municipality of..., Town of..., and Township of.... There are only a few
records that do not have Municipalities, Facilities, and Dates so I set up
the query with the criteria of * in the Municipality field. I also have it
sorted as Ascending.

Is there a way that I can get it to sort by the actual Municipality name? I
need Township of A to come before City of B and Municipality of C.

I'm using Acess 2003 and have extremely limited experience in coding.

Thanks muchly,
Qury

 

[strive4peace]

You should break the Municipality into 2 fields:

Municipality
MunicipalityType

ie: Town of Anytown -->
Municipality: Anytown
MunicipalityType: Town

then, when you want to combine them, you can do this:

MunicipalityType & " of " & Municipality

~~~

you can use Update queries to change the data you already have in your table


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Qury]

That makes sense. Should I then make another table that has City,
Municipality, Town, and Township as field entries so I could just put
MunicipalityType as a lookup to that table instead of having to enter in the
type for every entry? I may have to add more Municipalities in the future.

Thanks muchly,
Qury

 

[strive4peace]

Hi Qury (what is your name?)

you're welcome

yes, that would be a good idea... something like this:

Municipalities
- MunicID, autonumber -- PK
- MunicTypID, long integer -- FK to MunicTypes
- Municipality, text

MunicTypes
- MunicTypID, autonumber -- PK
- MunicType, text

PK = Primary Key
FK = Foreign Key

and then, when you want the Municipality in another table, you would set
up this field:

MunicID, long integer -- FK to Municipalities

you would use a combobox to collect it on a form


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Qury]

Hi,

I'm not sure that I will go as far as putting the Municipality names into a
separate table since there will only be one instance of it in the whole
Contacts table. But, since there are four types of Municipalities, I figure
it would be easier to put them in a separate table. It all makes sense now!

Possibly one last question though... You mentioned this code:
MunicipalityType & " of " & Municipality. I am not sure where I would put
that exactly. And, would it still apply with the new table I will be making
for the Municipality Type?

Thanks again!
Qury

 

[strive4peace]

you're welcome

"It all makes sense now!"

great!

"...not sure where I would put that exactly..."

in a query, put both tables in the top part as sources for the query

on the grid, you can define a calculated field that combines the
municipalitytype, the word "of", and the municipality

Munic: [MunicipalityType_fieldname] & " of " & [Municipality_fieldname]

anything before the colon will be the column label -- make sure it is
different than any fieldname in the source


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*