Hyperlink field based on other field

[jonny]

I need to create a table, with two columns only.
One column is a car model (string type)
2nd column is a relevant website (hyperlink type)

Example: Toyota ===> www.toyota.com
Honda ===> www.honda.com



How to create such hyperlink field?

[Rawb]

I would use two Tables instead of just one.

The first (CarMaker) Table would list the different car companies (Toyota, Honda, ect.) and their web sites. The second (CarModel) would list the individual car models and tie them to the car company (from CarMaker).

CarMaker Table
MakerID = Autonumber, Primary Key
MakerName = String, the name of the car company
MakerURL = String, the web site of the car company

CarModel Table
ModelName = String, the model name/number of the car, Primary Key
ModelMaker = Long Integer, the MakerID of the company that makes that model car, Primary Key

Then you can just use a Join to link the two tables and get the appropriate URL for each model.

SELECT
  [CarModel].[ModelName],
  [CarMaker].[MakerName],
  [CarMaker].[MakerURL]
FROM
    CarMaker
  INNER JOIN
    CarModel
  ON
    [CarMaker].[MakerID] = [CarModel].[ModelMaker]
WHERE
  [CarModel].[ModelName]='Corolla'

[jonny]

thanks, but given only model.
I need the link to website being created, only after new model added
how to implement that?

[Rawb]

My first post would do that.

If you look, the only thing I specify in my Query is the model Corolla, but it would return Toyota as the maker and http://www.toyota.com/ as the web site.

[jonny]

Rawb, I attached the file.
Could you please help. I'm a newbie in this area and really got lost.

[Rawb]

I took a quick look at your DB and everything you have so far looks good.

What you have to do now is design a Form that lets you add car models (and maybe car makers) to the system.

If you want to try it out just to see how it works, you can try manually adding some models to the CarModel Table. Then, to look up that car, just change your Query so it has that car's model instead of "Corolla".

[jonny]

We have some misunderstanding here,
The URL is supposed to be generated from ModelName, and not inserted manually.

[Rawb]

Unfortunately, that's not possible without also knowing the name of the company that made the car. Not all models include the maker in their names (in fact, most don't) so we have to manually include that linkage.

[jonny]

Let's assume the URL is www.<ModelName>.com,
How to implement this in the table field?

[Rawb]

If that's the case, there's no need to store that information in the database. In your Query just create a calculated Field that generates the URL for you:

SELECT 
  [ModelName], 
  "www." & [ModelName] & ".com" AS [ModelURL] 
FROM 
  CarModel 
WHERE 
  [ModelName]="Corolla"

As long as "Corolla" is a model listed in the CarModel Table, it should show up with the proper URL (in this case www.corolla.com).

[jonny]

Good, this is a progress.
Now just left to make this to be linked to web (to open website with this). How to do this?

[Rawb]

Queries cannot have hyperlinks inside them.

The only way to do that is to create a Form that converts it into an actual, clickable hyperlink (or to copy and paste it into a web browser).

[jonny]

so..?
theoretically I understand, but how to implement this?

[jonny]

Rawb, someone..?