By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Post only 1 instance of a field in an Unmatched Query

P: 23
Hi all,

I created an unmatch query but some rows have multiple fields that do not match. Therefore access shows the same row in the query results multiple times since there are more than 1 field that do not match.

Is there a way that access only shows 1 instance of a row no matter how many differences there are in it. It will make creating a report much easier.
Oct 16 '06 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 14,534
Why are you returning the fields that do not match? You have to decide in this case if you want the max, min, sum, first, etc. value in that field and group by the fields that do match. If you post your sql code I will have a look at it.
Oct 16 '06 #2

P: 23
I am trying to find data that does not match so that I can compare the current quarter with the previos quarter (These are the 2 tables that I am running the query from). I want a query that will show which project numbers had changes but if there were multiple changes in 1 project to show it only once in the query not the number of times a change was found.

Here is the SQL query

SELECT [ADT Calculated Amort Schedule (Next Quarter)].DCN AS Expr1, [ADT Calculated Amort Schedule (Next Quarter)].CostBas AS [New Cost Basis], [ADT Calculated Amort Schedule].CostBas AS [Old Cost Basis], [ADT Calculated Amort Schedule (Next Quarter)].[Start Amort Date] AS [New Start Amort Date], [ADT Calculated Amort Schedule].[Start Amort Date] AS [Old Start Amort Date], [ADT Calculated Amort Schedule (Next Quarter)].UseLife AS [New Ueful Life], [ADT Calculated Amort Schedule].UseLife AS [Old Useful Life]
FROM [ADT Calculated Amort Schedule (Next Quarter)] INNER JOIN [ADT Calculated Amort Schedule] ON [ADT Calculated Amort Schedule (Next Quarter)].DCN = [ADT Calculated Amort Schedule].DCN
WHERE ((([ADT Calculated Amort Schedule (Next Quarter)].CostBas)<>[ADT Calculated Amort Schedule].[costbas])) OR ((([ADT Calculated Amort Schedule (Next Quarter)].[Start Amort Date])<>[ADT Calculated Amort Schedule].[start amort date])) OR ((([ADT Calculated Amort Schedule (Next Quarter)].UseLife)<>[ADT Calculated Amort Schedule].[UseLife]));
Oct 17 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.