Results 1 to 8 of 8

Joining 2-Tables in a query

  1. #1
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477

    Joining 2-Tables in a query


    It's my understanding that data can be pulled from more then one table into a query. Say I track baseball daily game results (win, lost, home or away wins, points for & against, etc) all data that can vary. Now within this table when I input every team's home & away game I also need to include the Teams "League" & "Division" which remains the same per team for tracking varies standing queries, ect. So I set up a seperate Table with the Teams (30-Teams) league & division. In a query I joined my Results Table with my Teams Table where by when I enter in a team into a field the accossiate league & divison appears within the query, great! However here's the problem, all of the other fields from the Results Table -- in the query ('W' 'L 'HW' RW' 'PF' 'PA' etc) cannot be edited or no data can be inputted within that query. Suppose I have a field which contains a LeageWin & a DivisionalWin -- everytime a team wins in its League a "1" goes into the 'LeagueWin feild. Likewis with the divison a "1" goes into the 'DivisionWin' field. But, I can do this only in the Results Table which has the teams & the Divison Wins fields, but not the 'League' & the Divisional fields. So if I don't recall every teams divisional I'm prone for big time errors.

    Admitting being an amature (or below?) at Access to me it's a win/lose kind of thing. Meaning, you discover one way to cut down your work/make things easier, but then if affects other parts of your database which only tends to create more work, problems, & FRUSTRATIONS!!! But, I will not give up until everyone stops answering me here which I can't see happening.

    As always, any help would greatly be apprechiated!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,148
    Yes, tables can be joined in query but the result will not always be editable. Would need to know more about your data structure. Want to provide project for analysis?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    June,

    I'm holding off from sending you this project query until we get to some resolution with the Dealership--Zip Code project. I don't want to overwhelm you, although I may have already :-)

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,148
    If you want to continue with this then provide the project and will review.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    Yea, you were right... sorry

  6. #6
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    Sorry, here's the dbs

    Focus on the 'Reg Season Query' I joined the Reg Season results with the Teamstable to join the 'Team' witth the 'Conf' & 'Div' from the Teams Table, however now I can make any changes to the query (or Form). It's great how I have it set up whereby when I enter in a 'Team' I don't need to enter in it's 'Conf' &''Div', however thre downside is that i cannot enter in any other date
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,148
    First issue I see is that Teams table has the autonumber field as primary key but you are saving the team abreviation in the Reg Season table. I think we have discussed this before in regards to your other project. If you are going to save the ABRV field value, this needs to be the primary key. Should not be saving all that team info in Reg Season table, only the key. After fixing the primary key I had no problem editing the query.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  8. #8
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    Perfect! Thanks a bunch, June!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining 2-Tables in a Query
    By djclntn in forum Queries
    Replies: 25
    Last Post: 01-13-2012, 11:54 AM
  2. joining tables
    By frozendmj in forum Queries
    Replies: 5
    Last Post: 04-19-2011, 12:45 PM
  3. Joining more than 2 tables
    By anemoskkk in forum Access
    Replies: 6
    Last Post: 04-17-2011, 03:37 AM
  4. Help - Joining Tables..
    By fabian24 in forum Forms
    Replies: 3
    Last Post: 02-17-2011, 02:43 PM
  5. Joining or Combining Two tables
    By escuro19 in forum Queries
    Replies: 2
    Last Post: 02-16-2010, 02:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums