Need a query to join 5 related tables, accurately.

Punice

Registered User.
Local time
Today, 05:42
Joined
May 10, 2010
Messages
135
Researched & tried numerous approaches to solve the problem that I'm giving the experts to do for me, once again. I have a tbl_Job_Basics that I and need a query that merges it with data from these the [named fields] in these four tables; 'tblRevenue for the "Revenue" amount', 'tblMaterial for [Matl_Cost], [Sales_Tax], ['Sales_Tax]' & '[Del_Fee'], tbl'Labor' for[ Labor_Cost] & 'tblDump' for [Fee].

Here is my sql for incorporarting [Revenue] earned for various jobs, identified by 'J_ID'.
It produced the correct results, but when I join tblMatrial, tblLabor & tblDump to it, the data sheet contains duplicate data in fields that should be empty (ie, not all tables contain an equal number of records; like displaying two 'Job_Price' values from the tblJobs, when it should only be shown in the first row for a given J_ID.

q_Job_Bucks Revenue
SELECT q_Jobs_Basics.J_ID, q_Jobs_Basics.L_Name, q_Jobs_Basics.Percent_OH, q_Jobs_Basics.Percent_GnA, q_Jobs_Basics.Job_Price, q_Jobs_Basics.Status, tblRevenue.Biz_Date, tblRevenue.Revenue
FROM q_Jobs_Basics
RIGHT JOIN tblRevenue
ON q_Jobs_Basics.[J_ID] = tblRevenue.[J_ID];
 
You've been playing around with the joins, so keep playing around with it until the RIGHT JOIN becomes an INNER JOIN.
 
It looks like you might benefit from a table called tblExpense, which has a field for ExpenseType, and into which you would record all expenses. Failing that, you can UNION your expenses together in a query, and then use the results of that query to do what you want to do here.
 
Re: "Expense Table" approach. I will resort to modifying the form with the sub-forms that I use to enter 'Revenue', 'Matl_Cost, Sales Tax & Del_Fee', 'Labor' and the disposal 'Fee' plus the date & other data that I enter in them, as you indirectly suggested. However, I'll wait to see if a solution is offered, here, before I do that. Thanks, though. :)
 
I'll wait to see if a solution is offered, here
You can UNION your existing expenses together so they appear to come from a single table. Consider the following SQL . . .
SELECT LabourID As ID, "Labour" As SrcTable, JobID, [Date], Description, Amount
FROM tblLabour
UNION ALL
SELECT MaterialID, "Material", JobID, [Date], Description, Amount
FROM tblMaterial
UNION ALL
SELECT DumpID, "Dump", JobID, [Date], Description, Amount
FROM tblDump
. . . which makes similarly structured data from different tables appear in a single query, which will solve the issue you have described.
 
Markk, I followed your example and was able to create the query that I needed. I'll format the report to display the info as I wanted. Thanks for
your caring to help this old geezer.:)

SELECT tblMaterial.J_ID, tblMaterial.M_ID, tblJobs.L_Name, tblMaterial.E_Date, CCur(Nz(Round([tblMaterial].[Matl_Cost]+[tblMaterial].[Sales_Tax]+[tblMaterial].[Del_Fee],2),0)) AS Matl_Exp
FROM tblJobs LEFT JOIN tblMaterial ON tblJobs.J_ID = tblMaterial.J_ID
UNION ALL
SELECT tblLabor.L_ID, tblLabor.J_ID, tblJobs.L_Name, tblLabor.E_Date, tblLabor.Labor_Cost
FROM tblJobs RIGHT JOIN tblLabor ON tblJobs.J_ID = tblLabor.J_ID
UNION ALL
SELECT tblDump.D_ID, tblDump.J_ID, tblJobs.L_Name, tblDump.E_Date, tblDump.Fee
FROM tblJobs RIGHT JOIN tblDump ON tblJobs.J_ID = tblDump.J_ID
UNION ALL
SELECT tblRevenue.R_ID, tblRevenue.J_ID, tblJobs.L_Name, tblRevenue.E_Date, tblRevenue.Revenue
FROM tblJobs RIGHT JOIN tblRevenue ON tblJobs.J_ID = tblRevenue.J_ID;
 
Nicely done, but you can start to see too, how if all your financials were in one table, how much easier it would be. And you can still have fields that aren't used for some financials, and have multiple subforms that look different and do different jobs, but that still draw data from a single table.

Nice job though, cheers,
 
I'm going to do what you suggested, just to gain additional experience 'thinking & doing'. This 80 year old brain needs all the exercise that it
can get. Thanks again for you much appreciated help...Punice:D
 
Wow, good for you. Let me know if you have other questions or whatever,
Cheers,
 

Users who are viewing this thread

Back
Top Bottom