Please help with this report (1 Viewer)

Arvive

Registered User.
Local time
Today, 11:46
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

You've got your good things, and you've got mine.
Local time
Today, 06:46
Joined
May 21, 2018
Messages
2,806
you can make it a subform linked by employee, or use dlookups in calculated controls.
 

arnelgp

error reading drive A:
Local time
Today, 18:46
Joined
May 7, 2009
Messages
8,809
or include those Calculated fields in your query.
 

June7

AWF VIP
Local time
Today, 02:46
Joined
Mar 9, 2014
Messages
2,775
Never heard of "Djoin" - what is Djoin?

DLookups in textboxes on report probably simplest.
 

arnelgp

error reading drive A:
Local time
Today, 18:46
Joined
May 7, 2009
Messages
8,809
similar to ConcatRelated (Daniel p site).
 

Arvive

Registered User.
Local time
Today, 11:46
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")));
 

Arvive

Registered User.
Local time
Today, 11:46
Joined
Aug 14, 2019
Messages
13
Thank you all for the support. Dlookup did the job.
 

arnelgp

error reading drive A:
Local time
Today, 18:46
Joined
May 7, 2009
Messages
8,809
you may Left join the two queries.
 

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

Top Bottom