Results 1 to 15 of 15
  1. #1
    dbri is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6

    Post Counting the number of found matches

    I want a query that on the user entry can give the proper answers:



    It must give me the number of occurrences that that number appears in the table, a sortof countif :
    E.G. for the following
    if 1 is entered the result should be 6
    if 2 is entered the result should be 5
    if 3 is entered the result should be 4
    if 4 is entered the result should be 3
    Click image for larger version. 

Name:	access.JPG 
Views:	17 
Size:	17.5 KB 
ID:	36642
    Ideas? what should be placed in the query?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What do the 1 ,2 3 , 4 represent? You will get more focused responses if you give us more context for your post.
    I see RoomX (X in 1,2,3,4,5,..) but can only guess what this might mean.
    More info needed.

  3. #3
    dbri is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    I just want a query that counts the number of times a number appears over different fields (in the case Rooms), the number is of numeric format

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Assuming your situation resembles your example, the only way I can think of is to have one query that includes the source table as many times as there are fields. Equal join each table on a field that won't be part of the count. The query design grid would have criteria 1 in each field, but each on a different row (an OR situation). A second (Totals) query uses that query and performs a count for each field. If your source fields have data where you're showing nulls, then that complicates it.

    EDIT - I meant the only way I could think of that didn't involve a vba function since a query was requested.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    You will get more focused responses if you give us more context for your post.
    Have to agree with orange. I'd also note that repeating fields (ie. Room 1, Room 2,etc)is usually an indication of non-normalized data.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    More info required. Readers will help you, but you have to help them by putting your request/issue into meaningful context.

  7. #7
    dbri is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    I explored that area (putting the criteria on different lines (as for each room)) and did a couple of counts or sums but till not didn't have any success. Yes If possible I'll avoid VB functions. I'll try your version too. Thanks

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    dbri,

    People here will help. Describe in simple, plain English what you are trying to do.
    Since you are new to the forum, I am guessing you are new to database.

    It all starts with a clear requirement. But it can be done by trial and error as many have done-- just takes exponentially longer.

    Welcome to the forum and good luck with your project.

  9. #9
    dbri is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    Hi first of all thanks for the support. Here is the bigger picture. First of all this is just a fictitious scenario but I always wanted to know if with access is possible. I am aware about normalisation but I want to visualise the data in a table for the simplified hotel booking system. I didn't normalise so that at a glance the user can get an idea of when a room is available or not. If the room is normalised I'll get a long list and to extrapolate data wouldn't be easy, would it? Ideas are welcome....should I normalise and use Crosstab?Click image for larger version. 

Name:	db2.JPG 
Views:	14 
Size:	88.8 KB 
ID:	36645

  10. #10
    dbri is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    The query is needed to generate the bill. The data in the table refer to the booking number which is stored in a different table.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    See the info here.

    I think you may have an issue with table structure. Room availability is typically done via a query. Perhaps you could show us the table and relationships. I still don't quite understand the 1,2,3, 4 --which may be hiding some entity??
    good luck.

  12. #12
    dbri is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2018
    Posts
    6
    Yeah I explored that way of tackling things but having Rooms vs Dates, if possible for the booking stage is very important (sort of non-normalised). I wonder if it possible to store normalised and then extrapolate the Rooms vs Date in a table format (similar to the one I'm suggesting in the table)

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a data model from Barry Williams' site (databaseanswers.org). I have put X through the tables that are not likely useful to you at this point. You may not need HOTELS nor ROOMTYPES, BOOKING depending on your needs.

    This model shows the "typical tables and relationships" for Room Reservations. Barry's models are generic in that they ccover the most common needs. Each model can be modified, extended etc to suit your situation. They are meant as a starting point, guide or reference to get you started.

    Click image for larger version. 

Name:	HotelReservationModel.PNG 
Views:	12 
Size:	68.4 KB 
ID:	36646

    Good luck.

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by moke123 View Post
    I'd also note that repeating fields (ie. Room 1, Room 2,etc)is usually an indication of non-normalized data.
    I considered that but thought "fields aren't going to be added unless there's a renovation" which would be remote. Problem is that the samples given in most threads don't really resemble reality.

    Don't understand the reluctance to use a function unless maybe there can't be a form to provide input. Quite simple and flexible I think, regardless of normalization:
    Code:
    Function GetCriteriaCount(varValue As Variant) As Long
    Dim rs As DAO.Recordset
    Dim fld As Field, i As Integer
    
    Set rs = CurrentDb.OpenRecordset("table1")
    If Not (rs.EOF And rs.BOF) Then
        Do Until rs.EOF
            For i = 1 To rs.Fields.Count - 1
                If rs.Fields(i) = varValue Then GetCriteriaCount = GetCriteriaCount + 1
            Next
            rs.MoveNext
        Loop
    End If
    
    End Function
    The only caveat I can think of with this code is that there's no option to include or not an autonumber id field. It just excludes it, but that should be adaptable.

    QUERY SQL:
    SELECT getcriteriacount("1") AS Cntfield
    FROM Table1
    GROUP BY getcriteriacount("1");

    DATA
    ID FLD1 FLD2 FLD3 FLD4 FLD5
    1 1 1
    2 1 1
    3 1 1
    4 1


    RESULT for "1" 7

    btw, you get a result in query datasheet for every field that you put the function call into. I only posted the count result rather than a picture of a datasheet.
    Last edited by Micron; 12-18-2018 at 02:07 PM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Just another observation. In the screenshot you appear to have a dropdown in the room field. Are these table level lookup fields?
    If so, they only tend to make things more difficult. http://access.mvps.org/access/lookupfields.htm

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

Similar Threads

  1. Replies: 7
    Last Post: 02-29-2016, 06:13 PM
  2. Extracting number of files found by module
    By JeroenMioch in forum Access
    Replies: 4
    Last Post: 01-21-2016, 11:43 AM
  3. Counting the number of enrollees on a course
    By slaterino in forum Access
    Replies: 2
    Last Post: 10-12-2010, 12:24 AM
  4. Replies: 1
    Last Post: 05-18-2010, 12:05 PM
  5. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 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
  •  
Other Forums: Microsoft Office Forums