Need to reduce # of helper queries to get to desired output

[skydivetom]

Experts:



I need some assistance with streamlining a query (or # of queries). Allow me to provide some background first.

The attached database includes two (2) tables:
1. tblCity
2. tblFiscalYear

Background:
- tblCity includes forty (40) example records.
- Ultimately, there are 20 distinct cities (currently 2 each in this example) in field [City]
- For the most part, the value in field [Code] is the same for the cities (e.g., record #2 and #22 both equal Charlotte and B).
- However, in this sample data, there are cities where the codes has changed (e.g., "Chicago"). That is, record #3's [Code] = C while record #23's [Code] = D.

What I want to achieve:
- I want to identify the cities where the code has changed.
- In this case, my Query4 correctly outputs: Austin (A, Z); Chicago (C, D); Houston (H, S).
- Unfortunatly, I currently have to use three (3) "Helper Queries" in order to get the output in Query 4:
-- Query1 serves as input to Query2
-- Query2 serves as input to Query3
-- Query3 serves as input to Query4

Existing Problem: I use too many "helper" queries and I need to mitigate the # of required queries to get to the desired output (shown in Query4).

Goal: How should I modify the query process so that I don't need the 3 helper queries? Ideally, I would have a single query (but using one helper query -- if required -- would be ok, too).

Note #1: Next year, I will add the same 20 cities to the data set. So, when using a GROUP BY query, the count will not always be >1... next year, it may be greater than >2.
Note #2: If set city data (e.g., Fiscal Year 19) includes a city that was NOT listed in FY18, I probably also want to output this city as well. However, maybe I should use a separate query for any cities where I don't have a 1:1 match between the fiscal year.

Thank you,
EEH

[CJ_London]

perhaps a single query like this

SELECT tblCity.City, tblFiscalYear.FiscalYear, tblCity.Code, tblFiscalYear_1.FiscalYear, tblCity_1.Code
FROM (tblFiscalYear INNER JOIN (tblCity INNER JOIN tblCity AS tblCity_1 ON tblCity.City = tblCity_1.City) ON tblFiscalYear.FYIDpk = tblCity.FYIDfk) INNER JOIN tblFiscalYear AS tblFiscalYear_1 ON tblCity_1.FYIDfk = tblFiscalYear_1.FYIDpk
WHERE (((tblCity_1.RecordIDpk)>[tblCity].[RecordIDpk]) AND ((tblCity_1.Code)<>[tblcity].Code));

not sure if this is what you put together as an example, but cities should be listed only once in tblCities. you have a many to many relationship between cities, codes and fiscal years. You should have

tblCities
CityPK
CityName

tblFiscalYears
FiscalYearPK
FiscalYear

tblCitiesFiscalYears (similar to your tblCities)
CFYPK
CityFK
FiscalYearFK
Code

[skydivetom]

Ajax:

thank you... the closing [Code] in the post is off. Currently, when I copy/paste the SQL and get the following:
SELECT tblCity.City, tblFiscalYear.FiscalYear, tblCity.Code, tblFiscalYear_1.FiscalYear, tblCity_1.Code
FROM (tblFiscalYear INNER JOIN (tblCity INNER JOIN tblCity AS tblCity_1 ON tblCity.City = tblCity_1.City) ON tblFiscalYear.FYIDpk = tblCity.FYIDfk) INNER JOIN tblFiscalYear AS tblFiscalYear_1 ON tblCity_1.FYIDfk = tblFiscalYear_1.FYIDpk
WHERE (((tblCity_1.RecordIDpk)>[tblCity].[RecordIDpk]) AND ((tblCity_1.Code)<>[tblcity].

... unfortunately, something got chopped off so the SQL does not execute right now.

Also, I'm ok removing the FY relationship and pasting the fiscal years directly into the tblCity (if this makes a difference).

That said, could you please repost the complete SQL? Attached is modified DB with FY in tblCity.
Thank you,
EEH

[skydivetom]

Ajax:

I restructed the database to follow your recommendation. Please see attachment.

What SQL would you recommend for this particular model?

Thanks,
Tom

[CJ_London]

I did correct the code. It is an issue with the way the forum interprets what has been written

based on your new structure, pretty much the same

SELECT tblCities.CityName, tblFiscalYears.FiscalYear, tblCitiesFiscalYears.Code, tblFiscalYears_1.FiscalYear, tblCitiesFiscalYears_1.Code
FROM (((tblCities INNER JOIN tblCitiesFiscalYears ON tblCities.CityPK = tblCitiesFiscalYears.CityFK) INNER JOIN tblFiscalYears ON tblCitiesFiscalYears.FiscalYearFK = tblFiscalYears.FiscalYearPk) INNER JOIN tblCitiesFiscalYears AS tblCitiesFiscalYears_1 ON tblCitiesFiscalYears.CityFK = tblCitiesFiscalYears_1.CityFK) INNER JOIN tblFiscalYears AS tblFiscalYears_1 ON tblCitiesFiscalYears_1.FiscalYearFK = tblFiscalYears_1.FiscalYearPk
WHERE tblCitiesFiscalYears_1.CFYPK>[tblCitiesFiscalYears].[CFYPK] AND tblCitiesFiscalYears_1.Code<>[tblCitiesFiscalYears].code

edit

The below are might be's - your question is what I would call 'short term' - i.e. find a solution to the immediate problem. I don't know the background to your requirement but if you need a longer term solution, you need to define what is actually required in the longer term.


- based on your example data - if you add another fiscal year then you may need to change the query otherwise you will be comparing FY18 with FY20 as well as FY19. What that solution is depends on what your actual long term requirement is. So could be solved for example just by adding an additional criteria e.g.

... and tblFiscalYears.FiscalYear='FY18'

There is also an assumption that you enter data in the correct order, so if you decided to enter FY17 data now, this part of the query could give the wrong result

tblCitiesFiscalYears_1.CFYPK>[tblCitiesFiscalYears].[CFYPK]

so it might be better to use instead

tblFiscalYears_1.FiscalYear>tblFiscalYears.FiscalY ear

Hopefully that makes sense!

[skydivetom]

Ajax:

thank for the additional information/assistance.

I've plugged your SQL into v02 (attached). It outputs three records.

Now, in the last ~hour or so, I added three queries to v03. I also added your SQL. Version 3 includes now five (5) fiscal years and I made arbitrary changes in the [Code].

Your output (17 records) is different that my output (15 records). I believe 15 records is the accurate count but this query does not compare one FY against another. Trying to figure out the best way forward.
At this time, I'm merely exploring options to determine **deltas** ("which codes", "which cities", and "when").

Please note that in my actual database, [Cities] = Entities (Organizations) and [Codes] = Systems that an organization should have. Ultimately, I using "cities" paired with a single code field was easier to explain in this post.

Again, if you have any additional thoughts on the query, I'd appreciate the feedback.

Cheers,
Tom

[CJ_London]

did you read my additional comments?

you asked for one thing, now you have an 'ah yes but what I actual want is..'

you need to think through what your long term requirement is