Results 1 to 6 of 6
  1. #1
    Christiekp is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    1

    Query to provide count of multiple records containing same data in a particular field

    I cannot build in SQL but need to use Expression Builder in my access query.



    I have a table that contains much data and one field is batch number. There are occasionally multiple records with the same batch number. I need to create a unique field which would concatenate the batch and the count of the batches.


    I need to create a field, which in most cases would contain a 1 because there would be only one instance of the batch.

    For example batch 22-3456 has only one instance, my concatenated field would be 22-2345-01. If there were three batches with the same number they would be 22-2345-01, 22-2345-02 and 22-2345-03.

    What should I use in my update query to get that identifier?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I would not store it, just calc when needed.
    You could use DCount()
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you don't actually need a count (which you would easily get using a dCount but would be 3 for all the three records in your batch) but a "rank". Review this link and also do a Google search on "Access ranking query" and you'll get what you're after...
    http://allenbrowne.com/ranking.html

    Google search:
    https://www.google.com/search?q=acce...hrome&ie=UTF-8

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Vlad,
    I was thinking of criteria <= ID ?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the 01 is real data, it needs to be stored as such and not calculated - because it's data?
    We know (or should) that you cannot rely on sort order in tables alone, so assigning a rank value on the fly could result in
    22-2345-02 | 01/01/2022
    22-2345-03 | 02/01/2022

    on one day and on another

    22-2345-02| 02/01/2022
    22-2345-03| 01/01/2022

    This is why I dislike posts that have the absolute minimum of inputs and desired outputs. If the number (22-2345) relates to test data such as when, by whom, date, etc. that is one table, and batch data belongs in another. IMO not enough info to provide focused answers but that's just me.
    Last edited by Micron; 11-09-2022 at 09:39 AM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Christiekp,
    I suggest you step back and review your requirement. It seems that Batch or BatchNumber is important to you. If that is true, then it should be a field in a table. A good practice is to make a drawing(rough is fine) of the things/subjects (tables) involved in your "business", and for each table the attributes/details (fields) related to that table.

    SEE Intro to Data Modeling

    And welcome to the forum!
    Last edited by orange; 11-09-2022 at 07:02 AM. Reason: spelling

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

Similar Threads

  1. Replies: 3
    Last Post: 12-29-2021, 04:18 AM
  2. Replies: 7
    Last Post: 08-28-2014, 01:33 PM
  3. Replies: 7
    Last Post: 07-17-2014, 12:42 PM
  4. Replies: 7
    Last Post: 04-28-2014, 07:09 AM
  5. Replies: 4
    Last Post: 01-24-2013, 10:10 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