Results 1 to 4 of 4
  1. #1
    IT_Charlies is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    10

    how to narrow down a search by two field's data

    I am quite new to Access and programming,

    I have a table with 1,101,203 records, 16 fields. How do I create a form to search by, 'Account number', and narrow down searches for
    +/- 10% on price and sq ft.

    example:I would search the account number and it would give me;


    Account_no: 10140455
    Color: White
    Etc: Etc
    Etc: Etc
    Value: 149720
    Sq_Ft: 13875

    I want to get a list of records displaying +/- 10% of the 'Value' and 'Sq_Ft' I got from the search I made of the 'Account_no'.
    So when I search 'Account_no': 10140455 and it gives me that account information(etc, etc, etc, Value and Sq ft), I also want to get a list of records that have +/- 10% of the value and sq ft of the 10140455 record.

    I hope someone understands what I'm asking for and can help me.
    Thank You in advance for the tips and/or help I receive.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    ?? You want to find accounts that have value +/- 10% Value of account 10140455 and +/-10% of sq-ft of account 10140455?? It's only this account?
    I would determine .9*14970( 13473) and 1.1 *14970(16467) and use those numeric values
    and .9* 13875(12487.5) and 1.1* 13875(15262.5)

    Hopefully value and sq_ft are indexed.

    Select account from table
    where
    value between 13473 and 16467 AND
    sq_ft between 12487 and 15263

  3. #3
    IT_Charlies is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    10
    It is not only that one account. let me put like this, My client walks in and tells me his account number for his property, i put in his number in my search box. When the results come back i want to get all the records that are +/- 10% and his record in between the +/- 10% records.
    Account no Etc etc etc etc Value Sq ft
    etc etc etc etc etc etc etcetc etc etc etc etc etc etc
    10140455 etc etc etc etc 149720 13875
    etc etc etc etc etc etc etc
    etc etc etc etc etc etc etc
    etc etc etc etc etc etc etc

    Hope this is a better explanation.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    I'm trying to emphasize that dealing with 1.1+ million records could be slow.
    Things will improve a lot if Value and sq_Ft fields are indexed. I calculated the .9 and 1.1 % values for Value and SQ_ft since I wanted to avoid doing these 4 calculations in each of the records involved.

    You could have a form where an account number is entered:

    Account Number _________ and type in the account number,

    behind the scenes vba gets the Value and SQ_FT values for the account. It then calculates the .9 and 1.1 values
    for Value (say X and Y) and SQ_ft (say A and B), and creates some SQL to build the query.

    The idea is to do the calculations of .9 and 1.1 once and then put in the actual values.
    Also, since you want his account figures included, I think I would add an OR to the query
    and if you want his numbers to be within the data being displayed I'd suggest a SORT BY.

    The basic query is along this line, but I'd still do it behind the scenes (vba code) to avoid the extra (potential) 4 million calculations.
    ---untested---
    Code:
    SELECT theFieldsYouWant FROM Table
    WHERE
    (Value BETWEEN X and Y  AND
    Sq_ft BETWEEN A  and B)  OR
    (acctNo = AcctNo That was typed in)
    ORDER BY Value ;

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

Similar Threads

  1. dlookup to narrow search results
    By blappy347 in forum Programming
    Replies: 2
    Last Post: 03-29-2013, 02:25 PM
  2. Replies: 3
    Last Post: 08-22-2012, 03:28 AM
  3. Replies: 7
    Last Post: 08-08-2012, 03:28 PM
  4. Replies: 12
    Last Post: 03-22-2012, 02:48 AM
  5. Generating List and Narrow down to type
    By bhaktharvali in forum Database Design
    Replies: 2
    Last Post: 01-07-2012, 03:39 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