Show all Records

[DavRob]

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

 

[theDBguy]

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.

 

[DavRob]

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

 

[Dreamweaver]

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

 

[theDBguy]

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

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