Two queries almost identical except one doesn't work

[ruaridhmac]

I have an access 2003 database which holds data for lorry loads of timber delivered to different places at different prices and by different hauliers who get paid different ammounts.

I have tables that hold the prices for both haulage (sorted by the delivery location and haulier) and product price (sorted by the delivery location and the haulier.

I have two almost identical queries which give the haulage price for a particular load and the product price for a particular load.

Only the haulage prices seem to be returned - i have checked the settings and relationships in each of the tables invlovled and they appear to be that same.

Why would one query return the value and the other not???

 

[neileg]

It would help if you posted the SQL of the queries. Otherwise we're just guessing!

 

[ruaridhmac]

I'm new to this as you can probably tell

Here's the SQL of the two queries.

Haulage Price:
SELECT Loads.[Advice Note No], Loads.AL, Loads.Project, Loads.Contract, Loads.Customer, Loads.[Haulage Contractor], [Haulage Prices].[Haulage Price]
FROM [Haulage Prices] RIGHT JOIN Loads ON ([Haulage Prices].Haulage = Loads.[Haulage Contractor]) AND ([Haulage Prices].Customer = Loads.Customer) AND ([Haulage Prices].Contract = Loads.Contract) AND ([Haulage Prices].Project = Loads.Project);

Product Price:
SELECT Loads.[Advice Note No], Loads.AL, Loads.Project, Loads.Contract, Loads.Customer, Loads.Product, [Product Prices].[Product Price]
FROM [Product Prices] RIGHT JOIN Loads ON ([Product Prices].Product = Loads.Product) AND ([Product Prices].Customer = Loads.Customer) AND ([Product Prices].Contract = Loads.Contract) AND ([Product Prices].Project = Loads.Project);

To the untrained eye (me) they look pretty similar. See what you make of them.

One thing i couldn't work out was why 'haulage contractor' from the loads table is shown in square brackets and 'product' isn't. Both the queries were created in design veiw.

thanks, R.

 

[gemma-the-husky]

first, access places the brackets round field names with spaces in them, so that the field names are treated correctly. A lot of people always put hyphems or underscores in field names to avoid having to remember to include these brackets

secondly - are you sure the haulage prices and product prices tables are identical - is it possible that there is something wrong with the joins you have made that mean that the query returns no items.

(i take it this sql has come from the access query design window - in that case, as you have 4 links, try removing them, and reinstating them to see which link is stopping the records appearing!)

 

[ruaridhmac]

thanks gemma-the-husky,

still can't actually work out what was wrong but you're suggestions probed me to do a few things to try and fix it. Ended up copying 'haulage prices' table and modifying it to work for 'Product Prices'. This has worked and now the query returns the prices that i need. I'm assuming that there must have been something wrong with the table which i couldn't find as the table that now exists for 'product prices' looks exactly like the one that i had before????

also thanks for pointing out the use of square brakets - i'll now try not to use spaces in naming objects just to keep things simple.

Thanks again, R.