Single Query - Results from Three Tables

[maxx102]

Hello, I have a query question and I am not sure that Access is able to do this or not, but I thought I would ask.



I have three tables in Access that we use to track vehicles in our parking ramp.

Table 1 is a general vehicle information that we search by license plate. When we run the query, the little box pops up and we type in the plate number. If the vehicle is registered, it shows the make, model, color, license plate #, license plate state, and who registered the vehicle with our department (security).

Table 2 is a parking violation table. Again, we search by license plate and it shows how many violations the vehicle has (from zero to many) and what type of violations.

Table 3 is is a temporary parking permit. We search by plate or permit number and we receive our results.


What I would like to do is do one search and we receive on the screen the results from all 3 tabels. So if I ran plate number 123ABC, it shows the vehicle make, model, plate number, license plate state, who registered the vehicle, how many parking violations the vehicle has and any parking permits it has been issued.

Is there a way I can link these three tabels togehter?

[pbaldy]

Have you tried adding all 3 tables to a query, with joins from the first table to each of the other 2 on the plate number field? You'd want to edit the join to show all records from the first table.

[maxx102]

I have not. I'm not sure how I join the first table to the other two tables. I thought it might be relationships and but when I did a test, it didn't give me the result that I wanted.

[pbaldy]

What's the SQL of your query?

[maxx102]

Here it is...I hope it makes sense to you.

SELECT [Parking Registration].[License Plate], [Parking Registration].[License Plate State], [Parking Registration].[Vehicle Make], [Parking Registration].[Vehicle Model], [Parking Registration].[Vehicle Color], [Parking Registration].[Last Name], [Parking Registration].[First Name], [Parking Registration].[Company Name], [Parking Registration].[Work Email Address], [Parking Registration].[Office Phone], [Parking Registration].[Emergency Contact Number], [Parking Registration].[Vehicle Location], [Parking Registration].[Departure Date], [Parking Registration].[Arrival Date], [Parking Violation Table].[Ticket #], [Parking Violation Table].Issued, [Parking Violation Table].[CSO Name], [Parking Violation Table].[Violation Number], [Parking Violation Table].[Fines Given], [Parking Violation Table].[Fines Paid]
FROM [Parking Registration], [Parking Violation Table]
WHERE ((([Parking Registration].[License Plate])=[Please enter license plate number (example:ABC123)]));

[pbaldy]

You don't have a join between the tables, so presumably this is returning a lot more records than you expect, as it will produce a Cartesian product. In design view, click/drag between the license plate fields in each table. In SQL View the FROM line will look like:

FROM [Parking Registration] INNER JOIN [Parking Violation Table] ON [Parking Registration].[License Plate] = [Parking Violation Table].[License Plate]

By the way, in the long run you'll find the spaces in your names aren't worth the bother.

[maxx102]

So does this look correct?

SELECT [Parking Registration].[License Plate], [Parking Registration].[License Plate State], [Parking Registration].[Vehicle Make], [Parking Registration].[Vehicle Model], [Parking Registration].[Vehicle Color], [Parking Registration].[Last Name], [Parking Registration].[First Name], [Parking Registration].[Company Name], [Parking Registration].[Work Email Address], [Parking Registration].[Office Phone], [Parking Registration].[Emergency Contact Number], [Parking Registration].[Vehicle Location], [Parking Registration].[Departure Date], [Parking Registration].[Arrival Date], [Parking Violation Table].[Ticket #], [Parking Violation Table].Issued, [Parking Violation Table].[CSO Name], [Parking Violation Table].[Violation Number], [Parking Violation Table].[Fines Given], [Parking Violation Table].[Fines Paid], [Parking Violation Table].[License Plate]
FROM [Parking Registration] INNER JOIN [Parking Violation Table] ON [Parking Registration].[License Plate] = [Parking Violation Table].[License Plate]
WHERE ((([Parking Registration].[License Plate])=[Please enter license plate number (example:ABC123)])

[pbaldy]

Sure; does it work? You may want to edit the join by right-clicking in the line in design view.