Join Table Issues

[Benoni2]

So here's a fun question I would really appreciate some help on. The actually problem uses more complex data, but if anyone can help me solve the problem below, either with a VBA example or through querries, I would really appreciate it.

Okay, so:

Two tables:

Table 1

AgencyID Begin R End R
a1 1 100
a2 101 200
a3 201 300


Table 2

agencyID IdNum data 1 data 2…
33 bla bla
124 bla bla
222 bla bla
137 bla bla
12 bla bla
298 bla bla

Okay, so what I need to be able to do is take the two tables, compare the IdNum of the second table to the Range of the first table signified by BeginR and EndR, and if it is in a particular range, I need the agencyId field of the second table to inherit the agency id from the first table.

I have thousands of records, and a couple hundred ranges in real life, so I can't set up an if statement for each range of numbers without being there for a few weeks.

I was thinking something along the lines of an for loop that goes to each record in the database in the first table, but I don't understand enough about vba or for statements to get there. Any help or small examples would be greatly appreciated.

 

[Benoni2]

as follow up, the colums don't line up well in my tables above, but I think you can get the idea. However, in the second table, it's important to note that agencyID is empty all the way down. The three columns apply to the last three column titles, Idnum, data1, and data2

 

[JoeAtWork]

The SQL statement might be something like:

UPDATE [Table 2] t2 SET t2.agencyID = (SELECT t1.AgencyID FROM [Table 1] t1 WHERE t2.agencyID
BETWEEN [Begin R] AND [End R])

I'm not so sure about the syntax, but I think the logic is correct. I've made the assumption that the ranges don't overlap, i.e. the SELECT statement will return only one AgencyID for every agencyID in [Table 2].

 

[TheAceMan1]

How are ya Benoni2 . . .

In the following SQL, [blue]you![/blue] substitute proper names in [purple]purple[/purple]:

[blue]UPDATE [[purple][b]Table 2[/b][/purple]], [[purple][b]Table 1[/b][/purple]] SET [[purple][b]Table 2[/b][/purple]].AgencyID = [[purple][b]Table 1[/b][/purple]].[AgencyID]
WHERE ((([[purple][b]Table 2[/b][/purple]].IDNum) Between ([[purple][b]Table 1[/b][/purple]].[Begin R]) And ([[purple][b]Table 1[/b][/purple]].[End R])));[/blue]

After your substitutions, copy/paste the SQL to the [blue]SQL View[/blue] of a new query and check it out! . . .

See Ya! . . . . . .

 

[Benoni2]

Hey, thanks so much you guys. It worked great. I was hoping to do it vb, but this is much faster, waist of time trying to program it the other way. thanks.