2

I followed the tips by others to produce an access query.

I have two tables. See figure1. And the result is figure2.

Figure1 http://img.libk.info/f1.png http://img.libk.info/f1.png

Figure2 http://img.libk.info/f2.png http://img.libk.info/f2.png

The method to generate the result query is solved in another question.

The query script :

TRANSFORM Nz(Count([number]),0) AS CountValue
SELECT Table1.ID
FROM Table1, Table2
WHERE (((Table2.number) Between [table1].[start] And [table1].[end]))
GROUP BY Table1.ID
PIVOT DatePart("yyyy",[ndate]);

My question is:

Is there anyway to write back the query result to table 1?

I want to add two new columns in table 1. And be able to add or update the query value to the field base on its "ID".

I'm not sure my description is clear or not. Hope you may understand and thanks for your help!

Community
  • 1
  • 1
Webster H.
  • 35
  • 1
  • 11

1 Answers1

0

You won't be able to do it directly. However, here are two ways it could be done indirectly.

Method 1: Temp Table

This method is best for a quick-and-dirty one-time solution.

  1. Create a Make-Table query based on your query and use it to make a temporary table.
  2. Use the temporary table joined to [Table 1] to update your two new fields.
  3. Delete the temporary table

Method 2: VBA Routine

This method is best when you want a repeatable method. It's overkill if you're only going to do it once. However, if you want calculated values for every year, you'll need to run it again.

  1. Read the query into a recordset
  2. Loop through the Recordset and for each ID, either
    • Run a sql statement to update table 1, or
    • open a second recordset querying by the ID and Edit/Update

Here's a simple version that updates the value for a single year.

Public Sub UpdateAnnualTotal(ByVal nYear As Long)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim sId As String
    Dim nTotal As Long

    Set db = CurrentDb

    sSQL = "SELECT [ID],[" & nYear & "_count"] FROM AnnualTotalsQuery"
    Set rs = db.OpenRecordset(sSQL)
    With rs
        Do Until .EOF

            sId = .Fields("ID").Value
            nTotal = .Fields(nYear & "_count").Value

            sSQL = "UPDATE [Table 1] SET [" & nYear & "_count"] = " & nTotal  _
                & " WHERE [ID] = '" & sId & "'"

            db.Execute sSQL

            .MoveNext
        Loop

        .Close

    End With

End Sub
Don Jewett
  • 1,867
  • 14
  • 27
  • Hello, thanks for your reply. I'm not familiar with VBA. If I want to try method 2, I need to click the "visual basic" button? and insert the script into it? But I have no idea how to trigger it? @@ – Webster H. Aug 04 '15 at 11:22
  • I want to mix method1 and method2. When I run the query, it will creat a temp table.(my temp table will only have data in same year now, eg: all of them will be 2000) Will it possible for you to teach me how to use a table and to update another table's field base on its ID? Thank you > – Webster H. Aug 04 '15 at 11:29