Getting wrong information in query

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

Hi,

This will be brief. I have a query that sums total revenue
against a project by month. I have another query that sums
the expenses for a particular project by month (the end
result is to determine the net revenue for a project). The
problem is that when I run the query, the expense goes
against all the months. For example: March expenses will
show up with January, February and March revenue. What am
I doing wrong?

Thanks,
Karen
 
Sure. Here it is. Thanks for your help!

SELECT [tbl Projects].ProjectID, [tbl
Projects].ProjectNameAndCode AS Expr1, [tbl
Projects].ProjectType, [tbl Time Sheet Hours].DateWorked,
[tbl Time Sheet Hours].BillableDays, [tbl Time Sheet
Hours].BillingRateID, [BillableDays]*[BillingRateID] AS
[Total Delivery]
FROM [tbl Projects] INNER JOIN ([tbl Time Sheets] INNER
JOIN [tbl Time Sheet Hours] ON [tbl Time
Sheets].TimeSheetID = [tbl Time Sheet Hours].TimeSheetID)
ON [tbl Projects].ProjectID = [tbl Time Sheet
Hours].ProjectID;


SELECT DISTINCTROW [tbl Project Expense].ProjectExpenseID,
[tbl Project Expense].ProjectID, [tbl Project
Expense].Payee, Format$([tbl Project Expense].[Date],'mmmm
yyyy') AS [Date By Month], Sum([tbl Project
Expense].TotalPayment) AS [Sum Of TotalPayment]
FROM [tbl Project Expense]
GROUP BY [tbl Project Expense].ProjectExpenseID, [tbl
Project Expense].ProjectID, [tbl Project Expense].Payee,
Format$([tbl Project Expense].[Date],'mmmm yyyy'), Year
([tbl Project Expense].[Date])*12+DatePart('m',[tbl
Project Expense].[Date])-1;
 
I presume that there is another piece of SQL that joins
these two together. If so, could you post that as well.

Gerald Stanley MCSD
-----Original Message-----
Sure. Here it is. Thanks for your help!

SELECT [tbl Projects].ProjectID, [tbl
Projects].ProjectNameAndCode AS Expr1, [tbl
Projects].ProjectType, [tbl Time Sheet Hours].DateWorked,
[tbl Time Sheet Hours].BillableDays, [tbl Time Sheet
Hours].BillingRateID, [BillableDays]*[BillingRateID] AS
[Total Delivery]
FROM [tbl Projects] INNER JOIN ([tbl Time Sheets] INNER
JOIN [tbl Time Sheet Hours] ON [tbl Time
Sheets].TimeSheetID = [tbl Time Sheet Hours].TimeSheetID)
ON [tbl Projects].ProjectID = [tbl Time Sheet
Hours].ProjectID;


SELECT DISTINCTROW [tbl Project Expense].ProjectExpenseID,
[tbl Project Expense].ProjectID, [tbl Project
Expense].Payee, Format$([tbl Project Expense].[Date],'mmmm
yyyy') AS [Date By Month], Sum([tbl Project
Expense].TotalPayment) AS [Sum Of TotalPayment]
FROM [tbl Project Expense]
GROUP BY [tbl Project Expense].ProjectExpenseID, [tbl
Project Expense].ProjectID, [tbl Project Expense].Payee,
Format$([tbl Project Expense].[Date],'mmmm yyyy'), Year
([tbl Project Expense].[Date])*12+DatePart('m',[tbl
Project Expense].[Date])-1;
-----Original Message-----
Can you post the SQL for both queries to this thread.

Gerald Stanley MCSD
.
Click to expand...
.
Click to expand...
 
I posted the incorrect query the first time (the first of the two below). Sorry for being a dummy

