wierd error

[fini123]

I'm currently getting a wierd error...

when i use the code below...

SELECT clients.[company name], cars.car_registration, price_list.[tyre id], -1*(front_left_tyre+front_right_tyre+rear_left_tyre+rear_right_tyre+spare_tyre) AS [tyre quantity], price_list.[customer price], clients.discount, job.[job id], [tyre quantity]*[customer price] AS total, ([total]/100)*17.5 AS vat, [total]+[vat] AS [grand total]
FROM price_list INNER JOIN (clients INNER JOIN (cars INNER JOIN job ON cars.[car id]=job.[car id]) ON clients.company=cars.company) ON price_list.[tyre id]=cars.[tyre id]
WHERE (((job.[job id])=3));

it displays the correct job until i get on to the last job, with id 4. If I set it to show data for job id 4 then it displays absolutely nothing. I find this really wierd because, as I have already said, the rest display fine. Does anyone have an idea what could be wrong and how i could fix it?

fini123

 

[nigelrivett]

Nothing with job_id = 4 or nothing with that job_id has entries in all the other tables.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[rrrkrishnan]

It is possible that the car_id associated with job_id 4 is not available in cars table, or the tyre_id that is associated with the car_id that is associated with job_id 4 is not available in the price_list table.

 

[fini123]

no I've checked it several times and its all there. When I add a record after the 4'th (which is the last) record i can then see all the data in the 4'th one, but not the one after that... it is always the last record no matter what the id is..

fini123

 

[rrrkrishnan]

Are you having a flat file which holds all the job ids, if that is the case, you need to have a carriage return after the last record (last job_id).

 

[fini123]

no its an MS Access database...

fini123