Display total of a subforms entries on Main From - Query for Subforms Total

[MadAtMicrosoft]

I am creating a portion of a db wherein Employees input their day's tips. According to IRS rules, shared tips are to be documented. Thus I have created a Second table for Shared Tips.

***************************************************
A) The Main table = tblActualTipsReported has the following:

1) ActualTipsReported ID (PK) (Autonumber)
2) EmployeeID - Linked to tblEmployees
3) DateTimeStamp (General Date) (Default = Now()) {Not Visible}
4) TipDate (Short Date) (Default = (Now()) {Visible & UnLocked}
5) ActualDirectTips (Currency)
6) CreditDebitTips (Currency)


​

B) The Table feeding the subform = tblSharedTips

1) SharedTipsID
2) ReportingEmployeeID (Linked to tblEmployees)
3) DateTimeStamp (General Date) {Not Visible}
4) DateTipShared (Short Date) (Default = (Now()) {Visible & Locked}
5) TipSharedAmt (Currency)
6) ActualTipsReportedID (Link to tblActualTipsReported)
7) SharedWithEmployeeID (Linked to tblEmployees)​

******************************************************

I then created a Query = qselSharedTipsTotals to Subtotal by EmployeeID and ActualTipsReportedID the total of that day's shared tips with an Alias: DateTipsShared By Day


I created a Main Form (Tips Reporting) where the Employee Reports the Gross tips they received and then Via a Control Button they click to open the input of Shared Tips that opens another Form frmSharedTips


I am trying without success to get the total from my query of the total shared tips back onto the main form (Visable but Locked) and get the ?Name error.

I know I'm missing something in some Select Where statement.

Any suggestions?

 

[the_net_2.0]

would it be possible for you to post a screen shot of the query's sql, along with a ss of the mainform/subform combination?

As a side note, I know some waiters in my own town and the reported cash tips they receive are not accurate too often...

 

[MadAtMicrosoft]

would it be possible for you to post a screen shot of the query's sql, along with a ss of the mainform/subform combination?

As a side note, I know some waiters in my own town and the reported cash tips they receive are not accurate too often...

****************************************

Here They are, And below is the SQL Coding But it is also in a jpg attached

SELECT tblActualTipsReported.ActualTipsReportedID, tblActualTipsReported.EmployeeID, tblActualTipsReported.DateTimeStamp, tblActualTipsReported.TipDate, tblActualTipsReported.ActualDirectTipsAmount, tblActualTipsReported.CreditDebitTipsAmount, tblActualTipsReported.TtlTipsSharedPaidToOthers, CCur(Nz([ActualDirectTipsAmount],0)+Nz([CreditDebitTipsAmount],0)) AS TotalTipsReceived, qselSharedTipsTotals.[Sum Of TipSharedAmt], [ActualDirectTipsAmount]+[CreditDebitTipsAmount]-[qselSharedTipsTotals]![Sum Of TipSharedAmt] AS NetTips
FROM tblActualTipsReported INNER JOIN qselSharedTipsTotals ON (tblActualTipsReported.EmployeeID = qselSharedTipsTotals.ReportingEmployeeID) AND (tblActualTipsReported.ActualTipsReportedID = qselSharedTipsTotals.ActualTipsReportedID)
GROUP BY tblActualTipsReported.ActualTipsReportedID, tblActualTipsReported.EmployeeID, tblActualTipsReported.DateTimeStamp, tblActualTipsReported.TipDate, tblActualTipsReported.ActualDirectTipsAmount, tblActualTipsReported.CreditDebitTipsAmount, tblActualTipsReported.TtlTipsSharedPaidToOthers, qselSharedTipsTotals.[Sum Of TipSharedAmt], [ActualDirectTipsAmount]+[CreditDebitTipsAmount]-[qselSharedTipsTotals]![Sum Of TipSharedAmt];

 

[MadAtMicrosoft]

PS, FWIW I don't think ANY of them report them all. But we will have gone beyond the norm to try to get their info so they can't say much of anything to us. Just trying to stay a 1/2 step ahead of those guys if you know what I mean.