I have a select query that returns 151 business addresses from a “site” table. The address is composed of a street number and street name that can easily be reported as calculated values. No problem until my next step.

I want to now select all of the records within a date range from an “incident” table that have a street number and street name equal to each of the 151 addresses previously queried. The “incident” table has no “address” field, just the street number and street name fields.

I am thinking that a subquery might be the way to approach this dilemma. However, I am self-taught and have NO experience with subqueries. I really need expert suggestions that are detailed. Your efforts will be greatly appreciated!

So your incident table has two fields, one for [StreetNumber] and one for [StreetName]?

What about using a query to concatenate these ... Address: [StreetNumber] & " " & [StreetName] ..., then using that to compare with what you already have?

CAUTION!!!

Does the address known as "1234 Elm St" = the address known as "1234 Elm Street"? Or even if they are spelled identically, are they both in the same city? Or if one includes "Apt1" and another includes no apartment? Or ...

(hint: trying to match by "address" is risky!)

Amend to give the fields you require...

Select Sites.*, Incidents.* from
Sites, incidents
where Replace(Sites.Addressfield, " ","") = replace(Incidents.StreetNumber & Incidents.Streetname, " ", "")

Won't be quick though. And obviously spelling differences will not match. (High St does not equal High Street)

I'm not worrying about spelling data quality at this point. I will research more about the REPLACE function because I am not familiar with it.
But both responses inspired me to try other approaches.

I did try something else and it worked in the manner in which I wanted it to. That is, instead of using address information, I used name. I still first queried the "site" table by site_type (CHUR) to get the specific site types and related information (either address or name). This time, instead of retaining the address, I kept the site_name. The "names" table lastname variable has the same data as the "site" table site_name. I was able to then retrieve the "incident" table records that has the same "name" as those queried from the "site" table using the "names" table joins with the other two tables. The following is the SQL code that worked:

SELECT dbo_lwmain.inci_id, dbo_site.site_name
FROM (dbo_lwmain INNER JOIN dbo_nmmain ON dbo_lwmain.name_id = dbo_nmmain.name_id) INNER JOIN dbo_site ON dbo_nmmain.lastname = dbo_site.site_name
WHERE (((dbo_lwmain.date_occu)>=DateSerial(Year(Date())-3,1,1)) AND ((dbo_site.site_type)="CHUR"))
ORDER BY dbo_lwmain.date_occu;

When I try the same approach by using address information and modifying the JOIN statement to allow the concatenation of the street number and road name, it doesn't work (no records returned). Here is that code:

SELECT dbo_lwmain.inci_id, dbo_site.site_name
FROM dbo_lwmain INNER JOIN dbo_site ON dbo_lwmain.streetnbr & " " & dbo_lwmain.street = dbo_site.address1
WHERE (((dbo_lwmain.date_occu)>=DateSerial(Year(Date())-3,1,1)) AND ((dbo_site.site_type)="CHUR"))
ORDER BY dbo_lwmain.date_occu;

If that happened to me, I'd run off a quick query of each table, looking for discrepancies or other issues that jumped out at me re: the address info...