Joining 2-Tables in a Query

[orange]

Does your database use special features of Acc2010.
I have Acc2003 and can only read mdb.

If you can post a copy as mdb formast, I'll help where I can.

[djclntn]

Hey Orange,

June7 has been great & VERY patient with me, but maybe you can take a crack ait. I'll assume that you read my thread & have an idea of what I'm aiming for.

Thanks for giving it a look!

*I wasn't able to provide you with the query I had in trying to pull all the fields from the Series Results Table & the "Owners" from the Owners Table. Also, I'm shuutting down for the night maybe I'll catch you tomorrow.

Thanks again!

[June7]

Okay, maybe seeing will bring understanding. The attached shows how I suggest modifying the tables. I only modified the two tables. Did not expand to the data structure I suggested.
EDIT: Purpose served, file removed.

I ran an UPDATE query to populate the OwnersID into SeriesResultsRev. Then added/removed fields as seemed appropriate. I was going to update the Make field in OwnersRev but seems to be some errors in this field in SeriesResults. Example, is this correct:

Owners|CrTrcks|Series|Makes
Andrea Nemechek|#87|NNWS|Chevrolet
Andrea Nemechek|#87|NNWS|Toyota

I discovered a name with quote marks: James "Billy" Ballew. I teach my users not to use quote marks in any data entry, and apostrophes only where I give consent. These are special characters in VBA and SQL and can cause issues in programming when encountered in data. I don't know if macros are troubled by them. If you will never use any code then probably okay but I replaced them with parens.

Performing Search and Replace on a wildcard character requires special treatment. In the Search/Replace dialog Search box type [#], then in the Replace don't put anything, then click ReplaceAll.

[djclntn]

June,

I owe you a ton, thanks a lot! I do need to asks you a few more ?'s

1. I have other seasons NASCAR databses which I want to change over to this new datasbase format. In the series Results Table how did you get rid of the "#" in the CrTrck Field? Can you please tell me step-by-step?

2. In a query are you able to control the allignment of vaules in a field (s)? Like, I want my CrRck values all to bbe alligned to the right, can I somehow do this in a query?

[orange]

I have looked at your database quickly. It appears, and you have said, that you copy and paste the results from some source into the database. From my quick test, the records for a given RcWk and Series are displayed in the order of finish.
For example
Series Results SrsRsltsID Series RcWk Strt Fnsh CrTrcks R Drivers Makes Sponsors Status
5933 NNWS 20 3 1 #60 Carl Edwards Ford Fastenal / GearWrench Running
Part of the issues you are having, based on my reading of this thread, is that you are intimately familiar with the terms/acronyms and structure of your data and NASCAR, and we are not. So immediately there is a communications issue. One that can be overcome, I'm sure. First is the NNWS(NationWide), NCWS(Trucks),NSCS(SprintCup) series. From a database perspective, people are trying to sort out exactly what things are involved, and how they relate. June has tried to do that by proposing some structures. And that is the traditional approach. However, I can see that you copy a particular record format which in itself is not normalized, but that is what you are using. So, my view is that we're working from the data you have and the processes you are familiar with. All that to say, let's not work from a traditional database E/R design approach.

So, you have weekly results by Series, by raceweek(RcWk) with a variety of fields that you are familiar with.

In my view,there is a need to clarify the definitions of a few things.

Owner for example - seems that what you have is a combination of Name, Series, Car/Vehicle Number, and RaceWeek.

A few things I don't see are Track, Date, Year which seems a little strange since you have kept data for a few years (I think that's what I read).

So if you had a schedule of Series, Track, RaceWeek, Date you could bring these together with the results (I think) to have a database spanning years.

As has been pointed out , the quotes and # signs in the data will be an issue.

Multiple sponsors can be determined by the "/" character.

Comments?

[djclntn]

I can answer of your questions right off the bat. Here is a link (an example) of where I get my data results from the Internet which I first paste into a Excel Worksheet, make a few adjustments, copy, & then plop into my Table each week. Here I go repeating myself, but the oone field in which I can't copy/paste idss the Owners field which I manually have to type in each week (or race). Originally I thought that if I could have an Owners Table withtheseries, Car, & Owner i could join it with the Series Results Table in a query. Thereby, my visions were that when I entered a record with the series (NSCS) (Sprintcup) & say Car (15) the owner of car (15) in the NSCS Series from my owners table would automatically be updated in my owners field in my Series Results Query. Apparently what I thought would be simple is now turning into a 4-day project.

It is correct that I've bbeen keeping track (or records) of NASCAR 3-Series dating back to 2000, however each season/year I start a new database which is why I don't have date fields or track fields, etc.

I asked June, but I'll ask you too, how do I remove the"#" in my carTruck field while still bkeping the number? And an important note, the Car or Truck number needs to be in text format because I may have vechicle numbers like, "01"& "1" -- if there're in nuber format the "01
will come up as, "1."

[djclntn]

Here's the link of where I retreiive my race results each Week

http://www.nascar.com/races/cup/2011..._official.html

anexample

[June7]

1. Removing the # character is a Find/Replace. Open the table, click Search/Replace on ribbon Home>Find. Performing Find and Replace on wildcard character requires special treatment. In the Find/Replace dialog Find What box type [#], then in the Replace don't put anything, then click ReplaceAll.

2. No. Alignment is a property of controls on form and report.

[orange]

Why do only some CrTrcks have the # sign? I see what you mean 01 and 1 are different CrTrcks.

[djclntn]

Thanks June for that tip. I wasn't putting in the brackets

[June7]

As I said, it's sinking in, slowly, but get it now. Your data download doesn't include the OwnersID. So both tables must keep the Series, CrTrcks, Owner fields. Then the two tables can be joined on this compound key with the query I provided in earlier post. That join query will then be the basis for an UPDATE of the OwnerID field in SeriesResults table. Access Help has guidelines on using the designer and wizards to build various queries.