Please help with this report (1 Viewer)

Arvive

Registered User
Joined
Aug 14, 2019
Messages
13
I have this report using a Djoin query which brings out multiple rows.
I need to bring to the report records from another query that brings only a single row which will populate the fields in the area marked yellow. Kindly assist.
Please see the image of the report attached.
 

Attachments

MajP

Surrender Dorothy!" That's all!
Joined
May 21, 2018
Messages
2,420
you can make it a subform linked by employee, or use dlookups in calculated controls.
 

arnelgp

error reading drive A:
Joined
May 7, 2009
Messages
8,631
or include those Calculated fields in your query.
 

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
Never heard of "Djoin" - what is Djoin?

DLookups in textboxes on report probably simplest.
 

Arvive

Registered User
Joined
Aug 14, 2019
Messages
13
or include those Calculated fields in your query.
@arnelgp Please, these are the two queries below. I need help merging them.

Query 1 Brings multi rows----

SELECT tbldaywork.empno, tbldayworkdetails.datecompleted, DJoin("descode","(select tbldayworkdetails.datecompleted, tbldayworkdetails.descode, tbldaywork.empno from tbldaywork INNER JOIN tbldayworkdetails on tbldaywork.workid = tbldayworkdetails.headid )","tbldaywork.empno =" & [tbldaywork.empno] & "and tbldayworkdetails.datecompleted =#" & Format([tbldayworkdetails.datecompleted],"yyyy\/mm\/dd") & "#") AS DESIGNS, Sum(tbldayworkdetails.weight) AS [TOTAL WEIGHT], Count(tbldayworkdetails.descode) AS [TOTAL DESIGNS]
FROM tblEmployees INNER JOIN (tbldaywork INNER JOIN tbldayworkdetails ON tbldaywork.workid = tbldayworkdetails.headid) ON tblEmployees.EMPNO = tbldaywork.EMPNO
WHERE (((tbldayworkdetails.completed)=True) AND ((tbldayworkdetails.datecompleted) Is Not Null And (tbldayworkdetails.datecompleted) Between [Forms]![frmBeadersearningreport]![txtdate10] And [Forms]![frmBeadersearningreport]![txtdate11]))
GROUP BY tbldaywork.empno, tbldayworkdetails.datecompleted, DJoin("descode","(select tbldayworkdetails.datecompleted, tbldayworkdetails.descode, tbldaywork.empno from tbldaywork INNER JOIN tbldayworkdetails on tbldaywork.workid = tbldayworkdetails.headid )","tbldaywork.empno =" & [tbldaywork.empno] & "and tbldayworkdetails.datecompleted =#" & Format([tbldayworkdetails.datecompleted],"yyyy\/mm\/dd") & "#")
HAVING (((tbldaywork.empno)=[Forms]![frmBeadersearningreport]![cboEmplist10]));

Query 2 brings a single row

SELECT tblEmployees.EMPNO, tblBeaderdeduction.Years, tblBeaderdeduction.Months, tblBeaderdeduction.SalAdvance, tblBeaderdeduction.SSNIT, tblBeaderdeduction.Loan, tblBeaderdeduction.Surcharge, [SalAdvance]+[SSNIT]+[Loan]+[Surcharge] AS Total
FROM tblEmployees INNER JOIN tblBeaderdeduction ON tblEmployees.EMPNO = tblBeaderdeduction.Empno
WHERE (((tblEmployees.EMPNO)=[Forms]![frmBeadersearningreport]![cboEmplist10]) AND ((tblBeaderdeduction.Years)=Format([Forms]![frmBeadersearningreport]![txtdate11],"yyyy")) AND ((tblBeaderdeduction.Months)=Format([Forms]![frmBeadersearningreport]![txtdate11],"mm")));
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom