Formatting numbers as addresses (1 Viewer)

fendermate · Mar 25, 2008

My database is a maintenance record of our building complex. The unit/building number is the key field. The problem I am having is having access recognizing the unit numbers and building numbers, not as whole numbers.
Example: I-380, 10-380, 2-390, 13-370, and so on. When sorted, access treats them as whole numbers. I have tried two separate fields but it would be nice to have only one field and be sorted by building numbers.
I am not a coder so be gentle.
Can you help?

gemma-the-husky · Mar 25, 2008

is the first one a letter I?

what sort order are you tring to achieve, with these examples

fendermate · Mar 25, 2008

No, they are all numbers.
It would be nice to sort by building number eg. 1-370, 2-370, 3-370, etc.
There are three buildings, #370, #380 & #390 and each building's units are numbered 1 to 23.
Does that help?

gemma-the-husky · Mar 25, 2008

but what sort order are you trying to achieve, and what order are you getting?

neileg · Mar 25, 2008

You have two different pieces of data, the building number and the unit number, so they should be stored in different fields. You can concatenate then into one in a query or a form or report. Sorting on the individual fields is then easy.

SInce this is the tables forum, I'm concerned that you are worrying about order in the table. Don't, apply the sort in your form or report.

fendermate · Mar 26, 2008

I presently have split the data into two fields and have no problems. What I'm doing is creating an entry form for others not so diligent with Access. If I stay with this design, I will have to figure out how to set up a search box to include both pieces of data; the unit number and the building number.
Is that possible with Access? It would solve a few problems.

fendermate · Mar 26, 2008

The order I would like when I sort in ascending order is units #1 to 23 of building #370 and below that units #1 to 23 of building #380 and below that, units #1 to 23 of building #390.
If the units/ building numbers are in one field they are addressed as whole numbers. The resulting sequence is mixed. #1-370 is 1,370 ,#1-380 is 1,380 and #2-380 is 2,380 etc. It becomes difficult to view data in groups of one building because they cannot be sorted buy building.
It is the same if you had fields with street addresses where the streets may be numbered.
Would you set up two fields? one for the house number and one for the street name or number/ example; #328-12th street? Usually not.
If not, how could you search customers on 12th street. I guess you'd have to use wild cards. Try explaining that to someone who knows nothing of database code.

fendermate · Mar 26, 2008

I would like to sort, numerically, the unit numbers and by building numbers. Here's another parallel problem; You have a database of customers' addresses. The streets are both numbers and names. Most data fields I've seen for addresses usually don't have separate fields for building # and street names. How then could you search for customers on 12th street without using wild cards? How would you be able to sort your customers by addresses? Addresses like #1328-12th st and #23-20th st. wouldn't be grouped by street.
Access looks at them as whole numbers like 132,812 and 2,320.
Can't the hyphen (-) be used as a separator? Then the unit # and building# would be treated as two different numbers.

neileg · Mar 26, 2008

fendermate said:
I presently have split the data into two fields and have no problems. What I'm doing is creating an entry form for others not so diligent with Access. If I stay with this design, I will have to figure out how to set up a search box to include both pieces of data; the unit number and the building number.
Is that possible with Access? It would solve a few problems.

No problem at all. I would use 2 combo boxes. The first would select the building number. The second would present a list of the valid unit numbers for that building. This technique is known as cascading combos. Do a search in these forums.

You need to understand the principles of normalisation (search in here or in Wikipedia). One of these principles is that data should be atomic. That means that each piece of data is at the lowest level of detail that has a meaning. That's why you shouldn't store builing number and unit number in the same field. On your form, you can capture or display these as one field, but you should store them as two fields.

fendermate · Mar 27, 2008

Thanks Neil,
I'll start researching. If I have any more problems (no doubt), I know where to come.

Formatting numbers as addresses (1 Viewer)

fendermate

New member

gemma-the-husky

Super Moderator

fendermate

New member

gemma-the-husky

Super Moderator

neileg

AWF VIP

fendermate

New member

fendermate

New member

fendermate

New member

neileg

AWF VIP

fendermate

New member

Similar threads

Users who are viewing this thread