Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2003 - counting total number of records in a table

    I am constructing a database in Access 2003 which lists contact information. So far so good, it looks like this:
    ID, First_Name, Last name, etc
    1, John, Doe
    2, Tim, Johnson

    For the purpose of displaying these data through a merge with Word ("Good day [First_name] [Last_name], as one of our [Number_of_contacts] contacts ..."), I need to create an additional column in this table that shows how many records there are and this table. This total number of records should simply be repeated for each entry, which should look like this:

    ID, First_Name, Last_name, Number_of_contacts
    1, John, Doe, 2
    2, Tim, Johnson, 2

    I am not a database professional, nor do I have time to become one, and while I got the database running as intended I am at a loss as to how to get this one little number in my table (I found a lot about the 'count' function in queries, but this doesn't seem to help me much) so any suggestions would be appreciated!

    Ron

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You can't do that in a table and it doesn't make sense to do it, technically. I suggest you create a query based on that table and then add a column in that table with some SQL that will get you the number you need. If you tell me the column names and the table name, I can write the SQL here and you can create a new query by pasting the code posted here.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Rui,

    I was playing around with storing the count of records in an environment variable and then setting a Word Document Variable that could be retrieved in a Field Code. However, this is a problem since the Environment Variables available only update when the system is rebooted! Maybe you could write the SQL code to run from Word and then set the Document variable that way it wouldn't have to be stored in the table?

    Here's the code I was playing with.
    Code:
    Option Explicit
    
    Public Function iRecCnt() As String
      
       iRecCnt = Environ$("RECCOUNT")
      
    End Function
    
    Sub SeRecordCountDocVar()
    
       On Error GoTo ErrorHandler
       
       ActiveDocument.Variables.Add Name:="RecordCount", Value:=iRecCnt()
       
    GoTo GetOut
    
    ErrorHandler:
    
       If Err() = 5903 Then
         ActiveDocument.Variables("RecordCount").Value = iRecCnt()
       Else
         MsgBox "Problem setting RecordCount Document Variable" & vbCrLf & _
                "Error " & Format(Err()) & " encountered.", _
                vbOKOnly + vbCritical, "Error Not Handled"
       End If
       Resume Next
       
    GetOut:
    BTW, even though it wouldn't update it did pickup the current value of the Environment Variable in the Field code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    RG,

    I confess I haven't ever accessed Access from Word and it's been quite a few years since I used VBA in Word (do it in Access, Outlook and Excel but never in Word). I suppose it wouldn't be too hard, but an Access instance would need to be create from Word and a query executed. Would probably be simpler to add the count column to the query (although counting in each record would be totally excessive). The SQL is obviously very simple (SELECT COUNT(*) FROM TABLE_NAME), but I confess some doubts about the best way to do this.

  5. #5
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the fast replies Rui and RG.

    I'm new to access, and didn't realize that it doesn't make sense to do it as I had imagined.

    I had read a bit about queries, and had already managed to fairly easily make a query that counted the number of records, but did not know how to proceed from there (I tried to get the "Number_of_contacts" column to display this query value but Access would have none of that). Overall I had thought it rather elegant to simply have it shown in the table as I'd had imagined, and I would rather disappointed that it wouldn't let me. For context, this would be a small contact database, filled before every project and discarded afterwards, which would facilitate email/mail communication during the project by automatically filling in the first name, last name, and number of contacts in a document after a word merge as described in the original post.

    I'm no expert with office, but after I was tasked with this assignment I felt that one access table connected to an access form in which to fill out contact information and a word document through word merge, would do the trick. It works like a charm I'm pleased to say, except for the fact that I'd like it to display the total number of contacts in the word document as shown in the original post. If it proves to be too much of a hassle or beyond my access skills I'll just skip it so that it has to be manually entered into the word document.

    "If you tell me the column names and the table name, I can write the SQL here and you can create a new query by pasting the code posted here"

    I have one table, called "contacts", which has 4 columns: "ID" (which is automatically generated), "First_Name", "Last_name", and "Number_of_contacts"

  6. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Ok, the SQL for the query that will include the contacts can be like this:

    Code:
    SELECT ID, First_Name, Last_Name, (SELECT COUNT(*) FROM CONTACTS) As Number_of_Contacts
    FROM CONTACTS
    To use it, choose the CREATE tab -> Query Design. Close the Show Table dialog without choosing any table. At the leftmost upper tab, click SQL or click View and choose SQL View. You will have a window open some text, just paste the SQL I just posted in that window, replacing all the text that was there. Click the Exclamation Point icon at the top left to execute the query, verify that it shows what you need and then just save it, giving it the name you prefer. You can then use that query from word, for the merge you need.

    HTH

  7. #7
    New Lounger
    Join Date
    May 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, it worked like a charm and I now better understand how to work with queries. Also I learned that these can merge with a word document as well (previously I thought this only worked with tables), which helps me a lot since it allows me to control which merge fields show up in the document. Thank you for bearing with me, I took somewhat of a shortcut by coming here instead of reading a book on access, so I really appreciate the help.

  8. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Glad it worked .

Posting Permissions

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