Jump to content

  • Log In with Google      Sign In   
  • Create Account


MS Access question


Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.

  • You cannot reply to this topic
6 replies to this topic

#1 Ryan_001   Prime Members   -  Reputation: 1345

Like
0Likes
Like

Posted 14 March 2014 - 02:34 PM

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


Sponsor:

#2 Buckeye   Crossbones+   -  Reputation: 4413

Like
2Likes
Like

Posted 14 March 2014 - 02:47 PM

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.


Please don't PM me with questions. Post them in the forums for everyone's benefit, and I can embarrass myself publicly.


#3 Ryan_001   Prime Members   -  Reputation: 1345

Like
0Likes
Like

Posted 14 March 2014 - 03:00 PM

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!!!' ; )



#4 Buckeye   Crossbones+   -  Reputation: 4413

Like
1Likes
Like

Posted 14 March 2014 - 03:16 PM

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


Please don't PM me with questions. Post them in the forums for everyone's benefit, and I can embarrass myself publicly.


#5 Ryan_001   Prime Members   -  Reputation: 1345

Like
2Likes
Like

Posted 14 March 2014 - 03:40 PM

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



#6 Buckeye   Crossbones+   -  Reputation: 4413

Like
1Likes
Like

Posted 14 March 2014 - 05:44 PM

Thanks for posting the resolution.


Please don't PM me with questions. Post them in the forums for everyone's benefit, and I can embarrass myself publicly.


#7 tanzanite7   Members   -  Reputation: 1223

Like
0Likes
Like

Posted 15 March 2014 - 09:55 AM

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, 15 March 2014 - 09:57 AM.





Old topic!
Guest, the last post of this topic is over 60 days old and at this point you may not reply in this topic. If you wish to continue this conversation start a new topic.



PARTNERS