I just DON'T know how to relate Tables - PERIOD!

[djclntn]

I'm sorry but I'm a very honest straightforward person & if I seem angry it's ONLY at myself. While working in one of my (many) databases I realize that I could cut down oin input data by relating 2-tables in a query, but of course it's NOT working for me naturally. The database is all of the NHL Games in a given season -- almost 3,000 Records. Each time a team plays I enter the 'Date' Home or Away' 'Conf' 'Div' 'Team' etc in a Table named, Results. I also have a table named, Teams which contains varies info regarding each 32-teams including the 'Conf' & 'Div" 'Team." Just today it had occurred to me, why can't I pull the 'Conf' & the 'Div' from the Teams Table into a query with my Results.?

Eg. The team Philadelphia Flyers (PHI) in in the 'EASTERN' Conference & in the 'Atlantic' Division. which I have in my Teams Table just once, but many times in my Results Table. I tried joining the tables a few ways, but nothing is working.

- I tried joining the TeamsID from the Teams Table (1) to the TeamsID (many) in the Results Table. When I ran a query pulling all of the fields from the Results Table except the 'Conf' & the 'Div' fields which I pulled from the Teams Table -- no results. Now, out of trial within this query Ijoined the 2-'Teams' field from both tables & I got my results. However one problem, it won't let me edit or add new records.
- I also tried relating the tables by the ResultsID...?



I really hete myself for continually pestering you guy, but as I always say I reallyapprechiate you guys

[aytee111]

Would you like to attach you database - or part thereof?

[djclntn]

Just focus on thr Reg Season Tables & Queries & t2011--2012 Season1.ziphe Teams Table

Thanks

[aytee111]

Have a look at New-tbl and New_form. I have simplified things a lot, but it is a way for you to enter match results. See if this is on the right track before taking it to the next level.

I'm not sure exactly what your problem is - are you having issues trying to enter the results or report on them or what?

[djclntn]

I'm sorry, but yot got me totally lost, but I do thank you for your time. I thought that possibly I could create a Relationalship between the Results Table & the Teams Table whereby when I input a given team into a query or form the 'Conf' & the "Div' correspondent with that team from the Teams Table will automatically come up. I can't understand, I have other databases where I have Tables joined pulling data from both tables in a query. I tried to replicate the same thing in those databases tio this one, but it's not working. I "though" that Icould elimnate having to type in the teams 'Conf' & 'Div' by pulling thoses 2-fields from the Teams Table. I'm starting to see that maybe I lack the right Access "lingo" in trying to explain what I want to acheive.

I can send to you one of those databases& show you what I mean?

[aytee111]

I don't think that sending a different database is going to help. I think you are doing something different here.

Your table - as well as my NEW one - both have two teams so cannot link directly to the teams table as permanent relationships. Any linking will have to be done manually. My NEW form does this by using combo boxes, where you would select the team from the Teams table but do it twice - the home team and away team. The NEW table does not contain anything except the ID taken from the Teams table - on the form it is only displaying the one field but it could easily display them all (all the ones shown when you click the drop down).

Here is an example of a query that shows who played who, when, and the results (further information can be added to the table later). Instead of sending you the whole database again, just copy and paste this into a new query:

SELECT New_tbl.MatchDate, Teams.CONF AS HomeConf, Teams.DIV AS HomeDiv, Teams.CITY AS HomeCity, Teams.TEAM AS HomeTeam, New_tbl.HomeTeamScore, Teams_1.CONF AS AwayConf, Teams_1.DIV AS AwayDiv, Teams_1.CITY AS AwayCity, Teams_1.TEAM AS AwayTeam, New_tbl.AwayTeamScore
FROM (Teams INNER JOIN New_tbl ON Teams.TeamsID = New_tbl.HomeTeamID) INNER JOIN Teams AS Teams_1 ON New_tbl.AwayTeamID = Teams_1.TeamsID;

[orange]

You seem to have need of a model similar to Andy
https://www.accessforums.net/showthr...-Database-Help

Maybe there is enough interest for you, Andy and others to create a workable, shareable model for team sports with teams, games etc.