Problem with group result
I had a report that was grouped on two fields from two tables, by work location and by period (month & year). I get the desired result in the design view perfectly.
Now when I pass a parameter values from a SQL statement by providing the work location and the period I get a different result. I used two different statements to see the result but the result is undesirable. It also works perfect in the database.
Here's the result that I want in the design mode:
Here's the result I get when using this statement, and it also gives results of the 5th month result in page 2.
SELECT * FROM tblPerDiem pd INNER JOIN tblEmployee emp ON pd.employeeID=emp.employeeID
WHERE MONTH(pd.[monthIssued])='4' AND YEAR(pd.[monthIssued])='2015' AND emp.workLocation='Head Office'
And here's the result when using this statement. I used it from the query built when linking the desired tables in the database expert just to see the result.
But it is different from the first one, no duplication of records but it displays the other month's records.
SELECT tblEmployee.employeeID, tblEmployee.firstName, tblEmployee.middleName, tblPerDiem.perDiem, tblPerDiem.accomodation, tblPerDiem.numberofDays, tblPerDiem.advanceDeduction, tblEmployee.workLocation, tblPerDiem.monthIssued
FROM tblEmployee tblEmployee INNER JOIN tblPerDiem tblPerDiem ON tblEmployee.employeeID = tblPerDiem.employeeID WHERE MONTH(tblPerDiem.[monthIssued])='4' AND YEAR(tblPerDiem.[monthIssued])='2015' AND tblEmployee.workLocation='Head Office'
What seems to be the problem that I haven't noticed? Please I need some insights.