Beginner Question (don't hate me)... query by species to list counties w/hyperlinks

[Matt Clara]

Ok, absolute beginner here, and yes, I know I should just go and work through a couple good tutorials and a month from now I'll be able to answer my own questions. I wish I had the time, so humor me, take a look at what I've got, and then if you can, offer some suggestions, and if I'm way off base, point me to those tutorials and I'll tell the people who want this done that I can't do it. I hope that's fair enough.

I have a database I built myself (I know, we're off to a bad start right there!). It contains multiple tables, one listing species (fish in this case), and then there's a table for each specie with a list of county names and hyperlinks to web pages with more information on that specie of fish in that county, in this case the web pages tell what bodies of water you can go fishing for each specie with some expectation of catching one.

So, I want to be able to query the database for two things, species and county, say, brook trout and Grand Traverse County, and be returned a link that can be clicked on to open a web page. Doesn't sound difficult, but I've tried some tutorials and none of them seem to lend themselves to this kind of database work. Most want to help you create a database to easily allow you to add new data, which isn't what I need.

Here are some screen shots of my database so far:




I will send half of my daughter's halloween candy to anyone who can point me in the right direction as confirmed by my actually completing this project!
Thanks!


Matt

[ssanfu]

Welcome to the forum.

So....
One species can be in many counties and
One county can have many species

If both statements are true, then you have a many-to-many relationship.
You need a junction table.


At a minimum, the tables could be:

tblSpecies
SpeciesID_PK (Autonumber)
SpeciesDescription (Text)
{Other fields as required}


tblCounties
CountyID_PK (Autonumber)
CountyName (Text)
{Other fields as required}


tblSpeciesCounties (junction table)
SpeciesCounty_PK (optional -Autonumber)
SpeciesID_FK (Long Integer) link to tblSpecies
CountyID_FK (Long Integer) link to tblCounties
WebPageURL (Hyperlink)
{Other fields as required}

The junction table takes the place of all the other tables (AtlanticSalmon, BrookTrout, BrownTrout,...)

Use a main form/subform for data entry.
Main form could be a query based on the species - subform a query on the junction table. Use a combo box to select the county.
Or the main form could be a query based on the county- subform a query on the junction table. Use a combo box to select the species.

Users should NOT have access to the raw tables. Interaction should be by forms only.
Clear as mud?

[June7]

Agree with Steve.

For tutorial on form/subform, review: http://office.microsoft.com/en-us/ac...010098674.aspx

[Matt Clara]

Thank you, Steve and June--that points me in the right direction, and (hopefully) that's all I need!