Results 1 to 4 of 4
  1. #1
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33

    Pull data from one table and calculate and post into field in another table

    I have a subform where I enter the date someone attends a meeting and then this information flows into my table "Meeting Dates". I want to take this information from Meeting Dates and count the number of times an individual attends a meeting during a specific year and then have this data fill in the appropriate field in my "Attendees" table. For instance: AttendeeID = 64, 2020 Attendance = 3



    "Attendees" Table: Primary Key = AttendeeID, Field to drop data into "2020 Attendance"
    "Meeting Dates" Table: MtgDates, AttendeeID

    I found something online and thought it could work.
    SELECT T1.Employee_ID,
    (SELECT COUNT(*) AS CNT FROM Employees AS T2 WHERE T2.Manager_ID =
    T1.Employee_ID) AS Supervised_Count
    FROM Employees AS T1;


    Now, if I could just figure out where to plug my data into.
    SELECT MEETING DATES.AttendeeID,
    (SELECT COUNT(*) AS CNT FROM XXXX WHERE XXXX = XXXX) AS 2020 Attendance
    FROM XXXX AS XXXX

    HOWEVER.. if this isn't going to work let me know what might. : )

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a totals query based on your MeetingDates (suggest to not use spaces in field or object names in Access) in which you Group By AttendeeID, Year([MeetingDate]) and Count([MeetingId]). That will give you a nice summary for each AttendeeID and Year. I suggest you do not store that in your other table but rather get the info from this query as needed (for example you can use a dLookup in a form to get the record matching the AttendeeID and Year).

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

  3. #3
    kloun04 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Wisconsin
    Posts
    33
    That seemed to work Thank you!

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 6
    Last Post: 03-14-2019, 03:39 PM
  2. Replies: 4
    Last Post: 07-06-2018, 10:33 AM
  3. Replies: 1
    Last Post: 06-12-2015, 02:02 PM
  4. Replies: 2
    Last Post: 03-07-2013, 04:50 PM
  5. Replies: 3
    Last Post: 02-06-2013, 10:00 PM

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