Solved

# Requtls from query displaying on multiple rows instead of one

Posted on 2012-04-12
289 Views
Hi,

I am trying to create a query to caculate and show a person's pay date, gross pay for that pay date, and year to date gross total.

I am able to run the query, enter a pay date, and display the results but the results apear on two rows instead of just one row.

EMPID
pay_date
Gross

empid     pay_date                   gross
11111     2012-01-21 00:00     2000.00
11111     2012-02-07 00:00     2000.00
11111     2012-02-22 00:00     2000.00
11111     2012-03-03 00:00     2500.00
22222     2012-01-21 00:00     1500.00
22222     2012-02-07 00:00     1500.00
22222     2012-02-22 00:00     1500.00
22222     2012-03-03 00:00     1000.00

SQL i am using:

SELECT tblEmployeePay.EMPID AS ID, tblEmployeePay.Gross AS [Gross Pay], Sum(tblEmployeePay.Gross) AS YTD
FROM tblEmployeePay
WHERE (((tblEmployeePay.Pay_Date)<=[Enter Pay Date]))
GROUP BY tblEmployeePay.ID, tblEmployeePay.Gross;

------------------------------------------------------------------------------------------------

The results I am currently getting:

Run Query by and Enter a Pay Date: 2012-03-03 00:00 when prompted

Display results after entering pay date and clicking on ok

ID         Gross Pay           YTD
11111   2500.00             2500.00
11111   2000.00             6000.00
22222   1000.00             1000.00
22222   1500.00             4500.00

------------------------------------------------------------------------------------------------

The results i am trying to get:

Run Query by and Enter a Pay Date: 2012-03-03 00:00 when prompted

Display results after entering pay date and clicking on ok

ID         Gross Pay          YTD
11111   2500.00            8500.00
22222   1000.00            5500.00
0
Question by:cesemj
• 3
• 2

LVL 33

Expert Comment

Try this.

SELECT tblEmployeePay.EMPID AS ID,  Sum(tblEmployeePay.Gross) AS YTD
FROM tblEmployeePay
WHERE (((tblEmployeePay.Pay_Date)<=[Enter Pay Date]))
GROUP BY empid
0

Author Comment

Hi and thanks,

the results give me senario 1 which is what i received first before I modified the query to try to get the results in senario 2.

Senario 1
ID         YTD
11111   8500.00
22222   5500.00

Senario 2
ID         Gross Pay         YTD
11111   2500.00            8500.00
22222   1000.00            5500.00
0

Author Comment

Hi,

I modified the query to the below statement and get the results  i am looking for but is the statement right or am I wsetting myself up for failur down the road because of using poor syntax?

SELECT tblEmployeePay.EMPID AS ID, Last(tblEmployeePay.Gross) AS [Gross Pay], Sum(tblEmployeePay.Gross) AS YTD
FROM tblEmployeePay
WHERE (((tblEmployeePay.Pay_Date)<=[Enter Pay Date]))
GROUP BY tblEmployeePay.ID;
0

LVL 33

Accepted Solution

Norie earned 300 total points

What gross value do you want to return?

The most recent?

If it is then the logic of your query is right as far as I can see.
0

Author Closing Comment

Thanks for you help.
0

## Featured Post

### Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.