Query to Compare Data (1 Viewer)

MattioMatt

Registered User.
Local time
Today, 05:29
Joined
Apr 25, 2017
Messages
99
Hi all,

I have a table called tblAppInventory to which I upload data from Excel spreadsheets at the end of each month. The columns in my table and sample data are as follows:

ID | AppID | AppName | AppDeveloper | CodeTestingStatus | NumbOfVuln | DataDate

1 1004 AppOne Internal Passed 0 31/03/2018
2 1005 AppTwo Internal Failed 5 31/03/2018
3 1004 AppOne Internal Failed 2 30/04/2018
4 1005 AppTwo Internal Failed 5 30/04/2018

What I'd like to do is create a query where I can compare data differences between two dates so I can see where any data could have changed between the set of records.

For example, I'd like a query to show me that ID 3 / AppID 1004 has changed as the code status has changed from passed to failed and the NumOfVuln has changed from 0 to 2.

I'm struggling to understand how to do this and I'm wondering if you can offer some advice on how to do this?

Thanks in advance!
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
Create a query joining the table to the linked spreadsheet using a unique key field.
Add each of the fields you are interested in to the query from both spreadsheet and table.
Then add criteria for the first spreadsheet field to be not equal to the field value in the table.
Repeat for each of the other fields but put each field filter criteria on a new line so it's creating OR filters
 
Last edited:

bastanu

AWF VIP
Local time
Yesterday, 22:29
Joined
Apr 13, 2010
Messages
1,401
I would create one field in which I'd concatenate all the fields I'm interested in checking and I compare (<>) this against a similar field from the other table or spreadsheet.

Cheers,
Vlad
 

MattioMatt

Registered User.
Local time
Today, 05:29
Joined
Apr 25, 2017
Messages
99
Both,

Thanks so much for your help! I've understood what you've both recommended. The area where I'm struggling is I don't have a linked table or linked spreadsheet. Each month I upload the data to a single table. Therefore I'm looking to a comparison within the same table.

I've tried adding the table twice to the query and then using:

[tblAppInventory].[AppName]<>[tblAppInventory_1].[AppName] I've also tried just <>[tblAppInventory].[AppName].

But that doesn't seem to be working.

I need to be able to filter for a date and then do the comparison that way from the single table. Is the above still applicable? If so any steer on how I can achieve this?
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
I don't see how you can do the comparison in the import table alone

If I understand you correctly, you need to compare data from the import table based on the spreadsheet with the final destination table.

Please adapt your query and,if still having problems, post the sql for the entire query including the where clause for a selected date
 

MattioMatt

Registered User.
Local time
Today, 05:29
Joined
Apr 25, 2017
Messages
99
I don't have an import table as an interim of getting to the final destination table.
The table I want to do the comparison on is the final destination table. It's simply using Access tools to import the data from a spreadsheet.
 

isladogs

MVP / VIP
Local time
Today, 05:29
Joined
Jan 14, 2017
Messages
18,186
I don't have an import table as an interim of getting to the final destination table.
The table I want to do the comparison on is the final destination table. It's simply using Access tools to import the data from a spreadsheet.

Oh I see. In the original post it sounded like you wanted to check for differences before importing from Excel.
So are you overwriting the same Access table each month or importing to a new table each time? If the latter, then what happens to the old table?
 

bastanu

AWF VIP
Local time
Yesterday, 22:29
Joined
Apr 13, 2010
Messages
1,401
Have a look at the attached file and let us know if that is what you are looking for.

Cheers,
Vlad
 

Attachments

  • TableCompare.zip
    19.4 KB · Views: 55

jdraw

Super Moderator
Staff member
Local time
Today, 01:29
Joined
Jan 23, 2006
Messages
15,364
Vlad,

Seems to work as requested.
I do have a question re your SQL: Why are you using !(bang) in SQL?? curious --it works with . or !

Code:
SELECT Table1.AppID
	,Table1.CodeTestingStatus
	,Table1.NumbOfVUln
	,Table1.DataDate
FROM Table1
INNER JOIN Table1 AS Table1_1 ON Table1.AppID = Table1_1.AppID
WHERE (
		((Table1.CodeTestingStatus) <> [table1_1] ! [CodeTestingStatus])
		AND ((Table1.DataDate) <> [table1_1] ! [DataDate])
		)
	OR (
		((Table1.NumbOfVUln) <> [table1_1] ! [NumbOfVUln])
		AND ((Table1.DataDate) <> [table1_1] ! [DataDate])
		);
 

MattioMatt

Registered User.
Local time
Today, 05:29
Joined
Apr 25, 2017
Messages
99
Thanks so much to all that has contributed so far!

bastanu, thanks for your upload of your example. I've used this to start building the query I need and what I've found is it's returning the all data (whether there are differences or not). I have data for each month since January as ooposed to the small example I posted in the forum.
I've managed to replicate this in your example by adding another row for AppID 1004. Why would this be happening?

Table


Query
 

Attachments

  • table1.png
    table1.png
    15.2 KB · Views: 175
  • Query1.png
    Query1.png
    11.4 KB · Views: 176

bastanu

AWF VIP
Local time
Yesterday, 22:29
Joined
Apr 13, 2010
Messages
1,401
It is simply showing you all the combinations that are different (cartesian product); open the query in design view and set its properties to show distinct values and you should see three records instead of 6.

Cheers,
Vlad
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:29
Joined
May 7, 2009
Messages
19,169
i can't get it to work in a query.

but, i was able to accomplish your goal using VBA and Temporary table (tblAppInventoryResult).

Please see the code (Open/Load) behind on the second datasheet subform.
See alsom Module1.
 

Attachments

  • appInventory.zip
    43 KB · Views: 56

static

Registered User.
Local time
Today, 05:29
Joined
Nov 2, 2015
Messages
823
Code:
select a.*,
	b.CodeTestingStatus as newstatus,
	b.DataDate as newdate
from tblAppInventory a 
	inner join (
		select 
			CodeTestingStatus,
			DataDate,
			appid
		from tblAppInventory
		order by DataDate asc) b 
	on a.AppID = b.AppID and b.DataDate > a.DataDate and a.CodeTestingStatus <> b.CodeTestingStatus
 

MattioMatt

Registered User.
Local time
Today, 05:29
Joined
Apr 25, 2017
Messages
99
Hi all,

Following the posts using two queries and then comparing for differences that way the problem is solved.

Thanks to all that has been involved in helping with this one!
 

Users who are viewing this thread

Top Bottom