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!
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!