SELECT DISTINCTROW [tbl Projects Query].ProjectID, [tbl Projects Query].Expr1, [tbl Projects Query].[DateWorked By Month], [tbl Projects Query].[Sum Of Total Delivery], First([qry ExpenseByMonth].Payee) AS [First Of Payee], First([qry ExpenseByMonth].[Date By Month]) AS [First Of Date By Month], Sum([qry ExpenseByMonth].[Sum Of TotalPayment]) AS [Sum Of Sum Of TotalPayment
FROM [tbl Projects Query] LEFT JOIN [qry ExpenseByMonth] ON [tbl Projects Query].ProjectID = [qry ExpenseByMonth].ProjectI
GROUP BY [tbl Projects Query].ProjectID, [tbl Projects Query].Expr1, [tbl Projects Query].[DateWorked By Month], [tbl Projects Query].[Sum Of Total Delivery]


----- Gerald Stanley wrote: ----

I presume that there is another piece of SQL that join
these two together. If so, could you post that as well

Gerald Stanley MCS
-----Original Message----
Sure. Here it is. Thanks for your help!
SELECT [tbl Projects].ProjectID, [tbl
Click to expand...
Projects].ProjectNameAndCode AS Expr1, [tbl
Projects].ProjectType, [tbl Time Sheet Hours].DateWorked,
[tbl Time Sheet Hours].BillableDays, [tbl Time Sheet
Hours].BillingRateID, [BillableDays]*[BillingRateID] AS
[Total Delivery
FROM [tbl Projects] INNER JOIN ([tbl Time Sheets] INNER
JOIN [tbl Time Sheet Hours] ON [tbl Time
Sheets].TimeSheetID = [tbl Time Sheet Hours].TimeSheetID)
ON [tbl Projects].ProjectID = [tbl Time Sheet
Hours].ProjectID
SELECT DISTINCTROW [tbl Project Expense].ProjectExpenseID,
Click to expand...
Click to expand...
[tbl Project Expense].ProjectID, [tbl Project
Expense].Payee, Format$([tbl Project Expense].[Date],'mmmm
yyyy') AS [Date By Month], Sum([tbl Project
Expense].TotalPayment) AS [Sum Of TotalPayment
FROM [tbl Project Expense
GROUP BY [tbl Project Expense].ProjectExpenseID, [tbl
Project Expense].ProjectID, [tbl Project Expense].Payee,
Format$([tbl Project Expense].[Date],'mmmm yyyy'), Yea
([tbl Project Expense].[Date])*12+DatePart('m',[tbl
Project Expense].[Date])-1
Can you post the SQL for both queries to this thread
Click to expand...
Click to expand...
 
I believe that the problem lies in the join statement
between the table and the query. At present, the join is
only defined by the projectid which is why you are getting
the total of the project expenses shown against each month
worked. The join definition needs to take into account the
month worked and expenses claimed. If the format of [tbl
Projects Query].[DateWorked By Month] and [qry
ExpenseByMonth].[Date By Month] are the same then the join
is likely to be

LEFT JOIN [qry ExpenseByMonth] ON [tbl Projects
Query].ProjectID = [qry ExpenseByMonth].ProjectID AND [tbl
Projects Query].[DateWorked By Month] = [qry
ExpenseByMonth].[Date By Month]

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I posted the incorrect query the first time (the first of
Click to expand...
the two below). Sorry for being a dummy!
SELECT DISTINCTROW [tbl Projects Query].ProjectID, [tbl
Click to expand...
Projects Query].Expr1, [tbl Projects Query].[DateWorked By
Month], [tbl Projects Query].[Sum Of Total Delivery],
First([qry ExpenseByMonth].Payee) AS [First Of Payee],
First([qry ExpenseByMonth].[Date By Month]) AS [First Of
Date By Month], Sum([qry ExpenseByMonth].[Sum Of
TotalPayment]) AS [Sum Of Sum Of TotalPayment]
FROM [tbl Projects Query] LEFT JOIN [qry ExpenseByMonth]
Click to expand...
ON [tbl Projects Query].ProjectID = [qry
ExpenseByMonth].ProjectID
GROUP BY [tbl Projects Query].ProjectID, [tbl Projects
Click to expand...
Query].Expr1, [tbl Projects Query].[DateWorked By Month],
[tbl Projects Query].[Sum Of Total Delivery];
----- Gerald Stanley wrote: -----

I presume that there is another piece of SQL that joins
these two together. If so, could you post that as well.

Gerald Stanley MCSD
-----Original Message-----
Sure. Here it is. Thanks for your help!
SELECT [tbl Projects].ProjectID, [tbl
Click to expand...
Projects].ProjectNameAndCode AS Expr1, [tbl
Projects].ProjectType, [tbl Time Sheet Hours].DateWorked,
[tbl Time Sheet Hours].BillableDays, [tbl Time Sheet
Hours].BillingRateID, [BillableDays]*[BillingRateID] AS
[Total Delivery]
FROM [tbl Projects] INNER JOIN ([tbl Time Sheets] INNER
JOIN [tbl Time Sheet Hours] ON [tbl Time
Sheets].TimeSheetID = [tbl Time Sheet Hours].TimeSheetID)
ON [tbl Projects].ProjectID = [tbl Time Sheet
Hours].ProjectID;
SELECT DISTINCTROW [tbl Project
Click to expand...
Expense].ProjectExpenseID,
[tbl Project Expense].ProjectID, [tbl Project
Expense].Payee, Format$([tbl Project Expense].[Date],'mmmm
yyyy') AS [Date By Month], Sum([tbl Project
Expense].TotalPayment) AS [Sum Of TotalPayment]
FROM [tbl Project Expense]
GROUP BY [tbl Project Expense].ProjectExpenseID, [tbl
Project Expense].ProjectID, [tbl Project Expense].Payee,
Format$([tbl Project Expense].[Date],'mmmm yyyy'), Year
([tbl Project Expense].[Date])*12+DatePart('m',[tbl
Project Expense].[Date])-1;
-----Original Message-----
Can you post the SQL for both queries to this thread.
Gerald Stanley MCSD
-----Original Message-----
Hi,
This will be brief. I have a query that sums total revenue
against a project by month. I have another query that sums
the expenses for a particular project by month (the end
result is to determine the net revenue for a
Click to expand...
project).
The
problem is that when I run the query, the expense goes
against all the months. For example: March expenses will
show up with January, February and March revenue.
Click to expand...
What
am
I doing wrong?
Thanks,
Karen
.
.
.
Click to expand...
Click to expand...
.
Click to expand...
 
It worked Gerald! I didn't fully understand your response so I started reading up a bit so I clearly understood you and it worked!! Thanks SO much

Karen
 
Back
Top