Results 1 to 4 of 4
  1. #1
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80

    Table Design Quirk

    I have a table with a field with a dropdown. The dropdown is populated from an "events" table, and since over time the list can get quite long, it displays only event items that have not been suppressed in the events table, generally anything newer than about four years.



    Part 2: Events are uniquely identified by an autonumber, but in the dropdown I have my problem table set to display the text description associated with that number.

    All good, but when an event is suppressed, it no longer shows in the dropdown (good thing), but past entries in my problem table only display the autonumber for the event, rather than the text description for that event (bad thing).

    I still need the dropdown to filter out suppressed events for new entries, but I'd like the text description to display for all entries since Day 1, rather than reverting to just the autonumber for the older, suppressed stuff. Don't know how. Help?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    but I'd like the text description to display for all entries since Day 1
    A bit confusing to me. Text description from where, the table? Have to think so since the way you describe the combo behavior (and say it's all good) makes me think so. Then what you seem to be describing is the view of a table where a key value is a foreign key from another table (which happens to be an autonumber) and that's not very informative. It's not supposed to be really. Unless the foreign key is a text field, that's what you'll get, but this is OK because you're not supposed to be poking around in tables anyway. You should have a form based on a query that provides the deprecated values, including text descriptors.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Ellpee is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2013
    Posts
    80
    Arrgh, just hit a wrong key and nuked my entire reply!!!

    Table 1 stores event info: autonumber key, text description, and suppressed yes/no.
    Table 2 stores donation info, and one of its fields associates the donation to an event.
    That field has a dropdown that EXCLUDES anything suppressed in Table 1, to keep the list from getting too long with useless options.
    All good for entering new donations, but past donations now show only the autonumber key from Table 1 if the event has been suppressed.

    So how can I exclude suppressed stuff from the dropdown but still get the text description for all items in that field, suppressed or not??????

  4. #4
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    You can't do this at the table level, and, as Micron already pointed out you shouldn't work directly in the tables to begin with. This would need to be done at the form level, likely a few different ways you could do it. Do you have any forms at all in your application?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A rather odd SQL quirk in Access
    By upnxT in forum Access
    Replies: 16
    Last Post: 08-05-2016, 09:54 AM
  2. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  3. Replies: 8
    Last Post: 03-24-2012, 11:03 AM
  4. table design: one big table vs. multiple small tables
    By lstairs in forum Database Design
    Replies: 3
    Last Post: 12-31-2009, 08:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums