Looking to SQL to see if it can streamline a cumbersome Excel task

There is a bimonthly task that I do, comparing ~ 4000 rows of data in Excel between the 'Start' and 'End' of a production cycle to determine the changes that occurred. Currently I'm using index-match in a rather cumbersome process in Excel to get my results. I am hoping there is a much faster way of doing this task, and to that end am posting my question in the Access forum in case SQL is an option.

Description:
I'm looking for a (?SQL query) based on two tables (EE_Start_Final and EE_End_Final tables) that extracts a list of all Chart #'s from both tables that meet the following criteria:
[Chart #] in Start table <> [Chart #] in End table
OR
[Chart #] in End table <> [Chart #] in Start table
OR
[Chart #]&[Type] in Start table <> [Chart #]&[Type] in End table
OR
[Chart #]&[BookA_#] in Start table <>[Chart #]&[BookA_#] in End table
OR
[Chart #]&[BookB_#] in Start table <>[Chart #]&[BookB_#] in End table
OR
[Chart #]&[BookC] in Start table <>[Chart #]&[BookC] in End table

The resulting Query table extracted based on the above criteria to include, in following order:
LIst of (unique)[Chart #]'s
[Type] from Start table
[Type] from End table
[BookA_#] from Start table
[BookA_#] from End table
[BookB_#] from Start table
[BookB_#] from End table
[BookC] from Start table
[BookC] from End table

I have attached a sample database with the Start and End tables as described. I did try a union join in SQL, but wasn't able to proceed any further and wasn't sure if that was the direction to take.

Please let me know if you have any questions. Also, if you recommend using another approach or software instead of Access, or if you think Excel can do the job better, please include your recommendations. Excel is the most readily available tool in my environment at the moment, which is why I've been it for solutions but if MS Access can do the job better, then that software can be made available to me.

Thanks!
Andrea
Cycle_Start_End_Compare.accdb
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Given those two sets of data, what is your desired output?

Combinations like this:

[Chart #] in Start table <> [Chart #] in End table
OR
[Chart #] in End table <> [Chart #] in Start table

Are called a full outer join in database terminology.  They are implemented by creating a left join query and a right join query and then unioning the two.

You should also reconsider your naming standard since you are moving to a relational database.  Object names should use only the upper and lower case letters, numbers, and the underscore.  I prefer CamelCase which capitalizes the first letter of each word over the_underscore which separates words with the underscore.  NEVER use embedded spaces or special characters such as the #.  Also do not use Access, VBA, or SQL function or property names such as Name, Month, Year since they can cause subtle errors or conflicts.  For example, is Me.Name the name of your form or is it a control named "Name" on your form?
0
nishant joshiTechnology Development ConsultantCommented:
This expert suggested creating a Gigs project.
Hi,

Below will be your SQL query based on information provided:

SELECT DISTINCT
	s.[Chart #],
	e.[Chart #],
	s.[Type],
	e.[Type],
	s.[BookA_#],
	e.[BookA_#],
	s.[BookB_#],
	e.[BookB_#],
	s.[BookC],
	e.[BookC]
FROM EE_Start_Final s 
INNER JOIN EE_End_Final e ON
	s.[Chart #] <> e.[Chart #] OR
	s.[Chart #] + s.[Type] <> e.[Chart #] + e.[Type] OR
	s.[Chart #] + s.[BookA_#] <> e.[Chart #] + e.[BookA_#] OR
	s.[Chart #] + s.[BookB_#] <> e.[Chart #]+ e.[BookB_#] OR
	s.[Chart #] + s.[BookC] <> e.[Chart #] + e.[BookC] 

Open in new window

0
ste5anSenior DeveloperCommented:
E.g.

SELECT *
FROM EE_Start AS S 
  INNER JOIN EE_End AS E ON S.UniqueChart = E.UniqueChart
WHERE (((S.[Chart #])<>[E].[Chart #])) 
  OR (((S.Type)<>[ E].[Type])) 
  OR (((S.[Book_A_#])<>[E].[Book_A_#])) 
  OR (((S.[Book_B_#])<>[E].[Book_B_#])) 
  OR (((S.Book_C)<>[E].[Book_C]));

Open in new window


You don't need to test for the concatenated values with [Chart #], cause you test it already on its own.

The onle question is: can rows be added or deleted? Then you need a FULL JOIN.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AndreamaryAuthor Commented:
Thanks for the quick responses, which I am going through now and trying them out...

Ste5an, rows can be added and deleted, so it sounds like I need a FULL JOIN. How would I modify the code for that? Also, when I ran the query I got a dialog box saying 'Enter parameter value' for 'E.Type'...how do I rectify that.

Thanks,
Andrea
0
ste5anSenior DeveloperCommented:
The dialog box comes from a typo. It must be [E].[Type] not [ E].Type:

SELECT *
FROM EE_Start AS S 
  INNER JOIN EE_End AS E ON S.UniqueChart = E.UniqueChart
WHERE (((S.[Chart #])<>[E].[Chart #])) 
  OR (((S.Type)<>[E].[Type])) 
  OR (((S.[Book_A_#])<>[E].[Book_A_#])) 
  OR (((S.[Book_B_#])<>[E].[Book_B_#])) 
  OR (((S.Book_C)<>[E].[Book_C]));

Open in new window


As already posted, you need to simulate it:

SELECT 'C' AS ChangeType, *
FROM EE_Start AS S
INNER JOIN EE_End AS E ON S.UniqueChart = E.UniqueChart
WHERE
 (((S.[Chart #])<>[E].[Chart #])) OR (((S.Type)<>[E].[Type])) OR (((S.[Book_A_#])<>[E].[Book_A_#])) OR (((S.[Book_B_#])<>[E].[Book_B_#])) OR (((S.Book_C)<>[E].[Book_C]))
UNION ALL
SELECT 'D' AS ChangeType, *
FROM EE_Start AS S
LEFT JOIN EE_End AS E ON S.UniqueChart = E.UniqueChart
WHERE E.UniqueChart IS NULL
UNION ALL
SELECT 'A' AS ChangeType, *
FROM EE_Start AS S
RIGHT JOIN EE_End AS E ON S.UniqueChart = E.UniqueChart
WHERE S.UniqueChart IS NULL

Open in new window

0
PatHartmanCommented:
@Andreamary
None of the suggested queries returns the set of data I think you need and they all have typos.  Once we see what you want returned, that will clarify what the query needs to do.

PS - although the + works as a concatenation operator under some conditions, it works differently than the & which is the standard Access query concatenation operator.  So, unless you want the specific property that + provides, always use the &.

A + B = sum of a + b when both are numeric
A + B = AB when one or both are strings
A + B = Null when one or both values are null

A & B = always = AB  even when both are numeric or one of them is null.
0
AndreamaryAuthor Commented:
Hi all,

Pat, thank you for your guidance on Access. As you can probably tell, I have very little recent experience in it, and none in SQL. To clarify the results I am after, I have attached the expected results (included the column order I am hoping for) of this query in an Excel spreadsheet (since this is the current and only way I know how to do this task).

Nishant, I tried your solution but received many errors unfortunately.

Ste5an, thanks for your updated solution. In terms of the charts listed, they almost matched the results I was expecting. The one chart that was missing — City 1-Chart4-E — should be listed due to changes in the book section of the comparison, in Book B and in Book C. This is captured in my results as attached. Is it possible for the code to be revised so this change is captured? Also, is it possible for the results to be formatted with the same columns, in the same order, as my attached results? Not being savvy in Access myself, I'm not sure if what I am asking is possible or realistic.

Thanks again for your contributions...

Andrea
Start_End_Changes_Results.xlsx
0
ste5anSenior DeveloperCommented:
The problem is, that there is a NULL instead of a empty string. In databases these are two different things. Even when they look the same in the grid view.

Either

SELECT 'C' AS ChangeType ,
       *
FROM   EE_Start S
       INNER JOIN EE_End E ON S.UniqueChart = E.UniqueChart
WHERE     Nz(S.[Chart #] , '') <> Nz(E.[Chart #] , '')
       OR Nz(S.[Type]    , '') <> Nz(E.[Type]    , '')
       OR Nz(S.[Book_A_#], '') <> Nz(E.[Book_A_#], '')
       OR Nz(S.[Book_B_#], '') <> Nz(E.[Book_B_#], '')
       OR Nz(S.[Book_C]  , '') <> Nz(E.[Book_C]  , '')
UNION ALL
SELECT 'D' AS ChangeType ,
       *
FROM   EE_Start S
       LEFT JOIN EE_End E ON S.UniqueChart = E.UniqueChart
WHERE  E.UniqueChart IS NULL
UNION ALL
SELECT 'A' AS ChangeType ,
       *
FROM   EE_Start S
       RIGHT JOIN EE_End E ON S.UniqueChart = E.UniqueChart
WHERE  S.UniqueChart IS NULL;

Open in new window

or

SELECT 'C' AS ChangeType ,
       *
FROM   EE_Start S
       INNER JOIN EE_End E ON S.UniqueChart = E.UniqueChart
WHERE     S.[Chart #]  & '' <> E.[Chart #]  & ''
       OR S.[Type]     & '' <> E.[Type]     & ''
       OR S.[Book_A_#] & '' <> E.[Book_A_#] & ''
       OR S.[Book_B_#] & '' <> E.[Book_B_#] & ''
       OR S.[Book_C]   & '' <> E.[Book_C]   & ''
UNION ALL
SELECT 'D' AS ChangeType ,
       *
FROM   EE_Start S
       LEFT JOIN EE_End E ON S.UniqueChart = E.UniqueChart
WHERE  E.UniqueChart IS NULL
UNION ALL
SELECT 'A' AS ChangeType ,
       *
FROM   EE_Start S
       RIGHT JOIN EE_End E ON S.UniqueChart = E.UniqueChart
WHERE  S.UniqueChart IS NULL;

Open in new window


Cause SQL has a tri-value logic with NULL and comparison with NULL is special: 'test' <> NULL evaluates to UNKOWN, not to TRUE. Such test require the special IS NULL or IS NOT NULL test predicates.

Or we use Nz(valu1, value2). If value1 is NULL, then the second value is returned.

Or we use the specialty of the string concatenation of NULL & '' which becomes '' (empty string).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Thanks so much for your solution (and explanation)...much appreciated, Ste5an! Using this approach in Access will save me a lot of time. :-)

Cheers,
Andrea
0
PatHartmanCommented:
You can colorize the differences on a report or form by using Conditional Formatting.  Please start a new thread if you need help with that.
0
AndreamaryAuthor Commented:
Thanks, Pat,, for that information....

Cheers,
Andrea
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.