Getting wrong information in query

[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

 

[Gerald Stanley]

Can you post the SQL for both queries to this thread.

Gerald Stanley MCSD

 

[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;

 

[Gerald Stanley]

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
.

.

 

[Guest]

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

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'), Yea
([tbl Project Expense].[Date])*12+DatePart('m',[tbl
Project Expense].[Date])-1

Can you post the SQL for both queries to this thread

 

[Gerald Stanley]

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

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].ProjectID

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 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
-----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

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
.
.
.

.

 

[Guest]

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