Blank Data Report From Unique List

[blongmire]

Hi All,

I'm looking for a way to build a report that will find which City names I haven't matched for each record in a separate table.

I have 3 tables and 1 query involved to solve this problem, one containing my clients job details [Client Data], one containing the other cities jobs [Competitor Data], one with the matches between the client jobs and competitor cities [Matched Jobs], and one (query) with a unique list of competitor Cities [Unique List of Competitor Cities].

So, how do I build a report to tell me, by record in Client Data, which cities I do not currently have a match for from [Matched Jobs].

Thanks for the help!

Bob

 

[TerryHogarth21]

I'm hoping I understand you correct that you have a query already that brings back the results you are expecting?

If you already have a query that solves your problem and that brings back the results you can just click on the query in Access then go to the Create -> then click on Report and name the report something.

When you click to open the report it will run the query and display the results of that in the report. You could always adjust modify and change the report in design mode to accommodate your needs.

 

[blongmire]

Hi Terry,

I don't have a query that generates the results I am looking for. I need help to build the query to display where I'm missing matches between my tables.

Thanks,

Bob

 

[TerryHogarth21]

Hi Terry,

I don't have a query that generates the results I am looking for. I need help to build the query to display where I'm missing matches between my tables.

Thanks,

Bob

Could you provide the fields in each of these tables to make it easier.

Also just so I understand is that [Client Data] is the table in which every other table will be compared to.
So for example if Phoenix is in [Matched Jobs] but not [Client Data], you expect that the query return Phoenix as the record from the Matched Job table because it doesn't exist in your [Client Data].

 

[TerryHogarth21]

'

 

[blongmire]

Hi Terry,

Here are the important fields from each table:

Client Data: contains 200 unique job titles from our client's city.
Job ID# - Autonumber
Job Title - text


Competitor Data: Contains 2,000 unique records of job titles from 15 unique competitor cities.
Competitor Job ID# - Autonumber
Job Title - text
City Name - text

Matched Jobs: Contains 500 unique records where we've manually selected which jobs from a competitor city are like the jobs from our client city.
Match ID -Autonumber
Client Job ID - number
Competitor Job ID - number

Unique List of Competitor Cities: Contains a unique list of the 15 City names from the competitor data table.

I'd like to have a query that shows, by client job title, which cities haven't been matched for that record. So, for Client Job ID 1 that may be Phoenix, Chicago, NY, and Miami, but for Client Job ID 2 it may be Boston and NY.

Ideally, I'd like the query to generate a record containing Client Job ID and a 1 of the city names not matched. Then I would have multiple records for each job ID that I could group on for reporting purposes. I'm 100% open to any better ways of building it.

Thanks for your help!

Bob

 

[blongmire]

I think I got it.

I built a query, with no join, between [Client Data] and [Unique List of Competitor Cities] to give me every possible match. I then created a concatenation of Client Job ID and City Name. Then, I built a query to show a concatenation of Client ID and City name for every job I've already matched (based on the job match query). Finally, I used a mismatch query to show me records from my every possible match query that weren't on my other query.

All in all, it works great and the report is perfect, but I'm not sure that was the cleanest way to do it.

Thanks for the help!

Bob