• Advertisement
Sign in to follow this  

MS Access question

This topic is 1410 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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]);

Share this post


Link to post
Share on other sites
Advertisement

It's been a while since I've done a query in 2010. However, I think I remember there being quirks with comparing memo fields. Don't know if your medical review and/or comments fits in that category. If that's where you're having differences, then you may have to read the memo field into another format (I can't remember what I used, sorry) and do a comparison there.

Share this post


Link to post
Share on other sites

Yes comments is a memo.  How do I change its format in a query?  Thanks for the advice non-the-less, it'll give me something more specific to google against than just 'OMG ACCESS NO DOES WORK!!!' ; )

Share this post


Link to post
Share on other sites

Yes comments is a memo.  How do I change its format in a query?  Thanks for the advice non-the-less, it'll give me something more specific to google against than just 'OMG ACCESS NO DOES WORK!!!' ; )

laugh.png  Been there.

 

To the best of my knowledge, you can't change field types in a query. That's the problem. However, if the comment field is the only difference, hopefully it's just a matter of looking at the field in both tables. At the time I had the problem, I was big into Access Basic so I wrote a comparison routine and dumped bad comparisons to a text file or something. Sounds like that's probably not a good option for you.

 

I don't have suggestions beyond that, Ryan. sad.png

Share this post


Link to post
Share on other sites

I figured it out.  Some of my fields were 'null' and it seems null != null....

 

Just a slight clarification in case there is any real bewilderment: null in databases marks a missing/unknown/non-applicable value - hence null can never match with another null as we don't have anything tangible to compare there at the given time. Null is a touchy subject as far as databases go, but my rule-of-thumb is that no field should ever allow null without a bloody good reason.

Edited by tanzanite7

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement