Skip to Content
avatar image
Former Member

Problem with group result

Hello,

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.

Thank you

1.png (28.6 kB)
2.png (28.4 kB)
3.png (21.5 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • May 22, 2015 at 08:37 AM

    Hi Nebil,

    If there are multiple rows in the tblPerDiem for each Employee (for the given time period and location), then you will see multiple rows being returned.

    You'll need to take a look at the tblPerDiem table to extract the right rows.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded