Use SQL Statement in Code to pull specific data (1 Viewer)

[skwilliams]

I have a daily table with fields named "Date" and "Count" and weekly
table with fields named "Date", MonCount, TueCount, WedCount,
ThuCount, FriCount, and SatCount.

I would like to set it up where I can input the Monday date on the
Weekly Form and click the button to execute the SQL statement so the
Count field on the Monday form is moved to the MonCount field on the
Weekly table. Same with the other days of the week.

This is the code I have, but it's not working properly.

Private Sub Command390_Click()

On Error GoTo Err_Command390_Click

DoCmd.RunSQL "INSERT INTO[Total]([oRstMon]) SELECT [RstTot]FROM
[Daily] WHERE [Daily].[Date]=[Total].[Date]"
Exit_Command390_Click:

Exit Sub
Err_Command390_Click:

MsgBox Err.Description

Resume Exit_Command390_Click

End Sub

Can someone tell me the problem with this code? Or do I need something
different.

Thanks.

 

[Rich]

Have to ask why if you already have counts entered by date, you then need to transfer them to another table with each day as a separate field? Use a crosstab query.

 

[skwilliams]

The data goes to different fields in the weekly form depending on the date. I was told by another developer that a crosstab query wouldn't work for this.

If you have suggestions, I'd be happy to try them.

Thanks.

 

[Pat Hartman]

I think the point of Rich's question is WHY are you storing the data twice. A crosstab will look like the second table. It just would not be updateable.