Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-28-2019, 01:55 AM   #1
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 13
Thanks: 11
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Please help with this report

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.
Attached Images
File Type: jpg Screenshot_1.jpg (72.0 KB, 22 views)

Arvive is offline   Reply With Quote
Old 09-28-2019, 04:46 AM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,785
Thanks: 34
Thanked 538 Times in 511 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Please help with this report

you can make it a subform linked by employee, or use dlookups in calculated controls.
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
Arvive (09-28-2019)
Old 09-28-2019, 06:50 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Please help with this report

or include those Calculated fields in your query.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Arvive (09-28-2019)
Old 09-28-2019, 10:59 AM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,272
Thanks: 0
Thanked 533 Times in 529 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Please help with this report

Never heard of "Djoin" - what is Djoin?

DLookups in textboxes on report probably simplest.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Arvive (09-28-2019)
Old 09-28-2019, 11:22 AM   #5
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Please help with this report

similar to ConcatRelated (Daniel p site).
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-28-2019, 02:29 PM   #6
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 13
Thanks: 11
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Re: Please help with this report

Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 09-28-2019, 04:13 PM   #7
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 13
Thanks: 11
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Re: Please help with this report

Thank you all for the support. Dlookup did the job.

Arvive is offline   Reply With Quote
Old 09-28-2019, 05:44 PM   #8
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,401
Thanks: 68
Thanked 2,703 Times in 2,588 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Please help with this report

you may Left join the two queries.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Change report filters and report label caption based on form command newdistrict Reports 6 07-23-2017 12:52 PM
Open report button to select data from 7 list boxes and present in a report Gavin59 Forms 19 03-07-2016 07:16 PM
[SOLVED] Open sub report from main report by selecting report name scopes456 Reports 3 12-15-2015 11:01 AM
Does MS Access Report support Hide/Show fields at the Report View time? accesser2003 General 3 06-13-2014 06:15 AM
Invoice report problem, report show items from previous invoices. saltyseadog Reports 1 04-16-2013 07:30 AM




All times are GMT -8. The time now is 04:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World