Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-11-2019, 05:47 AM   #1
alanrcooke
Newly Registered User
 
Join Date: Aug 2019
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
alanrcooke is on a distinguished road
Two queries into one (Income minus Expenses)

I have two queries which lists (datasheet) all income for a tax year - with calculated total. The other lists all expenses for the tax year - with calculated total.

Currently I simply note down each total and calculate my net income.

The tables and queries are not related in any way.

What I want to know is if there is a way I can combine the two queries in such a way that I can output the net income both as datasheet and hopefully a report as well for my records.

Thanks in advance

Alan

PS if required I can provide the SQL for both queries.

alanrcooke is offline   Reply With Quote
Old 11-11-2019, 05:54 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,480
Thanks: 58
Thanked 1,417 Times in 1,398 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Two queries into one (Income minus Expenses)

Hi. Yes, please provide the SQL and sample data plus the formula to calculate the net income.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-11-2019, 06:08 AM   #3
alanrcooke
Newly Registered User
 
Join Date: Aug 2019
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
alanrcooke is on a distinguished road
Re: Two queries into one (Income minus Expenses)

Thanks for quick response.

This is the SQL to output 'gross income':

SELECT tblClient.fldClient, tblWorkCompleted.fldInvoiceNumber AS Invoice, qryWorkCompTransDetails.fldWorkDate, tblWorkCompleted.fldInvoiceDate, qryWorkCompTransDetails.fldDescription, qryWorkCompTransDetails.fldHours, qryWorkCompTransDetails.fldHoursRate, Sum((Nz([fldHours]*[fldHoursRate]))) AS [Total Hours], Sum(qryWorkCompTransDetails.fldDayRate) AS [Total Day Rate], qryWorkCompTransDetails.fldMiles AS [Miles Done], qryWorkCompTransDetails.fldMilesRate AS [Rate per Mile], Sum((Nz([fldMiles]*[fldMilesRate]))) AS [Total Travel], Sum(qryWorkCompTransDetails.fldMisc) AS [Total Misc], Sum((Nz([fldHours]*[fldHoursRate])+(Nz([fldMiles]*[fldMilesRate]))+(Nz([fldDayRate]+(Nz([fldMisc],0)))))) AS [Total Invoiced], Sum(((Nz([fldHours]*[fldHoursRate])+(Nz([fldMiles]*[fldMilesRate]))+(Nz([fldDayRate]+(Nz([fldMisc],0))))))/12) AS [Month Avg]
FROM tblClient INNER JOIN (tblWorkCompleted LEFT JOIN qryWorkCompTransDetails ON tblWorkCompleted.[fldTransactionsPK] = qryWorkCompTransDetails.[fldWorkCompletedID]) ON tblClient.fldClientPK = tblWorkCompleted.fldAccountID
GROUP BY tblClient.fldClient, tblWorkCompleted.fldInvoiceNumber, qryWorkCompTransDetails.fldWorkDate, tblWorkCompleted.fldInvoiceDate, qryWorkCompTransDetails.fldDescription, qryWorkCompTransDetails.fldHours, qryWorkCompTransDetails.fldHoursRate, qryWorkCompTransDetails.fldMiles, qryWorkCompTransDetails.fldMilesRate, tblWorkCompleted.fldAccountID, tblWorkCompleted.fldAccountNumber, qryWorkCompTransDetails.fldWorkDate
HAVING (((tblWorkCompleted.fldInvoiceDate)>="06/04" & "/" & [Enter start year] And (tblWorkCompleted.fldInvoiceDate)<="05/04" & "/" & [Enter end year]) AND ((qryWorkCompTransDetails.fldDescription) Like "Work Completed - " & "*"))
ORDER BY tblWorkCompleted.fldInvoiceNumber DESC;


This is the SQL to output the 'Expenses'

SELECT tblExpenses.fldDate AS [Exp Date], Sum(tblExpenses.fldExpense) AS [Total Exp], tblExpenses.fldNotes, tblClient.fldClient AS Client, tblAllocation.fldAllocation AS Allocation, tblVendors.fldVendor AS Vendor, tblExpenses.fldExpensesPK
FROM tblAllocation INNER JOIN (tblVendors RIGHT JOIN (tblProject INNER JOIN (tblClient INNER JOIN tblExpenses ON tblClient.fldClientPK = tblExpenses.fldClientFK) ON tblProject.fldProjectPK = tblExpenses.fldProjectFK) ON tblVendors.fldVendorPK = tblExpenses.fldVendorFK) ON tblAllocation.fldAllocationPK = tblExpenses.fldAllocationFK
GROUP BY tblExpenses.fldDate, tblExpenses.fldNotes, tblClient.fldClient, tblAllocation.fldAllocation, tblVendors.fldVendor, tblExpenses.fldExpensesPK, tblExpenses.fldTaxDeductable, tblProject.fldProject, tblAllocation.fldAllocation
HAVING (((tblExpenses.fldDate)>="06/04" & "/" & [Enter Start Tax Year] And (tblExpenses.fldDate)<="05/04" & "/" & [Enter End Tax Year]) AND ((tblExpenses.fldTaxDeductable)=False));


I am unable to provide sample data due to the sensitivity of the info but hopefully the above will assist.

As mentioned each query provides a 'tax year' of data along with an annual total for each. I work out the net total manually.

alanrcooke is offline   Reply With Quote
Old 11-11-2019, 06:42 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Two queries into one (Income minus Expenses)

Is there some reason you can't join the two queries on client?

I would use a left join of income to expenses just in case there are are no expenses.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-12-2019, 08:00 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,480
Thanks: 58
Thanked 1,417 Times in 1,398 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Two queries into one (Income minus Expenses)

Hi. I'm with Pat on this. If you want to post a copy of your db, you can remove the "sensitive" data and replace them with "dummy" data.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Date minus 1 & Date minus 2 excluding weekends and holidays smartbuyer Modules & VBA 1 01-04-2017 01:13 AM
Excel file for income and expenses? YNWA Excel 1 01-10-2014 09:54 PM
income expenses query not showing all records Nicolette Queries 36 08-15-2010 03:34 PM
Employee Expenses table design RCurtin General 1 11-06-2006 07:54 AM
no MINUS in Access Queries? intern42 Queries 4 07-25-2003 12:22 PM




All times are GMT -8. The time now is 11:39 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