Results 1 to 8 of 8
  1. #1
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11

    Apending value from Query to row of table

    Afternoon all,

    I have a hopefully simple requirement and cannot make access do what I want. Hoping an expert here can point me in the right direction.

    Background:
    4 tables in Access(more will be added later)


    called
    Log
    ABC1
    SDV2
    FGB3

    'Log' table has 2 columns (and others that are not important for this but are needed in the table.
    Table Name
    Count
    (so has 3 rows created in it manually - ABC1, SDV2, FGB3 and nothing in the count column)

    ABC1 has a number of columns as do the other two tables.

    What I want to create is an update query (assume this is the right option)
    that
    does a row count of each of the 3 tables
    and inserts that count into the Log table against the corresponding 'table' entry

    So to clarify
    if ABC1 had 98 rows of data, SDV2 had 50 rows of data and FGB3 had 25 rows of data

    Before running the update the 'Log' table would hold
    ABC1 -
    SDV2 -
    FGB3 -

    And after running the update the 'Log' table would hold
    ABC1 - 98
    SDV2 - 50
    FGB3 - 25

    My question is how do I go about doing this?

    I can write a simple query that does the count - but when I try to create an update query to point to a specific cell in the 'Log' table Access does not do what I want.

    Thanks
    Jon

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Is this Log table going to be updated daily?
    If so, won't the previous counts be in there, so the values won't be blank?
    Wouldn't it better to add a Date field to the Log table, and write a new record for each day, with the counts on that day?

  3. #3
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11
    No, The log table will be updated on an adhoc basis and used to trigger another process.
    e.g. if Count = Null then trigger import process. If count <> Null then trigger reconciliation process.

    No Date field needed (but thanks for making me stop and think)

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You don't actually need to do an Update Query to get these numbers returned like that. You can do that in a simple Select Query.
    If the Table Name field in your Log table is actually "Table Name", this query will return the counts you want in the format you want:
    Code:
    SELECT Log.[Table Name], DCount("*",[Table Name]) AS [Count]
    FROM Log;

  5. #5
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11
    Sorry to be really thick but I cannot get that to work.

    I have created a simple database with the raw data (and the count column in the Logging table empty).

    can you please point me as to what to do - I am more of an excel VBA person and still finding my way around access.

    Thanks
    Jon

    Hopefully a small ZIP file with an equally small access database in it.
    test.zip

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Do you not know what to do with that code?

    All you have to do is to Create a new query, but instead of building it in the Query Builder, change the View to "SQL View", and copy and paste the SQL code I posted. Then switch to database view and see if it returns what you are looking for.

    Make sure all the table and field name references I am using match what you have.

  7. #7
    JonSmile is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    11
    You are a star..

    I was being thick and trying to use query builder.. Forgot all about SQL view.

    Now sorted

    again 100 thanks.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You are welcome!
    Glad I was able to help.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-01-2017, 06:57 AM
  2. Apending to a linked table
    By paulmc1981 in forum Import/Export Data
    Replies: 2
    Last Post: 02-21-2017, 05:05 AM
  3. Replies: 2
    Last Post: 01-20-2014, 12:50 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 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