How to ?

[Jigs]

Hi All,

I have 2 tables in my website project.

[1] Country - which consists of fields like Zonecode, countryname, locations
as shown below:

ZoneCode Country Locations
------------------------------------
Zone1 Australia LocAus01
Zone1 Australia LocAus02
Zone1 Australia LocAus03
Zone1 Australia LocAus04
Zone2 Canada LocCan01
Zone2 Canada LocCan02
Zone2 Canada LocCan03
Zone2 Canada LocCan04

[2] Cars - which consists of the all the cars available in the zonecode as shown below :

ZoneCode CarType Car Description
---------------------------------------------
Zone1 CarA aaaaaaaaaaa
Zone1 CarB bbbbbbbbbbb
Zone1 CarC ccccccccccc
Zone2 CarA aaaaaaaaaaa
Zone2 CarG ggggggggggg
Zone2 CarH hhhhhhhhhhh

Now, I want to have the report where it should display Countryname along with the cartype as shown below:

ZoneCode Country CarType Car Description
------------------------------------------------------------
Zone1 Australia CarA aaaaaaaaaaaa
Zone1 Australia CarB bbbbbbbbbbbbb
Zone1 Australia CarC cccccccccccc
Zone2 Canada CarG gggggggggggg
Zone2 Canada CarH hhhhhhhhhhhhh

I only knows that the query will have joins in it. But I do now know how to use it.

Can anybody please help me ?

Thanks in advance,

Jigs

 

[jfly]

first need to resolve other problems

I don't know if it's just your example but there is a relationship problem between your tables. Seams to me that the Locations is the field key so you would have to also put the locations in your cars table. Then you can, if you want so much to put the country name you can take the country name from the country table. But you have to do a 1 to many relationship to make it work.

So what you need:
to change your car table:
Locations CarType Car Description
---------------------------------------------
LocAus01 CarA aaaaaaaaaaa

Hope it helps

 

[Jigs]

No Relationsship

Hi, There are no relations ship between the tables. There are total 1800 to 2000 records in both the tables. I am not suppose to make any relationsship between any table.

Please help me in solving this query without using relationship.

 

[neileg]

jfly is right. The table relationships are strange here. The field linking the two tables is not a primary key in either table. If you join the two tables in a query it will return multiple records from the Country table because the location codes make these records non-unique.

So what you need to do is eliminate these duplicates. First create a query based on the Country table. You need the ZoneCode and the Country fields. Set the Unique values property for the query on. Save this query.

Create a second query. Add in the query you just made plus the Cars table. Join these on the ZoneCode field. Add ZoneCode, CarType, Car Description from the Cars table and Country from your query to the query grid. Run the query.

 

[Pat Hartman]

Neileg's solution will work as long as the data cooperates. In your example, the country table shows only one country for a zone but unless there is a unique index on the combination of zone/country, nothing will guarantee this. If there is bad data in the tables, it is possible that even the query suggested by Neileg will return duplicates and that will mess up your report. The design issues need to be resolved.

 

[neileg]

Thanks, Pat. I had overlooked the possibility of bad data messing this up.