Please help with this report (1 Viewer)

Arvive

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

  • Screenshot_1.jpg
    Screenshot_1.jpg
    72 KB · Views: 114

MajP

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:06
Joined
May 7, 2009
Messages
19,169
or include those Calculated fields in your query.
 

June7

AWF VIP
Local time
Today, 01:06
Joined
Mar 9, 2014
Messages
5,423
Never heard of "Djoin" - what is Djoin?

DLookups in textboxes on report probably simplest.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:06
Joined
May 7, 2009
Messages
19,169
similar to ConcatRelated (Daniel p site).
 

Arvive

Registered User.
Local time
Today, 09:06
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, 09:06
Joined
Aug 14, 2019
Messages
13
Thank you all for the support. Dlookup did the job.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:06
Joined
May 7, 2009
Messages
19,169
you may Left join the two queries.
 

Users who are viewing this thread

Top Bottom