Query Sorting

[jmm1954]

I have 3 tables
tbl 1 is rates
tbl 2 is truck info by employee (an emplpyee # and assigned truck number)
tbl 3 is daily detail for loads hauled (assigned truck number and actual
truck driven for that load)

I am trying to get to:

employee assigned truck
list of daily detail listing actual truck driven

If emp# 2 drives #3 it lists under emp# 3 instead of under emp# 2 it only
lists where emp# and actual# are the same in both tables

 

[Barry A&P]

I am a newbie so take this with a grain of salt

i dont quite get the detail in your post so i will guess a little at what
youre after

your tables should be something like

I am assuming the rate table is for specific jobs

Tbl_Rates
RateID PK
RateTitle
Rate

Tbl_Employees
EmployeeID PK
EmployeeName

TBL_Trucks
TruckID (pk) (liscence # or something as long as it wont change
TruckDescription (red ford dump)

Tbl_jobs
JobID (pk)
JobDate
JobTime
EmployeeID (key from Tbl_Employees)
TruckID (key from TBL_Trucks)
RateID (key from Tbl_rates)

a query like this should get what your after

SELECT tbl_employees.employeename, Tbl_jobs.JobDate, Tbl_jobs.JobTime,
Tbl_jobs.JobDescription, Tbl_trucks.TruckID, Tbl_rates.rate
FROM tbl_employees RIGHT JOIN (Tbl_rates RIGHT JOIN (Tbl_jobs LEFT JOIN
Tbl_trucks ON Tbl_jobs.TruckID = Tbl_trucks.TruckID) ON Tbl_rates.RateID =
Tbl_jobs.RateID) ON tbl_employees.employeeid = Tbl_jobs.emlpoyeeID
ORDER BY tbl_employees.employeename, Tbl_jobs.JobDate, Tbl_jobs.JobTime;

at least its somewhere to start

Barry

 

[KARL DEWEY]

I would use a single field for JobDate and JobTime.

 

[jmm1954]

report needs to be by truck assignd, a driver could drive assigned truck and
alternate truck(s) in the same day. Report needs to be by driver and under
that listing the date ticket# and truck number driven

 

[KARL DEWEY]

So, did you try Barry's suggestion?

 

[jmm1954]

Actually I had the correct stuff I needed I just had to change joining. His
joining information hekped me to get that correct. The thing that I came back
to is in the loader daily file there had to be both the assigned truck and
loaded truxk, which I already had done it was just the way I had it joined.
Thanks for the suggestion