?
Solved

Ref-Ontana request for code(Unable to get Ontanas EMAIL)

Posted on 2001-07-30
8
Medium Priority
?
198 Views
Last Modified: 2010-05-02
SELECT Sal_Mas.Emp_No, Sal_Mas.Emp_SurName, transfile.tran_empno, transfile.SalaryAdv, Sal_Mas.Emp_Basicsal, Sal_Mas.Allow1, Sal_Mas.Allow2, Sal_Mas.Allow3, Sal_Mas.Allow4, Sal_Mas.Allow5, Sal_Mas.Allow6, Sal_Mas.Allow7, Sal_Mas.Allow8, Sal_Mas.InterimAllow, transfile.Festadv, transfile.Lunch, transfile.StaffPfContr, transfile.Loan, transfile.CollegePFLoan, transfile.Paye, transfile.SundryDed,


FROM Sal_Mas, transfile WHERE
 Sal_Mas.Emp_No = transfile.tran_empno

UNABLE TO GET ALL TRANSACTIONS FROM MASTERFILE PLS HELP ME WITH THE CODE

MASTER FILE IS sal_mas
0
Comment
Question by:josephrrc
8 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6336466
You need a LEFT JOIN:

SELECT Sal_Mas.Emp_No, Sal_Mas.Emp_SurName, transfile.tran_empno, transfile.SalaryAdv, Sal_Mas.Emp_Basicsal,
Sal_Mas.Allow1, Sal_Mas.Allow2, Sal_Mas.Allow3, Sal_Mas.Allow4, Sal_Mas.Allow5, Sal_Mas.Allow6, Sal_Mas.Allow7,
Sal_Mas.Allow8, Sal_Mas.InterimAllow, transfile.Festadv, transfile.Lunch, transfile.StaffPfContr, transfile.Loan,
transfile.CollegePFLoan, transfile.Paye, transfile.SundryDed


FROM Sal_Mas
LEFT OUTER JOIN transfile
ON Sal_Mas.Emp_No = transfile.tran_empno
0
 
LVL 1

Accepted Solution

by:
alaplume earned 200 total points
ID: 6336501
The extra comma after transfile.SundryDed would be one problem.
0
 
LVL 1

Expert Comment

by:loveneesh_bansal
ID: 6336651
Hi the answer is so simple you have done every thing right but if you go once agin from your query you will find that in the select statement (before the where clause) you didnot mention the "transfile.tran_empno" field.

The right query shoul be like


SELECT Sal_Mas.Emp_No, Sal_Mas.Emp_SurName, transfile.tran_empno, transfile.SalaryAdv, Sal_Mas.Emp_Basicsal,
Sal_Mas.Allow1, Sal_Mas.Allow2, Sal_Mas.Allow3, Sal_Mas.Allow4, Sal_Mas.Allow5, Sal_Mas.Allow6, Sal_Mas.Allow7,
Sal_Mas.Allow8, Sal_Mas.InterimAllow,transfile.tran_empno, transfile.Festadv, transfile.Lunch, transfile.StaffPfContr, transfile.Loan,
transfile.CollegePFLoan, transfile.Paye, transfile.SundryDed,


FROM Sal_Mas, transfile WHERE
Sal_Mas.Emp_No = transfile.tran_empno


i know now the program will definately work.


Bye
Loveneesh

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:Otana
ID: 6336807
actually, the transfile.tran_empno field WAS already in the query.

Not that it matters if it wasn't, because it's perfectly possible to put a field in the WHERE clause which you have not selected.

But on the topic: Emoreau is right. With a left join you get all records in the first table, linked to their corresponding fields in the other table or a NULL-record where there are no corresponding records.

Hope it helped, :)
0
 

Author Comment

by:josephrrc
ID: 6342982
Thanks to all u nice guys my payroll prg is now working fine.Thanks alot may be any more problems I shall come back again to all u computer wizards
Cheers,
Josephrrc
0
 

Author Comment

by:josephrrc
ID: 6342983
Thanks to all u nice guys my payroll prg is now working fine.Thanks alot may be any more problems I shall come back again to all u computer wizards
Cheers,
Josephrrc
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6343010
You need to accept one of the comment now!
0
 

Author Comment

by:josephrrc
ID: 6343121
Now the prg is working thanks lot
cheers
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question