Page 1 of 2 12 LastLast
Results 1 to 15 of 26

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

    I have this Table which has many fields & I’m trying to breakdown this Table into 2-Tables, however I cannot get them to join in a query.

    Here is some of the Fields in my 2-Tables:

    Table 1: Series Results
    - SrsRsltsID
    - OwnrsID
    - Srs (Series)


    - RcWk
    - Start
    - Finish
    - CrTrck
    - Rookie
    - Driver
    - Make
    - Sponsor
    - *I use to have my “Owner” field in this Table, but I want to make a separate Table for the “Owners” which corresponds with the “CrTrck” Field.

    Table 2: Owners
    - OwnrsID
    - SrsResultsID
    - Owners (The Table is blank/has no data)

    In a query I tried taking the “Srs” & the “CrTrck” fields from the Series Results Table -&- the “Owners” Field from the Owners Table, but I get nothing in return.

    My objective/goal is to only need to type in the Owner correspondent to the CrTrck once vs. for every record as I’ve been doing.

    Any help would greatly be appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,010
    I don't understand the data relationships.

    Owners can have many results? Table2 appears to be a junction table for Owners and Results. This implies a many-to-many relationship and that each result will have many owners. What relationship does Owners have with CrTrck?

    I don't know what this item under Table2:Owners means: -Owners (The Table is blank/has no data)

    Consider:

    tblOwners
    OwnerID (primary)
    owner info fields

    tblEvents
    EventID (primary)
    event info fields

    tblEventOwner
    EOID (primary)
    OwnerID (foreign)
    EventID (foreign)

    tblResults
    ResultID (primary)
    EOID (foreign)
    Heat
    HeatTime
    Track

  3. #3
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    If I may let me explain my dbs a little further. I keep track of every NASCAR race per year which I’ll assume you’re unfamiliar with the sport. In a given year there are 36-races & about 43-drivers in every race – that’s 1,4548 Records in my Table. Now, many drivers don’t always drive the same Car # through the entire year (or season) & many times drives may drive different makes Chevrolet, Dodge, Ford, etc. & a lot of drivers have differ sponsors week to week – ALL of which I keep track in a database. No, I don’t physical type in ALL of the data; I have a way of copying every race off of the Internet, pasting it into an Excel worksheet & from there pasting it into my database tbl.

    The one aspect (for lack of a better term) is the Car# & the owner doesn’t change is always the same, which I have to update the owner field each week. I have certain queries dealing with the owners. Therefore, this upcoming NASCAR season I’m trying to think of ways to make less work for myself although I do this purely for enjoyment. I thought that if I could create an owners tbl which joins my main race results tbl I could avoid having to update the owners every race/week.

    I don’t know if I made any sense here? The “Owners” have NO info other than the car #

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,010
    The one aspect (for lack of a better term) is the Car# & the owner doesn’t change is always the same, which I have to update the owner field each week.

    I am confused by the above statement. If Car# & owner don't change, why is the owner field updated each week?

    If my suggested data structure doesn't fit then first thing you need to do is identify entities and relationships. Look at this tutorial http://forums.aspfree.com/microsoft-...es-208217.html

    Then post your selected design for analysis.

  5. #5
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    As I tried explaining, although the Car#’s & the Owners doesn't change other fields like the drivers may change according to the Car#. Therefore, for every race entry (Record) which has many fields I still need the "Owners" field in my query in order to run varies queries based on the owners like what drivers drove for which owners or how many sponsors, poles, wins etc. each owner has.

    In the past I would need to fill in the "Owners" field after pasting the rest of the races results into the tbl, but I thought that if I had an owner’s tbl which has the series, Car#, & Owner I could use the owners field in my Series Results tbl via query. This way when I enter a record with the series & car# the owner will automatically be entered.

    See, to make things a bit more complicated, in my Series Results Tbl I have 3-series, (NNWS, NSCS, & NCWS) which means that I can have the same car# (#14), but it has a different owner correspondent to the series. So, car# (#14) in NNWS may be Richard Childress. In NSCS, the owner for Car# #14 is Tony Stewart. In NCWS Car# (#14) is Jay Robinson.

    I have 2-Tbls:
    Series Results Table
    - SeresResultsID
    - OwnersID
    - RcWk
    - Series
    - Starts
    - Finsh
    - Car#
    - Driver
    - Make
    - Sponsor
    - Status

    Owners Table
    - OwnersID
    - SeriesResultsID
    - Series
    - Car#
    - Owners

    I related the 2-tbls by SeriesResultsID. When I create a query pulling ALL of the fields from the Series Results Tbl & just the Owners Field from the Owners Tbl, in return I get triple the amount of Records; in my Series Results Tbl I have 3.882 records & when I run the query I get 207,478
    Records…?

    Here’s the SQL:
    SELECT [Series Results].[SrsRsltsID], [Series Results].[Series], [Series Results].[RcWk], [Series Results].[Strt], [Series Results].[Fnsh], [Series Results].[CrTrks], [Series Results].[R], [Series Results].[Drivers], [Series Results].[Makes], [Series Results].[Sponsors], [Series Results].[Status], [Owners Query].Owners
    FROM [Series Results] INNER JOIN [Owners Query] ON [Series Results].Series = [Owners Query].Series;

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,010
    Very confusing. Car always has same owner(s) but each owner associated with one series? Tony Stewart is also a driver, right? Owner could be driver or driver might be hired?

  7. #7
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    also is an “Owner."

    <Car always has same owner(s) but each owner associated with one series?>

    No, I have the 3-NASCAR Series, "NNWS," "NSCS," & "NCWS." The only common theme here is, in each series the Car# & the Owner are always the same. That’s important to understand. Now, an Owner can have multiply Car# in one series & can also own multiple Car#’s in more than one series as well. So let’s take a well-known NASCAR owner like, Richard Petty who may own 3-Cars in the NSCS-Series, 2-cars in the NNWS, & 1-Car in the NWCS-Series, but the car#’s might not be the same in every series.

    Eg.
    NSCS: #43-Richard Petty, #44-Richard Petty, & 9-Richard Petty
    NNWS: #66-Richard Petty & #70-Richard Petty
    NCWS: #40-Richard Petty
    *This example is not accurate, but I’m just trying to show you how an owner can have multiple cars in more than 1-series.

    A driver, (now days) can drive (or race) in multiply series, & in different Car#’s (different owners) or within the same series throughout the season & many drivers race in more than just 1-series throughout the season. So a “driver” can have many-many variables during a season- race in multiple Series, Car#’s (Owners), Makes, Sponsors, ect.

    Eg.
    NNWS Car#18-Kyle Busch, Car#20-Kyle Busch
    NSCS Car#14-Kyle Busch
    NCWS Car# 33-Kyle Busch, Car#24-Kyle Busch

    So, my objective here is to have 2-tables. A table with every race results in one season for all 3-series with ALL of the fields I listed above excluding “Owners.”

    Then to create an Owners table that I can store whatever I need to relate/join the 2-tables in a query. So what I want to do is, when I add a new record with the series & the car#
    The owner from the owners table will be in my query vs. having to type in the owners for every record.

    If I’m confusing you more I’m really sorry. Possily I can send you anexample in an Excel Worksheet?

  8. #8
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    Sorry, Tony Stewart is a bad example because he is one of the few NASCAR "Drivers" who also is an “Owner."

    <Car always has same owner(s) but each owner associated with one series?>

    No, I have the 3-NASCAR Series, "NNWS," "NSCS," & "NCWS." The only common theme here is, in each series the Car# & the Owner are always the same. That’s important to understand. Now, an Owner can have multiply Car# in one series & can also own multiple Car#’s in more than one series as well. So let’s take a well-known NASCAR owner like, Richard Petty who may own 3-Cars in the NSCS-Series, 2-cars in the NNWS, & 1-Car in the NWCS-Series, but the car#’s might not be the same in every series.

    Eg.
    NSCS: #43-Richard Petty, #44-Richard Petty, & 9-Richard Petty
    NNWS: #66-Richard Petty & #70-Richard Petty
    NCWS: #40-Richard Petty
    *This example is not accurate, but I’m just trying to show you how an owner can have multiple cars in more than 1-series.

    A driver, (now days) can drive (or race) in multiply series, & in different Car#’s (different owners) or within the same series throughout the season & many drivers race in more than just 1-series throughout the season. So a “driver” can have many-many variables during a season- race in multiple Series, Car#’s (Owners), Makes, Sponsors, ect.

    Eg.
    NNWS Car#18-Kyle Busch, Car#20-Kyle Busch
    NSCS Car#14-Kyle Busch
    NCWS Car# 33-Kyle Busch, Car#24-Kyle Busch

    So, my objective here is to have 2-tables. A table with every race results in one season for all 3-series with ALL of the fields I listed above excluding “Owners.”

    Then to create an Owners table that I can store whatever I need to relate/join the2-tables in a query. So what I want to do is, when I add a new record with the series & the car#
    The owner from the owners table will be in my query vs. having to type in the owners for every record.

    If I’m confusing you more I’m really sorry. Possily I can send you anexample in an Excel Worksheet?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,010
    You can attach files to post. The Attachment Manager is below the Advanced post editor.

    For Access files, copy, remove confidential data, run Compact & Repair, zip if still large.

  10. #10
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    <P></P>

    Here's just the Owners & the Series Results Tbls

  11. #11
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    I thought that the missing piece to the puzzle was to include the RcWk into the Owners Table, but when I tried running a query with the Series Results & the Owners Table I get nothing. I'm missing somthing to join these 2-Tables.

    here' an updated version to my dbs

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,010
    To join these two tables why don't you populate the OwnerID field in Results table?

    What I am understanding so far:

    1. Car# has one Owner. Owner can have multiple cars. Owners can be in any series but the car runs in only one series. This is the association you want to establish with the Owners table. However, don't understand this: 'but the car#’s might not be the same in every series'. Might? Meaning they could? Richard Petty has car 44 in NSCS, could he also have a car that gets number 44 in NCWS? A car is permanently taken out of racing - another car will be assigned that number?

    2. A driver can work for any owner in any race in any car.

    Are there more tables to the database? Here is data structure I envision based on the entities I can recognize from your description of relationships.

    table of Owners
    OwnerID
    OwnerLast
    OwnerFirst

    table of Cars
    CarID
    OwnerID
    RacingNum
    Make

    table of Drivers

    table of Races

    table of Sponsors

    table of Results
    RaceID
    CarID
    DriverID
    Sponsors (multi-value field, hate 'em, alternative is a child table)
    Start
    Finish

    I don't understand the Makes and Status fields.
    Last edited by June7; 01-12-2012 at 10:42 PM.

  13. #13
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    First I wish to thank you for given me your time! Second, were you or were you not able to view my dbs I attached in a zip File? To address a few of your questions:

    1) < To join these two tables why don't you populate the OwnerID field in Results table?> If you mean the OwnerId PK from the Owners Table I do believe to have the OwnersID PK in the Series Results Table. Also, have the SeriesResultsID PK in the Owners Table which baffles me why I can’t pull these 2-tables together. I’m repeating myself here, but ALL I need is for the Owners Table to relate to the Series Table according to the “Series” & “Car.” I think that I tried to explain my objective above.

    2) <'but the car#’s might not be the same in every series' Might? Meaning they could? Richard Petty has car 44 in NSCS, could he also have a car that gets number 44 in NCWS? A car is permanently taken out of racing - another car will be assigned that number?> A) A Car# (#44) might be in more than one series, but the owner assigned to the Car# in each series doesn’t change throughout ‘a season.’ B) Once a car is taken out of “a race” due to an accident or other failures another car is not assigned that number. Eg. If Car 44 in the NCWS series gets in a crash the Car #44 in the NCWS doesn’t return until the next race. However, there may also be a Car #44 in the NCSC Series which has no relationship with the Car #44 in the NCWS even though they may or may not have the same owners.

    3) <Are there more tables to the database?> No, just 2-tables; Series Results Table & Owners Table. Although I may toy around with your suggested database table(s) structure, truthfully I not very found in having ALL of those Tables – it to confusing.

    4) <I don't understand the Makes and Status fields.> The “Make” field is the make of the car. Eg, Chevrolet, Ford, or Dodge. The “Status Field” tells how every Car# finished, whether it was still running, as in an accident, had engine failure, etc.

    I hope that you will stick with me on this journey for however long you’re willing, but I do remember you help me out in the past with other questions. With said I may go on the General Forum here & see if there’s anyone here who’s familiar with the sport of NASCAR. Again, I indeed appreciated your help!

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    40,010
    Yes, I am looking at your file and trying to get a handle on the data relationships. Doesn't really require an intimate knowledge of NASCAR. Your explanations are sinking in. Starting another thread on same topic would be considered bad forum etiquette. You could request thread be moved, however, that probably won't accomplish much. If other members view threads same way I do, we see all regardless of the specific forum posted in.

    It seems that CrTrks, OwnerName, Series create a compound unique identifier and these fields are in both tables. I tested this join and seems to work. If the result is correct, can use this as the basis for an UPDATE query to populate the OwnerID field in Series Results table. This will provide a single field key to join the tables and those three fields can be removed from Series Results.
    SELECT [Series Results].*, Owners.OwnerID, Owners.Owners
    FROM [Series Results] LEFT JOIN Owners ON ([Series Results].Owners = Owners.Owners) AND ([Series Results].CrTrcks = Owners.CrTrcks) AND ([Series Results].Series = Owners.Series);

    I suggest removing the link between SrsRslts fields in Relationships. And this field is not appropriate in the Owners table. I have never seen Relationship linking between Navigation Pane groups. Wonder what this accomplishes.

    Also advise removing the # character from the CrTrks values. The # is a wildcard character in Access and VBA. Having it as part of the value might cause issues.

    Also recommend not using spaces, special characters, punctuation (underscore is exception) in any names, nor reserved words as full name.

    Then I think Makes field belongs in the Owners table and Status in SeriesResults.

    My question about reassigning car number wasn't referring to a car being pulled from a single race, it was about a car being completely demolished or otherwise retired, never to race again. What happens to that number?

  15. #15
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    477
    I'm sorry but I think that I'm about to just give up & reverting back to my old method. I read & reread what you suggested & I can't wrap my brain around what you're suggesting me to do. Besides doing an update query which I tried & got nothing I don't understand why the 2-tables have NO relationship? In my mind this seems so simple, yet I'm just getting more & more frustrated with myself. I'm not happy with myself in not understanding & achieving my goal/objective.

    Now, about special characters like #, I know not to special characters in field names & object names & now I'm realizing that I also shouldn't use SC as values too. I have over 1500 records with the # sign in the field, how do I get rid of the #-sign while still keeping my number?

    I know that I'm asking a lot from you but you have a copy of my database. Any chance you might be able to do what you suggested & send if back to me. If you're too busy & don't have the time I understand & again I thank you for the time you've given me!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Options when joining two tables
    By Kevin_ in forum Queries
    Replies: 9
    Last Post: 07-29-2011, 09:06 PM
  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