Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: I don't want an alphabetical list for my Lookup Field

    I have imported a table from Excel (1 column of Subject Headings) grouped in order of subject.
    My table "Subject Headings" in Access is still in order of subject i.e. not alphabetical. When I make a lookup field in my Main Table to look up the Subject Headings table it is an alphabetical list even though I don't choose a Sort Field.
    Can I keep the order of my list? it makes more sense when having to view quite a long list.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sure you can - add a field called "SortOrder" then order by that instead of "Subject Heading"
    As a very intelligent () man once said
    Quote Originally Posted by Brett Kaiser
    The physical order of data in a database has no meaning
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2007
    Posts
    72
    Oh, okay, I'll try that tomorrow. I took out my list of numbers because it was causing me problems for another reason!! I'll try to remember what that was tonight, before I start going around in circles. Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lookup column.. I hope you haven't defined a lookup wizard in a table design. if your look up is implemented as a table with a foreign key relationship then you can build your lookup table in any sequence your diseases mind could chose.. by using a sort order. you options if you use a look up wizard are much more limited.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2007
    Posts
    72
    Hmm. I'm not quite sure what you were saying there... I've just re-imported my table and let Access put a Primary key field in and now my drop down list is in the order it was in Excel which is what I wanted. The only thing is, I haven't gone back to my form yet which is where I ran into problems with the Primary key field before which is what led to me taking it out, so my problems may not be over just yet!

    If you have time to explain the "look-up column" as opposed to the look-up field that the Wizard creates, I would appreciate it. I've just read "Creating a Lookup Field" in "Access 2003 for Dummies" but it only talks about using the Wizard.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    despite what many people may say Access IS a relational database product, although strictly speaking Access itself isn't a database... but thats another story/posting. Generally speaking its better to define a table to contain the values you want to lookup. so you would have say...

    product table
    product id
    product desc
    .....<blah>
    supplier id
    product Type

    say you defined your supplier as a lookup using the wizard.. not a problem.. you put in the values of the known suppliers... it works fine.... Then your company decides to drop a supplier, another merges, another stops selling products you buy, your company starts dealing with other suppliers. you have to go in and change the Supplier lookup values in the table design. not a good idea.

    if you implement the supplier id as a separate table, using the primary key of that table (supplier id) as your value in the product table, define a relationship between the two tables (both column types need to be the same, although an autonumber column is a variant of long integer)

    ...result you dont have to tinker with the table design, the users can change whatever they want whenever they need to (no more urgent phone call 5 minutes before you leave on a weekend/holiday.. we must have x,y & z done by close business tonight [well you'll never stop that altogether, but it wont be so that you make changes to their db to make their data work].

    table supplier
    supplier id
    supplier name
    <blah>

    ..whilst you are at it define a table for product types

    very occasionally lookup tables may be worthwhile.. eg Sex (M/F/?), but one of the drawbacks of using lookup tables is that as you start designing queries they can be a real nightmare to use, and get meaningfull results from.

    it may be worthwhile having a look at Paul Litwins bit on relational design on Rudy's (R937) site
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2007
    Posts
    72
    Okay, I think I just worked this out. Do you mean the Lookup Tab on the Properties in Design view? I deleted the Primary key and used the Lookup tab to select a Combo box and then Row Source is my table of Subject Headings and whaddaya know that works as well!

    I think this is what caused me problems before, doing it the way I just described above but with the Primary key. All my dropdown list would display was the number(the Primary Key). This was even if I changed the Bound Column to 2 (the column with the Subject Headings in it).

    So it wasn't my form that had the problem, it was when I was trying to do this before (I get so confused because I try twenty different things in five minutes and can't remember what I've done).

    Perhaps now I can get on with the job I'm here for, at least I've brought some structure to this database, we've gone from over 4000 Subject Headings and growing, to 239. I know that still seems like a lot but it's a diverse library.

  8. #8
    Join Date
    Jul 2007
    Posts
    72
    Thanks Healdem, I will read your reply in detail, before I go forth and start cataloguing in earnest. I'm a temp librarian in London and I can't bear the amount of really crap Access databases I've come across. At least at this place they have the flexability to allow me some time to fix up the structure a bit so I can add the next 4000 records with a bit of speed and integrity (I hope)
    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •