In both MS Access and MS Excel it is extremely unforced to form columns of paper or numbers. Both programs give us next to toolbar buttons that allow either ascendant sorts (1 ... 999) or down sorts (999 ... 1). The Ascending key has a blue-black "A" preceding a red "Z" beside a down pointing mark to the apposite of the 2 correspondence. The Descending lever has a red "Z" above a light-blue "A" with a downward pointing pointer to the authorization of the 2 junk mail. Simply put the pointer in the file you privation sized and sound onetime on the fitting knob.

As I declared in that first paragraph, this plant acute for manuscript or numbers. The challenges commence when you poverty to variety blended workbook and numbers. Here are 2 innocent authentic existence examples. (NOTE: the residuum of this nonfictional prose assumes we poorness ASCENDING sort command)

Example 1: Part Numbers. On a computer, which comes first?

Post ads:
Miniature File Cabinet for Business Cards with Built-in / Tonic Studios Guillotine Paper Trimmer 12-Inch-by-12-Inch / Winsome Wood 4-Shelf Narrow Shelving Unit, Espresso / 2013 Blue Sky Breast Cancer Desk Pad 22 X 17 / Equus 3145 Ford Digital Code Reader / Flash Furniture Mid-Back Black Mesh Office Chair with / MXL V67G Large Capsule Condenser Microphone / Uniden PRO510XL 40-Channel CB Radio / Nintendo Wall Graphics - New Super Mario Bros / 10" Handheld Brass Telescope w/ Wooden Box - Pirate / Samsung Galaxy Note 10.1 inch N8000 Tablet Slim Fit / Post-it Pop-up Notes Heart Dispenser, Holds 3 x 3 Inches / Telescope / Southwest Specialty Products 30005C Aquanet Diversion Can / HP 940XL (Genuine OEM Ink) 2-PACK Ink Cartrdges BLACK / Americopy 3,000 Address or Mailing Labels, 2-5/8" x 1" 100

  • W4
  • W39
  • W212

Example 2: Addresses. On a computer, which comes first?

  • 4 Main Street
  • 39 Main Street
  • 212 Main Street

Post ads:
3M Scotch Mounting Tape, .5-Inch by 75-Inch (110) / BIC Matic Grip Mechanical Pencil, Fine Point (0.5 mm), / AmazonBasics 6.5 feet/2.0 Meters Component AV Cable for / "French Ornate" Slim 2013 Weekly Planner and Organizer / Contour 2350 ContourHD Battery / Indestructible Aluminum Wallet/Credit Card Case (Assorted / Rolodex Mesh Three-Tier Desk Shelf (22341) / 3M Scotch Duct Tape, Purple, 1.88-Inch by 20-Yard / Sophia Global Remanufactured Ink Cartridge Replacement for / Perfect Timing - Turner 12 X 12 Inches 2013 Washington / 12 Pack (2 of each color) CLI-8 Compatible Ink w/ Chip for / Laptop Shoulder Strap Adjustable Wide Soft Padded / iPearl mCover Hard Shell Case for 15-inch Model A1398 / Sony PRS-T2/HBC 6 Touchscreen WiFi eReader - Black / BIC Velocity Bold Ball Pen, 1.6mm, Black, 12ct / Crosley 1950's Princess Phone - Pink

In both cases, the electronic computer sorts them contrary to the way a human would ordinarily impoverishment them sized. A human perfunctorily looks at the full twine of characters and next sorts victimisation a more convoluted algorithmic program than a core data processor program's variety logic, like that recovered in Access and Excel. To a computer, 2 comes formerly 4, so W212 sorts in the past W4.

Let's examine the Part Numbers trial prototypic.

If possible, have the way surroundings are numbered standardised so that all cog book of numbers have the said digit of characters. In our example, use W000004 and W000039 and W000212. This organization allows for 999,999 one-off constituent numbers AND it sorts the way we would anticipate it to kind.

If it is too late, and you cannot instigate the sought after pattern, next construct a new area in Access or a new indian file in Excel, and phone call it PartNumberSortable. Then, in Access, conceive a questioning that converts all the ongoing member numbers to a sortable edition. It will be an news query next to word philosophy like-minded this settled under the PartNumberSortable file in the query:

left([PartNumber],1) & formatting(mid([PartNumber],2),"000000")

All reports and queries can now go along to document your standard component part numbers, but they can be sorted by the new field, PartNumberSortable, which would stay unknown. You would besides have to convert any of your information passageway forms and routines so that they make the new piece of land unthinkingly.

In Excel you can compile a instructions for the new column. Here is one that industrial plant.

=LEFT(A1,1) & RIGHT("000000" & MID(A4,2,8),6)

This procedure assumes that the productive fragment digit is in single file A.

The computer address problem? Trickier. I'll deal with that one in different nonfiction. In the meantime, for more MS Access tips similar this, stop by

p.s. All the examples were processed on a computing machine near MS Office 2000; newer versions of Access and Excel may profession a touch differently, or have more than searching category capabilities.



hurszxi 發表在 痞客邦 留言(0) 人氣()