Hi all,

Using v3.27 & I have 4 tables
- Customer
- Payroll
- Job
- Job Transaction

The Job Transaction table has fields
- JobID
- PayrollID
- Date

and the Job table has fileds
- JobID
- Job Name
- CustomerID

and the Customer table
- CustomerID
- Customer Name

and I want a query to give me the fields
Date, Payroll Name, Job Name, Customer Name

I am OK to get the Job Number but how is the best way to get the
Customer Name into the equation

Thanks,
Pat

Pat

something like

SELECT Date, PayrollName, JobName, CustomerName FROM Transactions
JOIN Jobs ON JobID = JobID
JOIN Customer ON Jobs.CustimerID = CustomerID
payroll?????

Roy Lambert

Roy,

>SELECT Date, PayrollName, JobName, CustomerName FROM Transactions
>JOIN Jobs ON JobID = JobID
>JOIN Customer ON Jobs.CustimerID = CustomerID
>payroll?????

SELECT JobTran.Date, Payroll.Surname, JobCosts.JobCostCode
FROM (JobTran LEFT JOIN JobCosts ON JobTran.JobID = JobCosts.JobID)
LEFT JOIN Payroll ON JobTran.PayrollID = Payroll.PayrollID;

Gives me the Date, Name, Job Number (including ALL rows from JobTran
table and only those records from the other 2 tables where the joined
fields are equal).

How can I include the Customer table into this expression? (so I get
Date, Payroll Name, Job Number, Customer Name)

Thanks,
Pat

Pat,

<< How can I include the Customer table into this expression? (so I get
Date, Payroll Name, Job Number, Customer Name) >>

SELECT JobTran.Date, Payroll.Surname, JobCosts.JobCostCode,
Customer."Customer Name"
FROM (JobTran LEFT JOIN JobCosts ON JobTran.JobID = JobCosts.JobID)
LEFT JOIN Payroll ON JobTran.PayrollID = Payroll.PayrollID
LEFT JOIN Customer ON JobTran.CustomerID = Customer.CustomerID;

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

That did the trick, thanks

><< How can I include the Customer table into this expression? (so I get
>Date, Payroll Name, Job Number, Customer Name) >>
>
>SELECT JobTran.Date, Payroll.Surname, JobCosts.JobCostCode,
>Customer."Customer Name"
>FROM (JobTran LEFT JOIN JobCosts ON JobTran.JobID = JobCosts.JobID)
>LEFT JOIN Payroll ON JobTran.PayrollID = Payroll.PayrollID
>LEFT JOIN Customer ON JobTran.CustomerID = Customer.CustomerID;

I now need a variation on this so (instead of Payroll.Surname) it
gives me Payroll.Surname + Payroll.FirstName   (i.e. Jones, Tom)

I tried
Payroll."Surname" + ", " + Payroll."FirstName"
Payroll.Surname + ', ' + Payroll.FirstName
but no luck

Thanks,
Pat

Pat


Try

(Payroll.Surname + ', ' + Payroll.FirstName) AS FullName

Roy Lambert

>(Payroll.Surname + ', ' + Payroll.FirstName) AS FullName

That worked, thanks Roy

DBISAM v4

I have another variation to this that is not working

I have 3 fields Customer.City, Customer.State, Customer .zip and I am
using SQL to get a result of

Sydney, NSW, 2000

I have tried
SQL.Add('(Customer.City + Customer.State) AS LastAddressLine '); and
it works BUT I get    SydneyNSW

I have tried many combinations of

SQL.Add('(Customer.City ' + '''', '''' + ' Customer.State) AS
LastAddressLine ');

but I am always getting errors
What is the correct syntax for this ?

Thank you very much,
Pat

Pat


What you're doing is right, but with the wrong number of quotes. I find in easier to use QuotedStr eg

SQL.Add('Customer.City' + QuotedStr(', ') +' Customer.State AS LastAddressLine ');

Saves a lot of trying to count the little blighters AND figure out how many there should be

Roy Lambert