I have gotten this far, but I am unsure how to putall of these different SQL statements into one query
Code:
Function Price()
Dim strSql As String, strDynaTbl As String
Dim db As DAO.Database, rst As DAO.Recordset, qry As DAO.QueryDef, fld As Date
Set db = CurrentDb
strSql = "SELECT tbl1.[Current Date], tbl1.Location, tbl1.Quantity FROM tbl1"
Set rst = db.OpenRecordset(strSql)
Do Until rst.EOF
fld = rst("[Current Date]").Value
strDynaTbl = "[tbl" & Year(fld) & "]"
strSql = "SELECT tbl1.[Current Date], tbl1.Location,tbl1.Quantity, " & _
"[tbl1].[Quantity]*" & strDynaTbl & ".[Price] AS [Total Price], " &
FROM tbl1 INNER JOIN" & strDynaTbl & " ON tbl1.Location= " _
& strDynaTbl & ".Location"
rst.MoveNext
Loop
rst.Close
qry.Close
Set qry = Nothing
Set rst = Nothing
End Function