Formula for Group Footer (1 Viewer)

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
Okay, now, try switching the expression back to the original, but keep the Textbox in the Details section still.
Prompt to enter paramater value for TankDate pops up again
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
For that short period of time, I won't be able to see it, since I am only using my phone right now. Hopefully, somebody else will be able to. Otherwise, I'll be back in front of a computer in about two hours.
Ok...that will be 9pm over here so I will wait for your help as I really want to get this report completed tonight.

thank you so much
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:48
Joined
Oct 29, 2018
Messages
21,358
Prompt to enter paramater value for TankDate pops up again
Sounds like your control's name is the same as the field's name. Try changing the name of the control.
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
No no...different names. I haven't even named the calculated field in the group footer - still the generic TextNo

In the details section I have highlighted the [TotalBases] and [TotalTanks] which are what I need to sum in the group footer
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:48
Joined
Oct 29, 2018
Messages
21,358
No no...different names. I haven't even named the calculated field in the group footer - still the generic TextNo

In the details section I have highlighted the [TotalBases] and [TotalTanks] which are what I need to sum in the group footer
No. I was referring to the Textbox for TotalTanks. Is it also on the report, and the names are different?
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
The TotalTanks textbox is in the report only. It is not on any form or in the source query.

Due to the info I need to show in this report there are quite a few Dlookup fields to get the activity dates.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:48
Joined
Oct 29, 2018
Messages
21,358
The TotalTanks textbox is in the report only. It is not on any form or in the source query.

Due to the info I need to show in this report there are quite a few Dlookup fields to get the activity dates.
Hmm, if TotalTanks is not in the Record Source for the report, then that's probably why you were getting prompted for it. Try adding it to the record source.
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
This might not be possible.

The problem I am getting is that a work order is only generated when a property is inspected prior to installation. In the WorkOrder table I have all the date fields that I need for this particular report. However, if I run a WorkOrder report it is not showing all the candidates per parish, only those that have been inspected so far; and because of this some candidates have been overlooked by our field technicians.

So, using the qryCandidates (which gives their contact details and tank systems TO BE INSTALLED) I created this report which would show ALL candidates and added lookup fields to add the dates from the WorkOrder table. This way I can give this report to the field technicians for them to ensure they have visited everybody and can see what stage of installation each candidate is at and know how to action going forward.

The TotalTanks in this report is to give the sum of the number of tanks installed (once a date has been added to the Tank Installation Date field). All the date fields in this report are Dlookup fields.

I hope this helps explain my dilemma.
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
The group summary is for my boss' reference so he can quickly see totals per group and per parish
 

June7

AWF VIP
Local time
Today, 06:48
Joined
Mar 9, 2014
Messages
5,423
TankDate is not a field, it is a textbox with a calculation.

A candidate can have only one work order?

Domain aggregate functions can cause slow performance in queries/forms/reports (this report has 7 such expressions). Why don't you include tblWorkOrder in report RecordSource? Why an aggregate query? There are 1684 tblCandidates records but only 1670 in query. Is this what you would want? If you want to show all candidates, don't use INNER JOIN.
 
Last edited:

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
Hi

Only 1 work order is generated per candidate and is only generated once the pre-installation inspection is done. If I were to include the tblWorkOrder in this particular report, the report would exclude all those who have not been inspected yet. I need to show all candidates so that we can see each one's status is.

The difference in the number of candidates between tblCandidates and this report is that some either have declined to receive tanks or could not be located. There is a checkbox in tblCandidates named Remove; the source for the report only shows the candidates which that box remains unchecked.
 

June7

AWF VIP
Local time
Today, 06:48
Joined
Mar 9, 2014
Messages
5,423
So not ALL candidates.
Consider this query as report RecordSource.

SELECT tblCandidates.CandidateID, tblCandidates.Name, tblCandidates.Address, tblParishes.Parish, tblCandidates.ContactInfo1, tblCandidates.ContactInfo2, tblCandidates.ContactInfo3, tblCandidates.Notes, tblCategories.Category, tblCandidates.System, tblWorkOrder.PreInspectionDate, tblWorkOrder.BaseSubstructureDate, tblWorkOrder.BaseInstallationDate, tblWorkOrder.TankInstallationDate, tblWorkOrder.PlumbingConnectionsDate, tblWorkOrder.ElectricalConnectionsDate, tblWorkOrder.InspectedOn_IPS, tblWorkOrder.CommissionDate
FROM tblParishes RIGHT JOIN (tblCategories RIGHT JOIN (tblCandidates LEFT JOIN tblWorkOrder ON tblCandidates.CandidateID = tblWorkOrder.CandidateID) ON tblCategories.CategoryID = tblCandidates.CategoryID) ON tblParishes.ParishID = tblCandidates.ParishID;

Can include WHERE clause to filter on Remove or apply filter when report opens.

Now the count on System multi-value field can be done with DCount(). I tried using Count() and it errors.
=DCount("System.Value","tblCandidates","CandidateID=" & [CandidateID])

Could even do that DCount() in query so it is then a field that can be referenced with Sum() expression on report.

Another approach would be a query that expands the multi-value field elements to separate rows. Then can use Count() and Sum() expressions on the expanded dataset. Set textbox HideDuplicates property to Yes.

Report is too wide for page size. Need to adjust width of some textboxes.
 
