Anyone familiar with the sport of NASCAR?

[djclntn]

For years as a hobby I have in grossly maintain a database for every season of NASCAR results in ALL 3-Series, Bush, Cup, & Trucks. My database is set up for each season in which I copy & paste the Race Results off of the Internet & then into a Table. I include many fields like the Starting & finished position, Car#’s, Drivers, Makes, Sponsors, Status, & Owners. If you know/watch NASCAR variables changes from race to race in ALL 3-series. Driver’s today are driving (racing) in multiple series, in different car#’s (even within the same series) etc. The one element which doesn’t change in a given season & series are the “Car#” & the “owner.” So, after every race in all 3-series I’m able to copy & paste the results from the Internet, except for the Owner which I manually have to type in each race/series. I use the owners field for varies queries.



Recently I thought of the idea to create an Owners Table whereby I would only need to type in the owner once accoding to the Car# & series. Then I could join the Series Results Table & the Owners Table & in a query have ALL of the fields from the Series Results Table & the “Owners” Field from the Owners Table.

In the Series Results Table I have a SeriesResultsID (PK) & an OwnersID. In the Owners Table I have the OwnersID (PK) & the SeriesResultsID. I related the 2-Tables by the SeriesResultsIiD.

When I try to run a query with ALL of the fields in the Series Results Table & the “Owners” field from my Owners Table I get no records in return. Included in my Owners Table I have the “Series” field & the “Car” field which are also in my Series Results Table.

*Note, I do have someone on this form who has been so kind & patient in trying to help me, but as much as I try explaining to them about tNASCAR, if there’s someone here who’s familiar with NASCAR I would so greatly much appreciate to take a look at my database. It only has the 2-Tables & the 1-query

[Robeen]

There are no values in the SrsRsItsID field in the Owners Table - so when you join that field with the SrsRsItsID field in the Series Results Table - there are no matches.

[djclntn]

Don't I need the SeriesResultsId (PK) in the Owners Table in order to create a Relationship?

[Robeen]

I only looked at the query that you have in the database that you posted - since that was your question.

The query is not returning any records because there are no matches between the SrsRsItsID fields in the two tables.

What your query is saying [in non-SQL] is:
"Show me rows of data where the 'SrsRsItsID' fields in the two tables match AND where the 'CrTrcks' fields in both Tables match."

Try this:
Create a new query.
Don't select any tables from the Show Table box [just click 'Close'.
In the top left of the Access window - click SQL View.

Paste in this SQL:

 
SELECT Owners.OwnrsID, [Series Results].SrsRsltsID, [Series Results].Series, [Series Results].RcWk, [Series Results].Strt, [Series Results].Fnsh, [Series Results].CrTrcks, [Series Results].R, [Series Results].Drivers, [Series Results].Makes, [Series Results].Sponsors, [Series Results].Status
FROM Owners INNER JOIN [Series Results] ON (Owners.CrTrcks = [Series Results].CrTrcks) AND (Owners.Series = [Series Results].Series);

. . . run the query.

Tell me if that is closer to what you need.
I'm basing the above query on this that you said:

Recently I thought of the idea to create an Owners Table whereby I would only need to type in the owner once accoding to the Car# & series.

I'm still not convinced that this is the best way for you to go - but if it gives you what you need . . .

[djclntn]

It asks me to enter a Owners.series Parameter. First, I just click ok & got no results in return. Next I put in one of the series (NCWS) & in return I got all of the NCWS results minus the owners field

[Robeen]

I just followed my own instructions from my previous post - and I copied in the SQL from my previous post and when I ran the query - I was not prompted for any parameters.

I'm posting a screenshot of the query results for you to look at.

I have no idea why you are being prompted for a parameter.

[Robeen]

The other thing you can try is:
1. Create a query in design view,
2. Select both the tables.
3. Right-click on the 'SrsRsltsID' one-to-many Join line between the two Tables & delete it [since there is no data in the Owners table in this field].
4. Create a join between the two CrTrcks fields [drag the field from one table to the same field in the other table in the query design window].
5. Create a join between the two Series fields in the query design window.
6. Add all the fields you need in your query.
7. Run the query.

I just did this while I was typing in the steps - and it worked.

Let me know if that works!

[djclntn]

Itried yourfirst approach by putting this in a SQL:
SELECT Owners.OwnrsID, [Series Results].SrsRsltsID, [Series Results].Series, [Series Results].RcWk, [Series Results].Strt, [Series Results].Fnsh, [Series Results].CrTrcks, [Series Results].R, [Series Results].Drivers, [Series Results].Makes, [Series Results].Sponsors, [Series Results].Status
FROM Owners INNER JOIN [Series Results] ON (Owners.CrTrcks = [Series Results].CrTrcks) AND (Owners.Series = [Series Results].Series);

I'm still being asked for a parameter valus...?

Next I tried your second suggestion which worked & didn't work. In the query I'm NOT getting all of the records return..

Eg. In the Series Results Table I have about 899 records in the NCWS Series, yet in the query which has all of the fields from the Series Results Table & just the Owners field from the Owners Table I'm only getting 596 Records in return. Might you know why? overall m Seies Results Table contains 3,882 Records & my query is kicking back 2,603 Records. That's a difference of 1,279 missing...?

[Robeen]

If you look at my screenshot - I have circled 3882 at the bottom of the query. That is how many records my query returned.
I knew you would want to see all records - that's why I circled that number in red in the screenshot.
You must be doing something different - or perhaps you changed something in your database after you posted it . . . ??

[Robeen]

Does your Query design look like this [see screenshot]?

Make sure that the two joins are between the two 'Series' fields and the two 'CrTrcks' fields.

[djclntn]

I used the same dbs which I sent to you, no changes were made.

Attached is a pictureof the Design View & the Datasheet View

[Robeen]

Actually - I see two changes in your screenshot.
1. The Owners Table in the db you sent did not have a field named RcWk.
2. The Owners Table in the db you sent did not have a field named SeriesID.

. . . and I can't see all the fields in your Series Results table because the box is not fully expanded - so I don't know if you changed anything else.

[djclntn]

I think I made the following corrections & sent you back the dbs if you want to take a look. In the Results Owners query I'm still not getting all of the records from the Series Results Table...

[Robeen]

Your data has changed.

Now you have rows of data in the Series Results table that do not have a match in the Owners table.

You took the '#' sign off a whole bunch of rows in the CrTrcks field of your Owners table - but you left them On in the other table.

That is why the query is not returning the same results in your current db as it is in the copy that you first posted.

1276 rows in the query do not have OwnrsID - because the Owners table does not have rows in which both the Series AND the CrTrcks fields have matching data.

For example [these do not have matches in the Owners table]:
NCWS - #2
NCWS - #18
NCWS - #10
NCWS - #17
NCWS - #13

I'm pretty certain it is because you took the # sign off in the Owners table.

The Owners table now has rows with:
2
10
13
17
18
. . . in the CrTrcks field.

There's your problem.

[djclntn]

I'll have you know that had another thread going with the same question & I'd a lot of good people spending lots of time trying to help me fioigure this out. FYI, your approach worked best to fit my needs, but I sincerely & warmly thank everyone!