simflex

Hello everyone, I have the following situation:


tblAccidentEvent table relates to theEmp by empID
tblAccidentEvent table relates to tblAccidentInfo by trackingNumber
tblAccidentEvent table relates to ImagesFiles by trackingNumber
tblAccidentEvent table relates to countyDriver by accidentEventID
tblAccidentEvent table relates to tblVehicle by accidentEventID
tblAccidentEvent table relates to tblCollision by CollisionID
tblAccidentEvent table relates to tblDamage by damageID
tblAccidentEvent table relates to tblfactors by factorsID
tblAccidentEvent table relates to tblLocation by locationID
tblAccidentEvent table relates to tblweather by weatherID

The structure is like this:
tblAccidentEvent is the main table with
accidentEventID as the primary key identity not null
Empid (from theEmp table)
collisionID (from tblCollision table)
trackingNumber (from tblaccidentInfo table)
weatherID (from tblWeather table)

ImageFiles has:
ID pk not null,
trackingNumber(from tblAccidentInfo table)

tblVehicle has:
vehicleId pk identity not null
accidentEventID (from tblAccidentEvent table)
damageID (from tbldamage table)

countyDriver has:
driverID pk identity not null
accidentEventID (from tblaccidentEvent table)
driverCondition (from tblDriverCondition table)
contributingFactors (from tblfactors table)

The following are lookup tables:
tblDriverCondition,
tblLocation,
tblWeather,
tblDamage,
tblAccidentInfo,
tblFactors

I have the following query:

SELECT * FROM tblaccidentevent,
theEmp,
tblAccidentInfo,
tblDriverCondition,
Countydriver,
tblvehicle,
tblcollision,
tbldamage,
tblfactors,
tbllocation,
tblweather,
ImageFiles
where tblAccidentInfo.trackingNumber = tblaccidentEvent.trackingNumber
and tblAccidentInfo.trackingNumber = ImageFiles.trackingNumber
and tblDriverCondition.ConditionID = CountyDriver.DriverCondition
and tblAccidentEvent.accidenteventid=Countydriver.accidenteventid
and tblaccidentevent.accidenteventid = tblvehicle.accidenteventid
and tblaccidentevent.collisionid = tblcollision.collisionid
and tblvehicle.damageid = tbldamage.damageid
and tblfactors.factorsid = CountyDriver.contributingFactors
and tblaccidentevent.LocationOfImpact = tbllocation.locationid
and tblaccidentevent.weathercondition = tblweather.weatherid
and tblAccidentevent.empid = theEmp.empid
and tblAccidentevent.trackingNumber = @trackingNumber

When I run this query, some values are blank even though there are records for them on the db like
factorsDescription from tblfactors and filename from ImageFiles table.
What am I doing wrong with the query above?

mikechan8888

You have 10 tables directly related to the main table tblAccidentEvent but in your query, you only linked it with 7 tables. Try to link these tables with tblAccidentEvent too:

ImagesFiles
factorsID
damageID

Not sure if it will solve all your problems as I am getting dizzy after long hours of work! But at least it will be a start!

simflex

sorry it should be more like this:

tblAccidentEvent table relates to theEmp by empID
tblAccidentEvent table relates to tblAccidentInfo by trackingNumber
tblAccidentEvent table relates to countyDriver by accidentEventID
tblAccidentEvent table relates to tblVehicle by accidentEventID
tblAccidentEvent table relates to tblCollision by CollisionID
tblAccidentEvent table relates to tblLocation by locationID
tblAccidentEvent table relates to tblweather by weatherID

CountyDriver table relates to tblDamage by damageID
CountyDriver table relates to tblfactors by factorsID

tblAccidentInfo table relates to ImagesFiles by trackingNumber