Report from Query(ies) issue (1 Viewer)

[SimonD]

Hi guys,

I'm building a report based on a query, so far I have everything I need except for one field. (here is a summary of current state)

Here is the query so far (information from 2 tables)

SELECT [strLastName] & "." & Right([ServiceNumberID],3) AS [Name of case],
tblmember.strRank,
tblCase.datPreferral,
tblCase.datConvenedFor,
DateDiff("d",[tblCase].[datPreferral],[tblCase].[datConvenedFor]) AS [Number of days between preferral and date convened for], tblCase.datTerminated,
DateDiff("d",[tblCase].[datPreferral],[tblCase].[datTerminated])+1 AS [Number of days between preferral and date of completion]
FROM tblCase INNER JOIN tblmember ON tblCase.CaseID = tblmember.CaseID
WHERE (((tblCase.datPreferral)>=#1/1/2017# And (tblCase.datPreferral)<=Date()))
ORDER BY [strLastName] & "." & Right([ServiceNumberID],3);

my issue is my last field for my report is based on a calculation from another table.

Relationship (just showing the relevant fields):

[tblmember]
CaseID
ServiceNumberID
strLastName
strRank

[tblCase] (has a one-to-many relationship with [tblmember] & [tblHearing]
datPreferral
datConvenedFor


[tblHearing]
CaseID
intCourtdays <---- this is what my last field will calculate

The field I need to add to my report is the total number of court days for each Case, if a case has more than one hearing, I want it to add the number of days of each hearing under that case. if the case has no hearing, than 0 should be displayed.

Note: in the form based on Case with a subform based on Hearings, I have a text box (in the footer) that shows me that total
=Sum([intCourtDays])
since the parent/child relationship is based on CaseID, it adds all the data from multiple court days (intCourtDays).

but when I add the [tblHearing] to my query, without even selecting anything from that table, I go from 54 records to 10 records when running the query. which I don't want, I want 54 records.

So do I have to create another query and simply add that query result to the report, or merge it with the first query before creating my report?

hope this make sense!

 

[Minty]

Create a query that lists all Case ID's and sums the records in tblHearing use a left join to return all tblCaseID -> tblHearings. Call it qryHearingCount. Wrap the count in the NZ() function.

Now add that query to your original query and join on the case ID.

 

[SimonD]

I'm going to try that! thank you

 

[SimonD]

Create a query that lists all Case ID's and sums the records in tblHearing

I do not know which formula to use to have the query just sum up the intCourtdays for those that have the same CaseID, and not the others intCourtdays of other CaseID.

example of data:
Case ID (from [tblCase]) / intCourtDays (from [tblHearing])
1 / 0.5
2 / 0
3 / 0
4 / 1
5 / 4
5 / 2
6 / 4

I want Case Id 5 / 6 court days (which is the two hearings of 4 days + 2 days from the table above)

use a left join to return all tblCaseID -> tblHearings. Call it qryHearingCount. Wrap the count in the NZ() function.

Now add that query to your original query and join on the case ID.

for this portion thank you very much, I forgot about the left join, works great, and the NZ function, I read about it and applied it and it works.

 

[Minty]

Add a group by clause to group on the case ID. (Press the totals button in the query designer)

 

[arnelgp]

Just an Expression on your query using SQL view

DSum("IntCourtDays","tblHearing","CaseID=" & [tblCase].[CaseID]) As TotalCourtDays

 

[Minty]

Personally I wouldn't really use an domain function in a query - they can get get very slow.

 

[SimonD]

I'm reading up and learning about grouping by clause, it seems exactly what I need, thank you for pointing me in the right direction.

 

[arnelgp]

Slow is myth now, we new a2016, i7, etc.