Relational fields

[critter]

I'm sure this is a super easy question for all of you but I'm relatively new to this and don't want to screw it up. Disclosure: I actually run a relational database in my day job...but haven't built one from scratch. I am familiar with the basics of Access and have very advanced Word/Excel knowledge.

I want to build an Access db to track world travels. Trip dates, countries visited, hotels, memorable meals or sites seen, etc. I have 'countries' as a lookup field against a table that lists them all. How best do I set up cities and hotels to be dependent on each? For example, maybe I've been to Paris 3 times but stayed in two different hotels. On one of the trips I also went to Germany so that trip has to have both Germany and France as countries, each with dependent cities and within those cities, hotels and restaurants. How do I relate them all to each other? I want to eventually be able to query on a certain city and see where I stayed and ate (and if I get super fancy, a 1-5 rating for each!).

Thank you in advance!

 

[theDBguy]

Hi. Welcome to AWF!


Have you started to create your table structure? If so, you might consider posting a screenshot of your Relationship Window to give us a better idea on how to best help you.

 

[critter]

Thank you! My db is so basic I'm embarrassed to share but this is what I have so far:

I am starting with cataloging my grandmother's trips (hence the dates). I am trying to follow in her footsteps but need a way to organize the information, which is currently kept in longhand notebooks and albums.

The basics are fine but I need to build a connection between hotel, city and country so that when I'm ready to go to, say, Bermuda, I can see what hotels she stayed in on various trips. Thank you for your warm welcome!

 

[vba_php]

hello critter,

there are many things you can do with this. I would say it all depends on how you want to look at your stuff when it's reported back to you. for instance, if you don't mind looking at non-relational data, then what you have already is just fine. but if you wanna set up a relational structure then you should probably have table 1 'TRIPS' that has TRIP NUMBER as an autonumber and PK field, table 2 'COUNTRIES VISITED' that has 'TRIP NUMBER' as a LONG field and FK to table 1's autonumber and it's own autonumber as well as a PK, then table 3 'HOTELS STAYED AT' that has 'COUNTRY ID' as a LONG field and FK to table 2's autnumber field and it's own autonumber as well as a PK.

this will give you 3 relational tables. your picture obvioulsy shows more fields in your only table, but they are not visible so it's possible to set up even a deeper nested structure than this. but even though I've give you 3 possible tables to set up, you can even go crazier by putting 'memorable meals' in their own table as separate records as well and relate it to table 3.