Show all Records (1 Viewer)

Hi All

I am using a query to compare the "Address" field in 2 tables "TblCustomersNoDuplicates" and "TblCompletedJobsAddressed"

The query below works fine but I would like to have all duplicated addresses displayed

I attend households and businesses to complete service work, therefore, there may be several identical addresses In "TblCompletedJobsAddressed" my goal is to have all identical addresses displayed in a continuous form so that I can see what has been repaired in the past compared to the new work request.

The new work request is in "TblCustomersNoDuplicates"

SELECT [TblCompletedJobsAddressed].Completed_Date, [TblCompletedJobsAddressed].JobNo, [TblCompletedJobsAddressed].[CustomersName], [TblCompletedJobsAddressed].[CustomersAddress], [TblCustomersNoDuplicates].[CustomersAddress]
FROM TblCompletedJobsAddressed INNER JOIN TblCustomersNoDuplicates ON [TblCompletedJobsAddressed].[CustomersAddress]=[TblCustomersNoDuplicates].[CustomersAddress]
WHERE ((([TblCustomersNoDuplicates].[CustomersAddress]) Like [TblCompletedJobsAddressed].[CustomersAddress]));


DavRob

Hi. But why do you have the same addresses in two tables? You should have one address table and use its ID field as a foreign key in the other tables.

Hi theDBguy

Thanks for your reply

The tables are obtained from different sources

"TblCompletedJobsAddressed" contains data of previous service calls to a premises that has been attended to in the past

"TblCustomersNoDuplicates" contains a request to attend a premises

1. This may be the first service call for that premises, therefore, we have no historical data of this premises, in this scenario the subform would remain invisible

2. It may be the we have attended the premises before, therefore, we would have historical data of work done previously, in this scenario the subform would become visible


DavRob

You still dont need two tables it's a works system have a look at the works manager tables on my site use link in my sig you can get all the information from queries

Mick

DavRob said:

Hi theDBguy

Thanks for your reply

The tables are obtained from different sources

"TblCompletedJobsAddressed" contains data of previous service calls to a premises that has been attended to in the past

"TblCustomersNoDuplicates" contains a request to attend a premises

1. This may be the first service call for that premises, therefore, we have no historical data of this premises, in this scenario the subform would remain invisible

2. It may be the we have attended the premises before, therefore, we would have historical data of work done previously, in this scenario the subform would become visible


DavRob

Click to expand...

Can you post a copy of your db, so we can see what you mean by showing all duplicate addresses? Thanks.

Does the query in your first post show all the duplicates? If it does, then bind that query to a form by using the query as the form's RecordSource.

That query does not need a where clause because you are joining on the field of the where clause. Also, if you needed a where clause, you would never use LIKE unless you were looking for partial matches and used wildcards to indicate where the fuzzy stuff is.

To truly compare addresses, you need to pull out the parts like house number, street name, street type and remove all punctuation. In some cities, they also use compass designations and depending on the city, they might come before or after the street name. So as your code is now, 101 Main Ave. will not match 101 Main Ave