Page 3 of 6 FirstFirst 123456 LastLast
Results 31 to 45 of 76

Filtering Multiple data set from a single column Field

  1. #31
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,365

    Every field is a particular data type. For purpose of constructing the DCount expression as shown in earlier post, the field is text, number, or date/time type. The type determines what delimiters must be used for the parameters. Note the ' and # characters in the expression. Text fields require ' delimiters for criteria and date fields require # delimiters, number type doesn't require delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  2. #32
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I have tried both SQL statements both do not work one says there is a data type missmatch and the second one says "cannot have aggregate function in WHERE clause (Count(*)>1)"

  3. #33
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,365
    What are the data types of each of the fields? If they are numeric then remove the apostrophes.

    Sorry, instead of WHERE should be HAVING and it will come after the GROUP BY. Use the query builder to help construct query.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #34
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    So far I have tried the second modified SQL code and it does give the desired result but now that i am trying it on the actual file which holds over 1,000,000 records the query has been running for over 30 minutes and still has not shown the new table is this because there is to much data?

  5. #35
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,365
    I've never worked with that many records in one table but I guess that is a possibility.

    Is this a split db? Is backend on central file server?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #36
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    As far as I know this file is located in a SQL database which I programmed the form to automatically access once the access file is opened. Other than that I am not sure if it is backend or not.

  7. #37
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,365
    Then yes, that is a split arrangement. The tables are in the SQL file and the form is in Access file.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #38
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    In excell vba you can loop through the column field values, why can't you do it in access vba?

  9. #39
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    I would add a field to your table (not visible) that is a score (integer). Then iterate through the fields, running comparisons as you see fit, and add to the score field when any comparisons match. You can then display the entire dataset (or filtered by a minimum score) sorted by highest score (most matches)

  10. #40
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    Why cant I use the field names i already have?

  11. #41
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,365
    Quote Originally Posted by mr879 View Post
    In excell vba you can loop through the column field values, why can't you do it in access vba?
    VBA can loop through records and fields but not by directly referencing the table. This is what recordset objects are for. Cannot have VBA code behind a table or query. Code is in form, report, general modules.

    Queries can manipulate tables (filter, sort, calculate) but cannot have 'loop' code within a query. A query can call a VBA function. That function can manipulate a recordset object to do some data analysis and return a value to the query. The code would execute for each record of the query. If there are 1,000 records then the function would be called and run 1,000 times.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #42
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    This is an example code of someone who looped through the column "C" and compared the cell values to a string to see if it is a match and switched with the increment value. This is done in excell using excell vba.


    Option Explicit

    Sub Macro1()
    Dim r As Range, cell As Range, mynumber As Long

    Set r = Range("C1:C2642")

    mynumber = 1
    For Each cell In r
    If cell.Value = "OK" Then
    cell.Value = mynumber
    mynumber = mynumber + 1
    End If
    Next
    End Sub

  13. #43
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I just noticed that i can not make use of the query even if i were to get it to work because I need to be able to incorporate the UI that I have made which is used on the table itself for filtering and such.

  14. #44
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,365
    And that looping could be done with a recordset object in VBA, as noted in my previous post.

    Don't know what you mean by 'UI that I have made which is used on the table itself'. This UI is a form?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #45
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    The UI is a user interface which is made in the form itself, the setup is a split form where the form is at the top half and the table is shown at the bottom half of the page that way the user can display the filtered values in the bottom table.

Page 3 of 6 FirstFirst 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering Data from a specified column
    By mr879 in forum Programming
    Replies: 16
    Last Post: 05-15-2014, 10:59 AM
  2. Multiple choice in a single field
    By fabads in forum Access
    Replies: 6
    Last Post: 05-21-2013, 11:45 AM
  3. Replies: 2
    Last Post: 08-10-2012, 03:42 PM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Filtering dupe Data in a single field
    By label027 in forum Queries
    Replies: 4
    Last Post: 10-25-2011, 01:18 PM

Tags for this Thread

Posting Permissions

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