SQL Query updates table w/ record count

[Fireman1143]

I am trying to have a SQL query update a table with the record count from another table which meets the criteria of between two dates from a popup date form. The date part works in other similiar coding where I am pulling individual records by an ID field. This is differnt in that I need the total meetings held between two dates.

The table is "MEETING" and the part after WHERE is used elsewhere and works. My error message notes - "3141: The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or punctuation is incorrect."


Set mtgcnt = db.OpenRecordset("SELECT Meeting.mtgDate, Count(MEETING.mtgID) AS Cnt, from MEETING WHERE (((MEETING.MtgDate)>= #" & begdate & "# And (MEETING.MtgDate) <= #" & enddate & "#));")

It's been a few years since I helped write this application and I thought the change would be easy. Thanks for the assist.

 

[dhookom]

Your SQL statement would need to GROUP BY MtgDate if you leave it in the SELECT phrase.

Duane
Hook'D on Access
MS Access MVP

 

[Fireman1143]

Thanks dhookom. I tried GROUPing and I must be well off on the syntax.

Table Name: persStanding_TrnMtg
fld_Total_Trn
fld_Total_Mtg

The code below works to get the individual count of the training attendence for each individual within the dates selected. I would like to do the same type coding to update the same table with the TOTAL COUNT of training dates, not just the ones attended by an individual. The fields to be updated are listed above. Each time I do it I have received syntax errors or reserved word use and do not see where I was wrong.



begdate = Me.Beginning_Date
enddate = Me.Ending_Date

Set trg = db.OpenRecordset("SELECT Count(TRNPER.TrnPersonnelID) AS Cnt, CInt(Trim([TrnPersonnelID])) AS PID FROM TRAINING INNER JOIN TRNPER ON TRAINING.TrnDrillNumber = TRNPER.TrnDrillNumber WHERE (((Training.TrnDrillDate) >= #" & begdate & "# And (Training.TrnDrillDate) <= #" & enddate & "#)) GROUP BY CInt(Trim([TrnPersonnelID]))")

If trg.RecordCount < 1 Then

Else

trg.MoveFirst

Do While Not trg.EOF
trgid = trg!PID
Set prstd = db.OpenRecordset("Select * from persStanding_TrnMtg where fld_id = " & trgid & "")
prstd.Edit
prstd!fld_member_totals = prstd!fld_member_totals + trg!Cnt
prstd.Update
trg.MoveNext
Loop

End If

 

[dhookom]

I don't believe in saving a value that can be calculated. Can you explain your table structures and why you think you need to save a value that should be dynamically calculated as needed?

Duane
Hook'D on Access
MS Access MVP

 

[Fireman1143]

Thanks dhookom for the insight.

I "inherited" this coding from a programmer that had done the work previously. He has it set to save values to a table, which is completely emptied at the start of each time a report is run. My thought was to continue his methods (which I need to admit are probably above my level) but if there is another way, I am game.

 

[dhookom]

The total meetings held between two dates can be a simple domain aggregate function:

DCount("*","Meeting","MtgDate Between #" & Forms!frmDates!txtBegDate & "# and #" & Forms!frmDates!txtBegDate & "#")

I'm not sure why create a recordset.

Duane
Hook'D on Access
MS Access MVP