Urgent HELP

[rblundell]

Thanks to anyone in advance that can help. Your help is really appreciated!

Basically I'm building a database for a university assessment came across a big problem, cannot get my head around it at all.

I have a table called Service_TABLE with the price of each service on each car. I created a Query to display the highest service that was made on all the cars that were serviced. However it seems to display all the prices for every service on each car over and over again. Really stuck :|

Some useful screenshots below :

Query Design View : http://tinypic.com/r/2gybuxg/6
Query Result : http://tinypic.com/r/24dj3vq/6

What is wrong with this code??

SELECT MAX(Service_TABLE.Service_cost), Vehicle_TABLE.Vehicle_ID
FROM Service_TABLE, Vehicle_TABLE;

 

[Rabbie]

Thanks to anyone in advance that can help. Your help is really appreciated!

Basically I'm building a database for a university assessment came across a big problem, cannot get my head around it at all.

I have a table called Service_TABLE with the price of each service on each car. I created a Query to display the highest service that was made on all the cars that were serviced. However it seems to display all the prices for every service on each car over and over again. Really stuck :|

Some useful screenshots below :

Query Design View : http://tinypic.com/r/2gybuxg/6
Query Result : http://tinypic.com/r/24dj3vq/6

What is wrong with this code??

SELECT MAX(Service_TABLE.Service_cost), Vehicle_TABLE.Vehicle_ID
FROM Service_TABLE, Vehicle_TABLE;

First all you need to establish a relationship between Service table and Vehicle table. At present you will get each record in one table linked to every record in the other table.

Try looking in Access help for information about JOINS

 

[spaddhu]

No. 1 -> Join your two table properly.
No. 2 -> Where is the group by clause in your query?
It does not make sense to select a Max of something, without saying to
group by some column. After defining the join, give group by VehicleId.

Your answer should come...

 

[georgedwilkinson]

Look up cartesian product. You did a self join with no criteria so every row in your table is joined to every row in your other table.