Last edited:

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
So not ALL candidates.
Consider this query as report RecordSource.

SELECT tblCandidates.CandidateID, tblCandidates.Name, tblCandidates.Address, tblParishes.Parish, tblCandidates.ContactInfo1, tblCandidates.ContactInfo2, tblCandidates.ContactInfo3, tblCandidates.Notes, tblCategories.Category, tblCandidates.System, tblWorkOrder.PreInspectionDate, tblWorkOrder.BaseSubstructureDate, tblWorkOrder.BaseInstallationDate, tblWorkOrder.TankInstallationDate, tblWorkOrder.PlumbingConnectionsDate, tblWorkOrder.ElectricalConnectionsDate, tblWorkOrder.InspectedOn_IPS, tblWorkOrder.CommissionDate
FROM tblParishes RIGHT JOIN (tblCategories RIGHT JOIN (tblCandidates LEFT JOIN tblWorkOrder ON tblCandidates.CandidateID = tblWorkOrder.CandidateID) ON tblCategories.CategoryID = tblCandidates.CategoryID) ON tblParishes.ParishID = tblCandidates.ParishID;

Can include WHERE clause to filter on Remove or apply filter when report opens.

Now the count on System multi-value field can be done with DCount(). I tried using Count() and it errors.
=DCount("System.Value","tblCandidates","CandidateID=" & [CandidateID])

Could even do that DCount() in query so it is then a field that can be referenced with Sum() expression on report.

Another approach would be a query that expands the multi-value field elements to separate rows. Then can use Count() and Sum() expressions on the expanded dataset. Set textbox HideDuplicates property to Yes.

Report is too wide for page size. Need to adjust width of some textboxes.
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
I can't thank you enough for your help on this. I changed the source query as you outlined above, removing all the Dlookup formulas. Then in the group footer I added the formulas =Sum(IIf([BaseInstallationDate] Is Null,Null,[CountOfSystems])) and =Sum(IIf([TankInstallationDate] Is Null,Null,[CountOfSystems])) to get the total bases cast and total tanks installed per category.

Now I can send off my reports first thing tomorrow morning.

Thank you as well as theDBguy for all your help
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:48
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
Hi again. I need help with one more thing. Where ever we have to install a 1000-gal system, the candidate is receiving 2# tanks per 1000-gal system.

I'd like the DCount formula to include a criteria that would look for "*1000-gal*" and multiply by 2 so I can get the actual number of tanks installed as opposed to the number of systems.

This is the current formula

CountOfSystems: DCount("System","tblCandidates","CandidateID=" & [tblCandidates]![CandidateID])

I tried adding a column in the source query with the below formulas but the results are incorrect

IIf([System].[Value] Like "*1000-gal*",[CountOfSystems]*2,[CountOfSystems]*1)

I also tried this formula, but again the results are incorrect. No error messages, just not returning the correct result.

IIf([System].[Value] Like "*1000-gal*",DCount("System","tblCandidates","CandidateID=" & [tblCandidates]![CandidateID])*2, DCount("System","tblCandidates","CandidateID=" & [tblCandidates]![CandidateID])*1)

For example, I have a school that is to receive 2#x1000-gal systems which equates to 4# tanks in total. The above formulas are both returning a value of 2 for that school instead of the correct value 4
 

June7

AWF VIP
Local time
Today, 06:48
Joined
Mar 9, 2014
Messages
5,423
It doesn't work because the actual value saved in the multi-value field is a number - the ID from tblSystems. Let's take a different approach. Build the following query object:

qryCandidatesTankCount

SELECT tblCandidates.CandidateID, Sum(IIf(InStr([tblSystems].[System],"1000")>0,2,1)) AS TankCount
FROM tblSystems RIGHT JOIN tblCandidates ON tblSystems.SystemID = tblCandidates.System.Value
GROUP BY tblCandidates.CandidateID;

Now just include that query like a table in the report RecordSource joining on CandidateID fields. Bind textbox to TankCount field.

No domain aggregate functions.
 
Last edited:

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
Thank you. I will try and let you know if it works. I was able to send off the other reports this morning thanks to your great assistance. I used excel to create the report with the actual sum of the tanks but I will try your suggestion so next time I can run it in Access
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:48
Joined
Feb 19, 2002
Messages
42,970
If you don't install 1000 gallon tanks, take them out of the product list!!!! You do not what to hardcode stuff like this. It is stuff like this that makes applications impossible to maintain. Just order TWO 500 gallon tanks.
 

Tophan

Registered User.
Local time
Today, 10:48
Joined
Mar 27, 2011
Messages
362
It doesn't work because the actual value saved in the multi-value field is a number - the ID from tblSystems. Let's take a different approach. Build the following query object:

qryCandidatesTankCount

SELECT tblCandidates.CandidateID, Sum(IIf(InStr([tblSystems].[System],"1000")>0,2,1)) AS TankCount
FROM tblSystems RIGHT JOIN tblCandidates ON tblSystems.SystemID = tblCandidates.System.Value
GROUP BY tblCandidates.CandidateID;

Now just include that query like a table in the report RecordSource joining on CandidateID fields. Bind textbox to TankCount field.

No domain aggregate functions.
Thank you again for your help! This is counting the tanks per system as I needed. I can now use this for the next week's reports.
 

Users who are viewing this thread

Top Bottom