Results 1 to 6 of 6
  1. #1
    RedSanders is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    1

    Questionable suggestion by Performance Analyzer

    Running Access 365, Windows 10.



    Performance Analyzer had only one suggestion for my database - to change a lookup field in a table from "Short Text" to what I think is 'Long Integer. (The end of the suggestion is too long to fit in the Analysis Results window.)

    First, the table (tblLocations) being looked up only has 2 fields, - an AutoNumber whose field size is a Long Integer, and a Short Text field set to 40 characters.

    In the table (tblScales) containing the lookup field was automatically set to Short Text when the lookup was created. Neither do I see how to change it to a Long Integer, and why would it suggest that when what is being displayed is text?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    You have a lookup field in tblScales that uses a field in tblLocations as its source? I didn't know you could do that; probably because like many others, I avoid lookup fields (not lookup tables) and their issues altogether. The more accepted approach is to use a lookup table if you want an easily editable lookup source. Then you would use forms to save the parent record long integer ID (often autonumber PK) into the child record as a foreign key value. In that case, you would be using long integer values to relate records rather than text. Text based keys would require that you use natural keys (real data) instead of autonumbers. So the direct answer from me would be to ditch the lookup field altogether.

    I had a lot of links saved but lost them when my hd died, otherwise I'd point you to whatever I had about not using lookup fields. Suggest you see what you can find on it if no one else chimes in with something.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,575
    how to change it to a Long Integer,
    in the table design, choose number, at the bottom in the field properties choose long integer.

    why would it suggest that when what is being displayed is text?
    you would need to look at the bound column property - it is usually 0 for the first column which is usually the autonumber field

    see this link about the issues you can expect to get using lookups in tables - just because you can does not make it a good idea

    http://access.mvps.org/access/lookupfields.htm

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,431
    I had a lot of links saved but lost them when my hd died
    Micron, I hope you replaced that with an SSD!

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,357
    No, too much $$ and less capacity. Besides, if you're going to drop a laptop I'm not sure it would matter.
    I have a 1 TB usb drive that I wasn't using, but certainly am now. The real important stuff is on OneDrive too.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,431
    No, too much $$ and less capacity. Besides, if you're going to drop a laptop I'm not sure it would matter.
    I have a 1 TB usb drive that I wasn't using, but certainly am now. The real important stuff is on OneDrive too.
    I can't resist the new technology. Or even the old. I have a refurbished ThinkCenter that came with a Core I5 and an SSD that I use for my security/IOT network. It costed me $219.00 on Amazon.
    And we can't ignore backup. I use iDrive.

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

Similar Threads

  1. Replies: 13
    Last Post: 06-26-2017, 11:47 AM
  2. Replies: 5
    Last Post: 12-27-2012, 06:17 PM
  3. Template Suggestion?
    By TSO in forum Access
    Replies: 5
    Last Post: 03-08-2011, 04:35 PM
  4. I need suggestion in SQL
    By genesis in forum Access
    Replies: 0
    Last Post: 07-31-2009, 08:17 PM
  5. Need suggestion or help please
    By KLynch0803 in forum Programming
    Replies: 14
    Last Post: 06-11-2009, 10:21 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 - Senior Forums