Query two records on same table, and output based on results (1 Viewer)

[NBRJ]

(This is related to my Are the relationships between my tables *totally* wrong? thread in the Tables forum if anyone wants more background - sorry I can't post links until I hit 10 posts and don't want to spam just to get to that point).

Relevent to the problem below is the attached ERD, which is of part of the small database I'm designing at the moment, and I'm mostly there with the tables/relationships. However, it needs one big bit of functionality that I can't figure out how to do. I'm assuming it's done via a query, but please put me right if not.

I've signed up on Lynda.com, and I'm working my way through DB-related tutorials in an attempt to not need to ask such questions, but I'm now thinking I can't do what I need to and I'm panicking a bit. I'm not sure CASE will do it.

Small background: This database is for an educational establishment and is tracking software requests for each location (room) as well as the state of the software ready for deployment.

The fields I think are pertinent to the problem:

• SWTitle.Developer (Microsoft, Microsoft)
• SWTitle.Title (Word, Word)
• SWVersion.Version (2013, 2016)
• SWVersion.Platform (Windows, Windows)
• Installs.FIDLocation (Building1Room1, Building1Room1)
• Installs.InstallationYear (2015, 2016)

The tables are:

• Location (parent table Building)
• Installs
• SWVersion (parent table SWTitle)

Installs solves the M:M of Location and SWVersion. Installs specifies the software item, the location, and the installation year (and a few other things).

I need to be able to MATCH the following:

• SWTitle.Developer
• SWTitle.Title
• SWVersion.Platform
• Installs.FIDLocation

I need to then for those records that match above, inspect the record's InstallationYear (i.e 2016) with the previous installation year (i.e. 2015)

• Installs.InstallationYear

I then need to compare for these 2 entries (and there will only be two, one for each year - On that thought I could even do a composite key of location, title, version, year I guess) via:

• SWVersion.Version

I then need to output to a field for that entry (i.e. maybe called Installs.ChangeState) from the previous year, depending on results.

If SWVersion's are = then 'no change'
If the SWVersions are <> then 'update required'
If there is no previous version for the year (no record) then 'new to location'

So in the 2016 entry it will be 'update required'. From which a location list with associated sw can be pulled and filtered by the work that's needed (i,e update or new).

Is this possible? It has to be or they'll be using spreadsheets again this year.

Sorry it's so long, I wanted it to be clear what I was trying to do. Please be miracle workers

 

[plog]

Can you post sample data to demonstrate what you want? Please provide 2 sets:

A. Starting sample data from relevant tables/fields. Include table/field names and enough sample data to cover all cases.

B. Expected results. Based on A, show what you expect the query to return.

 

[NBRJ]

Example tables below.

tblBuilding

[B]IDBuild  Abb    Full[/B]
1        B1     Building1
2        B2     Building2

tblLocation

[B]IDLoc    FIDBuild    Room[/B]
1        1 (B1)      1.1
2        1 (B1)      6.2
3        2 (B2)      4.3

tblSWTitle

[B]IDSWT    Dev        Suite     Title[/B]
1        Microsoft  Office    Access
2        Microsoft  Office    Excel
3        Microsoft  Office    PowerPoint
4        Microsoft  Office    Word
5        Mozilla              Firefox

tblSWVersion

[B]IDSWV    FIDSWT        Version  Platform[/B]
1        1 (Access)    2013     Win
2        1 (Access)    2016     Win
3        2 (Excel)     2013     Win
4        2 (Excel)     2016     Win
5        2 (Excel)     2011     Mac
6        2 (Excel)     2016     Mac
7        5 (Firefox)   40       Win
8        5 (Firefox)   44       Win

tblInstalls - where the records will compare.

[B]IDInstalls   FIDLoc    FIDSWV              InstallYr  [COLOR=Blue]WorkRequired[/COLOR][/B]
1            1 (B1)    1 (Access 2013 W)   2015[COLOR=Gray]       New install [/COLOR]        
2            1 (B1)    2 (Access 2016 W)   2016       [COLOR=Blue]Update    [/COLOR]
3            1 (B1)    3 (Excel 2013 W)    2015[COLOR=Gray]       New install [/COLOR]        
4            1 (B1)    4 (Excel 2013 W)    2016       [COLOR=Blue]Update    [/COLOR]
5            2 (B2)    5 (Excel 2011 M)    2015[COLOR=Gray]       New install   [/COLOR]     
6            2 (B2)    5 (Excel 2011 M)    2016       [COLOR=Blue]No Change[/COLOR]    
7            1 (B1)    8 (Firefox 44 W)    2016       [COLOR=Blue]New install[/COLOR]

Obviously the 2015 entries would also have New install, as they have no previous years to compare them too, but we'll be filtering those out from the reports.

The bracketed text above is for clarity for the foreign keys.

These must match:

• SWT.Dev
• SWT.Title
• SWV.Platform
• Installs.FIDLoc

This must compare to an entry for the previous year

• Installs.InstallationYr (i.e. 2016 to 2015)

If there is no entry, then WorkRequired 'New install'.

Compare these field SWV.Version in the two records

• If = then WorkRequired 'No change'
• If <> then WorkRequired 'Update'

Example1
In tblInstalls Record #1 will compare to Record #2 because Dev, Title, Platform and Location all match.
Record 2 will compare the previous year's record (InstallationYr).
The versions will compare and find a difference resulting in the WorkRequired field in the 2016 (2) entry to change to 'Update'

Example2
In tblInstalls Record #5 will compare to Record #6 because Dev, Title, Platform and Location all match.
Record 6 will compare the previous year's record (InstallationYr).
The versions will compare and find NO difference resulting in the WorkRequired field in the 2016 entry (6) to change to 'No change'

Example3
In tblInstalls Record #7 will not to be able to find a record that matches Dev, Title, Platform and Location.
WorkRequired field in the 2016 (7) entry to change to 'New install'

I hope this all makes sense. I hope the CODE format doesn't go all wonky and cause confusion.

 

[plog]

Usually its the words and superfluous data that cause confusion. As is this case.

1. Looking at what you posted, the column [WorkRequired] in your last dataset is what you want the query to generate, correct?

2. Looking at your data, the first 4 tables are not necessary in determining what you want, correct?

3. [WorkRequired] should be able to be deduced simply by all the data already in tblInstalls, right?

4. The values you have in parenthesis are not actually in tblInstalls, correct? '1 (B1)' a value in FIDLoc, its just 1, right?

Lastly, fight any urge you have to go into depth about my questions. Yes/No's will suffice.

 

[NBRJ]

I forgot to apologise, I shoud've known to post all that in the first post!

I had hoped that a Eureka moment would come to me while typing all that. It did with a post I was typing yesterday (so I didn't post). Disappointed at brain and google search failure. I just had a meeting and did try to get out of the WorkRequired field, but no, they need it

I have a few more fields in the actual database. For example in SWV, one of which is a ReadyForDeployment checkbox, which would surely give an indication of work needed or not on a SWV item. So when pulling of the each location SW list for a year (for Task Sequence creation) if it's not ReadyForDeployment work is needed. But they want to highlight the changes so they don't have to go through manually each item for each room's task sequence and check it matches with what the database says or if a change is needed. I can understand, this DB will have 1000s of tblInstalls records.

 

[NBRJ]

1. Looking at what you posted, the column [WorkRequired] in your last dataset is what you want the query to generate, correct?

Correct.

2. Looking at your data, the first 4 tables are not necessary in determining what you want, correct?

But now else will it know to match, for example, tblInstalls Record 1 to Record 2 by not including these tables?

3. [WorkRequired] should be able to be deduced simply by all the data already in tblInstalls, right?

I'm confused at how you do that (I suspect I'm going to feel really stupid when you tell me).

4. The values you have in parenthesis are not actually in tblInstalls, correct? '1 (B1)' a value in FIDLoc, its just 1, right?

Yes. The parenthesis are showing the parent table data.

Lastly, fight any urge you have to go into depth about my questions. Yes/No's will suffice.

You bring to mind many red pen marks all over exam papers as a youth. Is this the forum way of telling me not to waffle. I'll try....

 

[plog]

I see it now, a few of the other tables are necessary (tblSWTitle & tblSWVersion). As for the brevity request, I always ask for sample data and everyone posts back 8 paragraphs walking me thru things, but never the data I ask for. You did a really good job, well, at least providing the data I requested; you did post half a page text on the why's and whats.

Everyone thinks their words shine like a spotlight onto the problem, but it just adds confusion. The data is what I need and now I see how to get there. Give me an hour or so.

 

[plog]

One more question. Let's add 2 new records to tblInstalls and you tell me what WorkRequired should be:

IDInstalls   FIDLoc    FIDSWV              InstallYr  WorkRequired
1            1 (B1)    1 (Access 2013 W)   2015       New install         
2            1 (B1)    2 (Access 2016 W)   2016       Update    
3            1 (B1)    3 (Excel 2013 W)    2015       New install         
4            1 (B1)    4 (Excel 2013 W)    2016       Update    
5            2 (B2)    5 (Excel 2011 M)    2015       New install        
6            2 (B2)    5 (Excel 2011 M)    2016       No Change    
7            1 (B1)    8 (Firefox 44 W)    2016       New install
8            3           1 (Access 2013 W)   2015       New install         
9            3           2 (Access 2016 W)   2017

#8 is new install, but what about 9? There's a year gap in there. Do I only look 1 year back and forward, or do I look forward and back no matter how long that is?

 

[plog]

In my code below, I assumed in my example above that #9 should be "Update". To accomplish what you want is going to take 3 query objects (which themselves have subqueries). Below is the code for the first one:

SELECT tblInstalls.IDInstalls, tblInstalls.FIDLoc, tblInstalls.InstallYr, tblSWVersion.FIDSWT, tblSWVersion.Version
FROM tblInstalls INNER JOIN tblSWVersion ON tblInstalls.FIDSWV = tblSWVersion.IDSWV;

Paste the above code into a query and name it 'Work_sub1'. It just puts the relevant tblSWVersion data in with the rest of the pertinent data. This will allow us to accurately look backward and forward.

