Blank Data Report From Unique List

blongmire

New Member
Joined
Jul 3, 2014
Messages
35
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

TerryHogarth21

Board Regular
Joined
Mar 20, 2012
Messages
245
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

New Member
Joined
Jul 3, 2014
Messages
35
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

Board Regular
Joined
Mar 20, 2012
Messages
245
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].
 

blongmire

New Member
Joined
Jul 3, 2014
Messages
35
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

New Member
Joined
Jul 3, 2014
Messages
35
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,846
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top