Query for tables with no primary key and having different number of columns (1 Viewer)

[Ramshan]

Hi Everyone,
How I can write a query and pull values from 2 tables having different number of columns and having no primary key.
I tried using SQL UNION ALL command, but his brings me only the common columns. My requirement is i need to pull all the columns from 2 tables for a particular query.
Example: Let table T1 Information related to schools and has 6 columns like SchoolID, SchoolName, City,County, Phone number, Ownedby
and T2 has 5 columns HospitalID, Hospital Name, City, County, Ownedby.

Now I need a query like this, If I enter the city name and hit enter, it should bring all the School information and Hospital information in that city.

P.S: In original data I have 59 columns in Table 1 and 57 in Table 2.

Thanks in advance!

 

[boblarson]

So it looks like you have two common fields - City/County. You can put the two tables into the SAME query, join City to City and County to County and then in the output put all of the fields you want from either table. Then in the City's Criteria I would have a form which has a combo box to select which city/county combo you wanted and then refer to it in the criteria.

[Forms]![FormNameHere]![ComboNameHere]

and that way you don't need to know how to spell the city correctly to have it work.

 

[Ramshan]

So will it look like this??

Select * from T1
FULL JOIN T2
ON ((T1.city=T2.city) AND (T1.county=T2.county));

 

[boblarson]

All except the Full Join part. Access doesn't support full joins.

It would be:

Select * from T1
INNER JOIN T2
ON (T1.City = T2.City) AND (T1.County = T2.County)

But that will only get you the information if that city and county exist in T2.