SELECT Sub1.IDInstalls, (SELECT Min(IDInstalls) FROM Work_sub1 WHERE Work_sub1.FIDLoc=Sub1.FIDLoc AND Work_sub1.FIDSWT=Sub1.FIDSWT AND Work_sub1.IDInstalls>Sub1.IDINstalls) AS NextRecord, (SELECT Max(IDInstalls) FROM Work_sub1 WHERE Work_sub1.FIDLoc=Sub1.FIDLoc AND Work_sub1.FIDSWT=Sub1.FIDSWT AND Work_sub1.IDInstalls<Sub1.IDINstalls) AS PriorRecord, Sub1.Version
FROM Work_sub1 AS Sub1;

Paste the above code into a query and name it 'Work_sub2'. It lines up each record in tblInstalls with its prior and next records so that you can compare them and see the WorkRequired status. Finally, the below query will give you what you want:

SELECT Work_sub2.IDInstalls, IIf(IsNull([PriorRecord]),"New Install",IIf([Work_sub1].[Version]=[Work_sub2].[Version],"No Change","Update")) AS WorkRequired
FROM Work_sub2 LEFT JOIN Work_sub1 ON Work_sub2.PriorRecord = Work_sub1.IDInstalls;

Let me know if it doesn't work correctly. If so, post sample data that makes it fail.

 

[NBRJ]

One more question. Let's add 2 new records to tblInstalls and you tell me what WorkRequired should be:

IDInstalls   FIDLoc    FIDSWV              InstallYr  WorkRequired
1            1 (B1)    1 (Access 2013 W)   2015       New install         
2            1 (B1)    2 (Access 2016 W)   2016       Update    
3            1 (B1)    3 (Excel 2013 W)    2015       New install         
4            1 (B1)    4 (Excel 2013 W)    2016       Update    
5            2 (B2)    5 (Excel 2011 M)    2015       New install        
6            2 (B2)    5 (Excel 2011 M)    2016       No Change    
7            1 (B1)    8 (Firefox 44 W)    2016       New install
8            3           1 (Access 2013 W)   2015       New install         
9            3           2 (Access 2016 W)   2017

#8 is new install, but what about 9? There's a year gap in there. Do I only look 1 year back and forward, or do I look forward and back no matter how long that is?

Only one year look back. If it's not there in 2016, it's not installed, the change for 2017 will be 'new install'.

 

[NBRJ]

In my code below, I assumed in my example above that #9 should be "Update". To accomplish what you want is going to take 3 query objects (which themselves have subqueries). Below is the code for the first one:

SELECT tblInstalls.IDInstalls, tblInstalls.FIDLoc, tblInstalls.InstallYr, tblSWVersion.FIDSWT, tblSWVersion.Version
FROM tblInstalls INNER JOIN tblSWVersion ON tblInstalls.FIDSWV = tblSWVersion.IDSWV;

Paste the above code into a query and name it 'Work_sub1'. It just puts the relevant tblSWVersion data in with the rest of the pertinent data. This will allow us to accurately look backward and forward.

SELECT Sub1.IDInstalls, (SELECT Min(IDInstalls) FROM Work_sub1 WHERE Work_sub1.FIDLoc=Sub1.FIDLoc AND Work_sub1.FIDSWT=Sub1.FIDSWT AND Work_sub1.IDInstalls>Sub1.IDINstalls) AS NextRecord, (SELECT Max(IDInstalls) FROM Work_sub1 WHERE Work_sub1.FIDLoc=Sub1.FIDLoc AND Work_sub1.FIDSWT=Sub1.FIDSWT AND Work_sub1.IDInstalls<Sub1.IDINstalls) AS PriorRecord, Sub1.Version
FROM Work_sub1 AS Sub1;

Paste the above code into a query and name it 'Work_sub2'. It lines up each record in tblInstalls with its prior and next records so that you can compare them and see the WorkRequired status. Finally, the below query will give you what you want:

SELECT Work_sub2.IDInstalls, IIf(IsNull([PriorRecord]),"New Install",IIf([Work_sub1].[Version]=[Work_sub2].[Version],"No Change","Update")) AS WorkRequired
FROM Work_sub2 LEFT JOIN Work_sub1 ON Work_sub2.PriorRecord = Work_sub1.IDInstalls;

Let me know if it doesn't work correctly. If so, post sample data that makes it fail.

Plog, thank you!!! I will check this out tomorrow and report back (although I'm acting as a Team Leader tomorrow due to a short straw scenario, so actual work on DB may not happen until Sat). I'll pick it to pieces to so I understand what's going on too I feel a thank you isn't going to adequately express my joy (and relief) that this IS possible!

 

[NBRJ]

Let me know if it doesn't work correctly. If so, post sample data that makes it fail.

This works PERFECTLY!!!!! Plog, you are a genius! GENIUS! I'm now going to spend time picking it apart so I can fully understand the logic. My main problem was that I wasn't thinking about it from pulling out data into subqueries, but trying to do it all in just one.

If anyone else is reading this, and feel in the mood to thank him, please do, because my one thank you isn't enough.