Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    count in query (Access 97-2007)

    Hi, In a query I need to be adding a number count as one of the columns. Easy enough in reports but how can I accomplish same in a query please.
    Existing set up is use the query to make a table then add an autonumber column.

    TIA
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count in query (Access 97-2007)

    You don't need a calculated field to fill an AutoNumber field - an AutoNumber field is populated automatically when you add it to a table.

    If you want to create a custom sequence number: does the table have a field that uniquely identifies the records?

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: count in query (Access 97-2007)

    Not immediately apparent but I reckon we could concatenate a couple of fields to form a unique field.
    Custom sequence number sounds like you have a plan?
    Thanks
    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: count in query (Access 97-2007)

    Say that you have created a field ID that uniquely identifies the record.
    You can then add a calculated column in a query based on the table:
    <code>
    SeqNo: DCount("*","NameOfTableOrQuery","ID<=" & [ID])
    </code>
    Substitute the name of the table or query and the real name of the ID field.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: count in query (Access 97-2007)

    Many thanks again Hans,
    I see the logic and it worked a treat.
    Query sorted on the ID field and counts up great

    Regards
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •