instead of two rows, one row is to be displayed

K

Krishnakanth

I am executing a query the following query.

SELECT
Salary_Details.Name,
Salary_Details.SSN,
Salary_Details.Employee_code,
Monthly_Details.OT_Hours,
Monthly_Details.OT_Payment,
Monthly_Details.Other_Amount,
Loan_Details.Loan_Number,
Loan_Details.Loan_Amount,
Loan_Details.Sal_Perdiem_Ind,
Loan_Repayment.Repay_Amount

FROM Salary_Details, Monthly_Details, Loan_Details, Loan_Repayment

WHERE
Salary_Details.Name = Monthly_Details.Name
and Salary_Details.SSN = Monthly_Details.SSN
and Monthly_Details.Name = Loan_Details.Name
and Monthly_Details.SSN = Loan_Details.SSN
and Loan_Repayment.Name = Loan_Details.Name
and Loan_Repayment.Loan_Number = Loan_Details.Loan_Number
and Loan_Repayment.Repay_Year = Monthly_Details.Year
and Loan_Repayment.Repay_Month = Monthly_Details.Month
and
(Salary_Details.Name)=[Forms].[Form1].[Combo6]
AND (Salary_Details.Employee_code)=[Forms].[Form1].[Combo18]
AND (Monthly_Details.Year)=[Forms].[Form1].[Combo8]
AND (Monthly_Details.Month)=[Forms].[Form1].[Combo10]
AND
((Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year));

and I am getting the output as follows.
===============================
name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
kevin 111223333 AJ00200 5 20 100

sal_perdiem_ind repay_amount
S 50
P 60

Two rows are displayed by executing this query. what I want is one row is to
be displayed as follows

name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
repay_amount_S repay_amount_P
50 60

I dont want to display sal_perdiem_ind.
The thing is If the sal_perdiem_ind = "P" then 50 should be moved to
repay_amount_P and if the sal_perdiem_ind = "S" then 60 should be moved to
repay_amount_S.

I am using ms access. Tables, queries, forms and reports everything is in ms
access 2000.

can it be done thru sql query ?

If so please help me to fix this problem

Krishnakanth VM
Software Engineer
 
A

AlanP

Whoops, it should have read "Distinct"

AlanP said:
Try adding the word distrinct after select

Krishnakanth said:
I am executing a query the following query.

SELECT
Salary_Details.Name,
Salary_Details.SSN,
Salary_Details.Employee_code,
Monthly_Details.OT_Hours,
Monthly_Details.OT_Payment,
Monthly_Details.Other_Amount,
Loan_Details.Loan_Number,
Loan_Details.Loan_Amount,
Loan_Details.Sal_Perdiem_Ind,
Loan_Repayment.Repay_Amount

FROM Salary_Details, Monthly_Details, Loan_Details, Loan_Repayment

WHERE
Salary_Details.Name = Monthly_Details.Name
and Salary_Details.SSN = Monthly_Details.SSN
and Monthly_Details.Name = Loan_Details.Name
and Monthly_Details.SSN = Loan_Details.SSN
and Loan_Repayment.Name = Loan_Details.Name
and Loan_Repayment.Loan_Number = Loan_Details.Loan_Number
and Loan_Repayment.Repay_Year = Monthly_Details.Year
and Loan_Repayment.Repay_Month = Monthly_Details.Month
and
(Salary_Details.Name)=[Forms].[Form1].[Combo6]
AND (Salary_Details.Employee_code)=[Forms].[Form1].[Combo18]
AND (Monthly_Details.Year)=[Forms].[Form1].[Combo8]
AND (Monthly_Details.Month)=[Forms].[Form1].[Combo10]
AND
((Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year));

and I am getting the output as follows.
===============================
name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
kevin 111223333 AJ00200 5 20 100

sal_perdiem_ind repay_amount
S 50
P 60

Two rows are displayed by executing this query. what I want is one row is
to
be displayed as follows

name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
repay_amount_S repay_amount_P
50 60

I dont want to display sal_perdiem_ind.
The thing is If the sal_perdiem_ind = "P" then 50 should be moved to
repay_amount_P and if the sal_perdiem_ind = "S" then 60 should be moved
to
repay_amount_S.

I am using ms access. Tables, queries, forms and reports everything is in
ms
access 2000.

can it be done thru sql query ?

If so please help me to fix this problem

Krishnakanth VM
Software Engineer
Click to expand...
Click to expand...
 
K

Krishnakanth

No these two rows are two different rows.

1st row sal_perdiem_ind = S and repay_amount = 50
2nd row sal_perdiem_ind = P and repay_amount = 60

Krishnakanth VM

AlanP said:
Try adding the word distrinct after select

Krishnakanth said:
I am executing a query the following query.

SELECT
Salary_Details.Name,
Salary_Details.SSN,
Salary_Details.Employee_code,
Monthly_Details.OT_Hours,
Monthly_Details.OT_Payment,
Monthly_Details.Other_Amount,
Loan_Details.Loan_Number,
Loan_Details.Loan_Amount,
Loan_Details.Sal_Perdiem_Ind,
Loan_Repayment.Repay_Amount

FROM Salary_Details, Monthly_Details, Loan_Details, Loan_Repayment

WHERE
Salary_Details.Name = Monthly_Details.Name
and Salary_Details.SSN = Monthly_Details.SSN
and Monthly_Details.Name = Loan_Details.Name
and Monthly_Details.SSN = Loan_Details.SSN
and Loan_Repayment.Name = Loan_Details.Name
and Loan_Repayment.Loan_Number = Loan_Details.Loan_Number
and Loan_Repayment.Repay_Year = Monthly_Details.Year
and Loan_Repayment.Repay_Month = Monthly_Details.Month
and
(Salary_Details.Name)=[Forms].[Form1].[Combo6]
AND (Salary_Details.Employee_code)=[Forms].[Form1].[Combo18]
AND (Monthly_Details.Year)=[Forms].[Form1].[Combo8]
AND (Monthly_Details.Month)=[Forms].[Form1].[Combo10]
AND
((Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year));

and I am getting the output as follows.
===============================
name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
kevin 111223333 AJ00200 5 20 100

sal_perdiem_ind repay_amount
S 50
P 60

Two rows are displayed by executing this query. what I want is one row is
to
be displayed as follows

name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
repay_amount_S repay_amount_P
50 60

I dont want to display sal_perdiem_ind.
The thing is If the sal_perdiem_ind = "P" then 50 should be moved to
repay_amount_P and if the sal_perdiem_ind = "S" then 60 should be moved to
repay_amount_S.

I am using ms access. Tables, queries, forms and reports everything is in
ms
access 2000.

can it be done thru sql query ?

If so please help me to fix this problem

Krishnakanth VM
Software Engineer
Click to expand...
Click to expand...
 
J

Jerry Crider

Krishnakanth said:
No these two rows are two different rows.

1st row sal_perdiem_ind = S and repay_amount = 50
2nd row sal_perdiem_ind = P and repay_amount = 60

Krishnakanth VM

AlanP said:
Try adding the word distrinct after select

Krishnakanth said:
I am executing a query the following query.

SELECT
Salary_Details.Name,
Salary_Details.SSN,
Salary_Details.Employee_code,
Monthly_Details.OT_Hours,
Monthly_Details.OT_Payment,
Monthly_Details.Other_Amount,
Loan_Details.Loan_Number,
Loan_Details.Loan_Amount,
Loan_Details.Sal_Perdiem_Ind,
Loan_Repayment.Repay_Amount

FROM Salary_Details, Monthly_Details, Loan_Details, Loan_Repayment

WHERE
Salary_Details.Name = Monthly_Details.Name
and Salary_Details.SSN = Monthly_Details.SSN
and Monthly_Details.Name = Loan_Details.Name
and Monthly_Details.SSN = Loan_Details.SSN
and Loan_Repayment.Name = Loan_Details.Name
and Loan_Repayment.Loan_Number = Loan_Details.Loan_Number
and Loan_Repayment.Repay_Year = Monthly_Details.Year
and Loan_Repayment.Repay_Month = Monthly_Details.Month
and
(Salary_Details.Name)=[Forms].[Form1].[Combo6]
AND (Salary_Details.Employee_code)=[Forms].[Form1].[Combo18]
AND (Monthly_Details.Year)=[Forms].[Form1].[Combo8]
AND (Monthly_Details.Month)=[Forms].[Form1].[Combo10]
AND
((Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year));

and I am getting the output as follows.
===============================
name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
kevin 111223333 AJ00200 5 20 100

sal_perdiem_ind repay_amount
S 50
P 60

Two rows are displayed by executing this query. what I want is one row
is
to
be displayed as follows

name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
repay_amount_S repay_amount_P
50 60

I dont want to display sal_perdiem_ind.
The thing is If the sal_perdiem_ind = "P" then 50 should be moved to
repay_amount_P and if the sal_perdiem_ind = "S" then 60 should be moved
to
repay_amount_S.

I am using ms access. Tables, queries, forms and reports everything is
in
ms
access 2000.

can it be done thru sql query ?

If so please help me to fix this problem

Krishnakanth VM
Software Engineer
Click to expand...
Click to expand...
Click to expand...
 
K

Krishnakanth

Jerry

I donot find your comments.

Krishnakanth VM
Software Engineer

Jerry Crider said:
Krishnakanth said:
No these two rows are two different rows.

1st row sal_perdiem_ind = S and repay_amount = 50
2nd row sal_perdiem_ind = P and repay_amount = 60

Krishnakanth VM

AlanP said:
Try adding the word distrinct after select

I am executing a query the following query.

SELECT
Salary_Details.Name,
Salary_Details.SSN,
Salary_Details.Employee_code,
Monthly_Details.OT_Hours,
Monthly_Details.OT_Payment,
Monthly_Details.Other_Amount,
Loan_Details.Loan_Number,
Loan_Details.Loan_Amount,
Loan_Details.Sal_Perdiem_Ind,
Loan_Repayment.Repay_Amount

FROM Salary_Details, Monthly_Details, Loan_Details, Loan_Repayment

WHERE
Salary_Details.Name = Monthly_Details.Name
and Salary_Details.SSN = Monthly_Details.SSN
and Monthly_Details.Name = Loan_Details.Name
and Monthly_Details.SSN = Loan_Details.SSN
and Loan_Repayment.Name = Loan_Details.Name
and Loan_Repayment.Loan_Number = Loan_Details.Loan_Number
and Loan_Repayment.Repay_Year = Monthly_Details.Year
and Loan_Repayment.Repay_Month = Monthly_Details.Month
and
(Salary_Details.Name)=[Forms].[Form1].[Combo6]
AND (Salary_Details.Employee_code)=[Forms].[Form1].[Combo18]
AND (Monthly_Details.Year)=[Forms].[Form1].[Combo8]
AND (Monthly_Details.Month)=[Forms].[Form1].[Combo10]
AND
((Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)=Monthly_Details.Year and
Month(Loan_Details.Eff_dt)<Monthly_Details.Month and
Year(Loan_Details.Exp_dt)>Monthly_Details.Year)
or
(Year(Loan_Details.Eff_dt)<Monthly_Details.Year and
Month(Loan_Details.Exp_dt)>Monthly_Details.Month and
Year(Loan_Details.Exp_dt)=Monthly_Details.Year));

and I am getting the output as follows.
===============================
name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
kevin 111223333 AJ00200 5 20 100

sal_perdiem_ind repay_amount
S 50
P 60

Two rows are displayed by executing this query. what I want is one row
is
to
be displayed as follows

name ssn employee_code ot_hours ot_payment other_amount
kevin 111223333 AJ00200 5 20 100
repay_amount_S repay_amount_P
50 60

I dont want to display sal_perdiem_ind.
The thing is If the sal_perdiem_ind = "P" then 50 should be moved to
repay_amount_P and if the sal_perdiem_ind = "S" then 60 should be moved
to
repay_amount_S.

I am using ms access. Tables, queries, forms and reports everything is
in
ms
access 2000.

can it be done thru sql query ?

If so please help me to fix this problem

Krishnakanth VM
Software Engineer
Click to expand...
Click to expand...
Click to expand...
 
Top