I'm not sure if this is the right spot for this question, but the Access forums I've found online have been pretty poor, and you guys seem to be a pretty knowledgeable bunch, so I thought I'd ask here : )
I'm helping a family member with a database that's already been made in Access 2010. I'm attempting to add data to TableA from another TableB, but I need to be able to show a 'Are you sure you wish to save changes' type dialog. So I need to know if there are any differences between TableA and TableB. The approach I was taking was to compare Count(TableA), Count(TableB), Count(TableA intersect TableB). If all 3 counts were equal then no update has to be made, otherwise I have to perform an update. I tried a bunch of different queries with varying success rates.
The main problem I have bumped into is that in some circumstances identical rows do not compare equal. I've taken a table and copied/pasted it with nothing changed and then performed the intersection on the 2 seemingly identical tables, and some (but not all) of the rows do not compare equal to their counterpart. I've inspected the tables visually, and as far as I can see nothing that was changed on the copy/paste. Now I didn't just copy the rows, but I copied/pasted the full tables in design mode, structure/layout and data, to ensure that they were identical.
So this has left me rather stumped. I'm admittedly an access 'noob' (the majority of my programming experiencing being with in C/C++), so perhaps I'm missing something. Chances are this is not the 'optimal' why to do things, and by all means if you know of alternate methods I'm all ears, but that said I am helping a family member (ie. this is not my project) and hence they might not be conducent to large changes.
My rather noob-ish attempt at an intersection SQL query is here:
SELECT a.* FROM TableA AS a, TableB AS b WHERE ( a.[Staff Member]=b.[Staff Member] AND a.[Day ID]=b.[Day ID] AND a.[Shift Date]=b.[Shift Date] AND a.[Start Time]=b.[Start Time] AND a.[End Time]=b.[End Time] AND a.[Duration]=b.[Duration] AND a.[Position]=b.[Position] AND a.[Shift Status]=b.[Shift Status] AND a.[Wage]=b.[Wage] AND a.[Medical Review]=b.[Medical Review] AND a.[Comments]=b.[Comments]);