Have an Access problem

Post #107,538 by boxley 6/27/03 11:40:35 AM Reply	Have an Access problem 2 tables that have a field called Serial_Number that is defined as a text field indexed with duplicates allowed. I try to run a query that has a relationship between the tables based on this field. The Query returns no data. If I use the find command on individual serial numbers I find matches. here is the sql statement SELECT hsinventory.[Serial Number], tbl_Matrix_MASTER.Serial_Number FROM tbl_Matrix_MASTER INNER JOIN hsinventory ON tbl_Matrix_MASTER.Serial_Number = hsinventory.[Serial Number]; thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,554 by tseliot 6/27/03 12:49:43 PM Reply	The SQL is correct. ..syntactically, anyway. Perhaps one of your Serial Number fields has leading/trailing spaces, nulls, tabs, or similar? Heck, maybe one's Unicode and the other isn't. :) Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,558 by tablizer 6/27/03 1:32:51 PM Reply	run toy data Perhaps one of your Serial Number fields has leading/trailing spaces, nulls, tabs, or similar? Indeed. Maybe run it with some "toy" data that you type in yourself. If that works, then it may indeed be above. If it does not work, then give Access the evil eye. Perhaps try it with a regular join just to see what happens. ________________ oop.ismad.com
Post #107,559 by boxley 6/27/03 1:33:13 PM Reply	how would I TRIM the above statement? my methods cause access to crash thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,564 by orion 6/27/03 1:51:48 PM Reply	Try this: UPDATE hsinventory SET hsinventory.[Serial Number] = Trim("" & [Serial Number]); UPDATE tbl_Matrix_master SET tbl_Matrix_master.serial_number = Trim("" & [serial_number]); It works with Access 2000. Trims out the spaces. It also avoids the null errors and other stuff by padding a blank to the text before trimming it. I created two tables named the same as yours, entered a few serial numbers into each table, had a few that matched, and the ones that matched came up in that query before and after the trim. I made sure that some columns had spaces after them. I think Access ignores the extra spaces. Any Relationships you have set on the two tables? I can't think of anything else that could hose it up right now. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,565 by boxley 6/27/03 2:18:14 PM Reply	your update ran OK, thanks Norm but same problem so I will assume the relationship is in error. There is a newbie(to us) called Diane who will take problem over, She unfortunately is a HP nix admin who knows as much as I do about crapcess so hopefully she can figure it out. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,590 by orion 6/27/03 5:18:27 PM Reply	Access relationships Under the tools menu is the Relationships command, which shows the relationships of the tables. See what fields are linked between those two tables. This is not hard to do, and I know you can do it. So far the only two fields I know about are serial number fields. There could be others. Like for example, what is the Primary key to the table? There may be a relationship set up between the primary keys of the two tables that is shorting out your query. One thing you could do, is make a new database, import those two tables to it, and then try running your query on that. It won't copy the relationships. Something else I thought of is that the database might be damaged in some way, if this is a brand new database it is unlikely, but if it has been around for a long time you may want to repair and compact the database, then see if the problem goes away. But before you do that, be sure to back it up first. Sometimes the repair and compact might remove data that is damaged, and last thing you need is losing data. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,560 by orion 6/27/03 1:37:13 PM Reply	Could be a relationship problem Go to the tools menu, select Relationships, is there any other indexes being set between those two tables? If so, this could be part of the problem. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,561 by boxley 6/27/03 1:41:02 PM Reply	only relationship, single user will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,567 by ChrisR 6/27/03 2:27:15 PM Reply	Well the documentation is misleading? :-) 2 tables that have a field called Serial_Number that is defined as a text field indexed with duplicates allowed. Did you mean to say that there are 2 tables with serial number columns - one named [Serial_Number] and the [Serial Number] => at least that's the way I read the query.
Post #107,584 by boxley 6/27/03 4:55:32 PM Reply	the cut and paste was correct on the sql but I described it incorrectly. Changed name of hsinventory.Serial Number to hsinventory serial so I wouldnt confuse myself further. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,591 by orion 6/27/03 5:21:09 PM Reply	I've changed it in my example I have created a database using the same names you have described in the query, all I have is serial_number in one, and serial in the other. No other column names. They are set as indexed with duplicates allowed. I am doing this to try and replicate the problem you are having. I am entering the data myself, bogus data, but data that works. Not the exact same as your database, but enough of it so I can get a better idea of what is going on. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,594 by CRConrad 6/27/03 5:52:26 PM Reply	Is "field indexed with duplicates allowed" on BOTH tables? The SQL may be syntactically correct, but I'm not so sure it makes sense semantically. For a master-detail relationship to work, the linking field must be unique in the master table; otherwise, how should the DBMS know which master record a detail record is supposed to be a "detail" of? You'd get the Cartesian product of the intersection of the tables (if that sounds like gobble-dee-gook, read it as "the number of records returned would explode into humonguousity") for your "inner join"; maybe Access is refusing to return any records at all in order to avoid this? Actually, in all RDBMSes I've used -- except for Access (if you want to grace that with the term "RDBMS"), of which I can't remember -- the linking field must be defined as the Primary Key of the master table in order to be able to declare a Foreign Key dependency on it in the detail table; I'm not sure if that's implementation-dependent, but it seems to me as if it's probably dictated by the SQL Standard. And yes, I know that having a declared Foreign Key relationship isn't strictly necessary in order to do a join in a query, but the concepts are at least logically related... Maybe, for all I know, to the extent that Access (implementation-dependent, again) requires it for your join to work. So what I'd recommend is, look into that Master table and see if you can't remove "duplicates allowed" from the index on your linking field; preferably, make it the Primary Key. HTH! [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #107,597 by orion 6/27/03 6:41:35 PM Reply	Re: Is "field indexed with duplicates allowed" on BOTH tab I duplicated his two tables with only serial_number and "serial number" as the only column of the two tables, indexed with duplicates allowed. No primary key was set, Access threw a fit that I didn't set a primay key, but I ignored it. The query works in my version of the database, but then I do not have all the fields that Box's database has, nor do I have any of the relationships set between the two tables. The query works without primary keys set, with just the serial number text fields. I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. Access has a Relationships part of the database that can set Master and Foreign keys. For example he could have a one to many relationship between FooKey on one table, and FooKey on the other, whoch are primary keys, and somehow they are not matching up. The Relationship is not shown in the Access query, but Access is using that Relationship of the FooKey linkage in the query. Of course since I cannot see his Relationships on the database, I cannot tell if there is one that is hosing up the query. It can be accessed via the Tools Menu, and selecting "Relationships" and it will show the tables and have a line between the fieldnames that have a relationship between the tables. If there isn't a relationship set between those two tables, then it could be a damaged database or something else. He trimmed out the extra spaces, and did a search to see that the numbers do indeed exist in both tables. Of course it could also be just as you said, Access is refusing to return any records to avoid a problem like intersecting tables. My database only has a dozen or so items in the tables, his could have thousands and produces a much larger recordset when run. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,607 by boxley 6/27/03 7:20:48 PM Reply	The only primary key is on the master table the primary key is an autonumbered key generated by Access. No other relationships exist except the one serial to serial_number. There is 16k records in the hsinventory and the master table has 4900 approx. The purpose of the exercise is to gather the hostnames from hsinventory to populate the master hostname field so we could gather usage stats from a 3rd table. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,611 by orion 6/27/03 8:22:20 PM Reply	Re: The only primary key is on the master table The primary key in the master table, is it linked to a key in the hsinventory table by the relationships, or are you just saying that you have a primary key on the master table and have no relationship between it and the hsinventory table? "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #107,627 by boxley 6/27/03 9:35:46 PM Reply	the only link is the serial fields your second sentence is correct. than, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,626 by tseliot 6/27/03 9:35:31 PM Reply	That is a major assumption on your part I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", and second, that's not true for all tables. If the "proper" design were "no duplicates allowed", then why have the option at all? Because some sets are not atomistically unique. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,682 by CRConrad 6/28/03 9:30:20 AM Reply	Actually, for oce Norm is far more right than wrong; you... ...OTOH, are vicey-versy. bob the poet writes: [Quoting Norm:] I agree with you that the proper design of a database needs a primary key set on the master table with no duplicates allowed. ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", Well, no -- it is true of ALL databases that ANY two tables in them that you intend to use in a "master-detail" fashion should be linked by a field that is unique in the "master" table. (And since a primary key, by its very nature, won't allow any duplicates, it is the obvious candidate for this.) If, as a not-even-very-charitable reading suggests, Norm is talking about only the case we have here -- how to properly connect two tables _in a "master-detail" relationship_ -- then he is perfectly correct not only in this instance, but for this kind of relationship in all (SQL-based) databases in general. and second, that's not true for all tables. No, but then we weren't talking about "all" tables in general, but more specifically those that are involved _in a "master-detail" relationship_. If the "proper" design were "no duplicates allowed", then why have the option at all? For indexes on fields that are not used to link a "detail" table to a "master" one, of course. And for indexing the link field in the "detail" table, because it is the nature of a "master-detail" relationship that there can be many "detail" records for each "master" record... The uniqueness requirement is only on the "master" table, because there can be only one "master" record for each "detail" record. Because some sets are not atomistically unique. Huh?!? Idunno, suddenly you're using words like "set" (and, Bog help me, "atomistically unique"! WTF???)... Are you talking of "sets" of values of fields other than the one used to link a "detail" to a "master" table? 'Coz if you are, you aren't talking about the same thing as anyone else in this thread. (And besides, if you're talking about "sets" of complete rows from a table, you're factually wrong here too.) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #107,696 by tseliot 6/28/03 1:13:30 PM Reply	We're not disagreeing here. You're just using more words than I did. It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #107,706 by CRConrad 6/28/03 2:58:36 PM Reply	Ah... that "assumption"! :-) Bobby B writes: It wasn't clear from box's original post that his tables were in what you call a "master-detail" relationship. It was Norm who first assumed that. Well, we actually know (now) that they aren't in a "master-detail" relationship, if by that you mean they'd have to have referential integrity constraints defined on them... But on the other hand, they are in a "master-detail" relationship in the sense that that's how they're being used in BOx's query (if I understood it correctly); they are being "related" by the JOIN in the query. At least the latter of my two paragraphs/sentences above is no assumption, on Norm's or anyone else's part. And that the latter should (as in, "ought to") imply the former is also more of a reasoned opinion than an assumption. :-) [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #107,972 by mmoffitt 6/30/03 11:40:08 PM Reply	ALL tables need PK's. Unless you want your sp's to update all "non-atomistically distinct" detail rows in your detail table. bcnu, Mikem The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism. - Mark Twain, "Monarchical and Republican Patriotism"
Post #108,016 by tseliot 7/1/03 10:31:18 AM Reply	Sorry; What's an "sp"? I may try to cram a lot of meaning into a few words, but at least I don't abbreviate where it's just as easy to write it out. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #108,018 by ChrisR 7/1/03 10:36:51 AM Reply	Probably 'Stored Procedures'.... ...but then we were talking about MS Access, so maybe not.
Post #108,047 by orion 7/1/03 12:32:23 PM Reply	In Access they are "Queries" but they work like stored procedures. But I get the idea. "I wonder how much of this BS Corporations will continue to shallow before they start looking into alternatives to Microsoft software?" -[link\|http://z.iwethey.org/forums/render/content/show?contentid=106839\|Orion]
Post #108,100 by tseliot 7/1/03 4:18:35 PM Reply	Are you disagreeing with me? I can't tell. ALL tables need PK's...Unless you want your sp's to update all "non-atomistically distinct" detail rows in your detail table. Is that not what I said? Or was it the extra step that happened to notice: a sentence written "all...unless" is pretty much the same as saying "not all"? As far as stored procedures go...wait, let me count them in my code...I count nearly... nearly one. Yes, I think that's right--nearly one. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #108,339 by mmoffitt 7/3/03 11:23:33 AM Reply	Dunno if I disagree. You write (in part) wrt Primary Keys: ??? First of all, I think you mean "design of a table (or perhaps 'these two tables')", and second, that's not true for all tables. I know the topic is not a real database, but the idea that there exists a database table that does not need a primary key is foreign to me. If that is your view, then I am most definitely in disagreement with you. If you've never worked with a real database, and you don't know that in most secure databases users are prevented from accessing tables directly and can only access the data in the tables through stored procedures and views (and you definitely want insert/update/delete sp's - and yes that's stored procedure), then it's not surprising that you hold that primary keys are unnecessary on some tables. But, you're absolutely wrong if that is your view. Even with toy databases you should put a PK on each table. Doing so can save yourself grief should your little app ever need to scale up. Not doing so is a violation of "good design". bcnu, Mikem "Son, if you haven't got time to do it right the first time, where are you going to find the time to do it a second time?" - Dewey Keller, my grandfather
Post #108,362 by tseliot 7/3/03 2:10:46 PM Reply	Toy and scale are not the issues. Design is the only issue I'm talking about. Not every table has unique elements, because not every Set of objects in the real world has unique elements. Example: you have 30 tool packages you can rent out to customers, all the same. There is no distinction between them. There never will be a distinction between them. That is the whole point: they are the same in every way. A table which models these 30 tool packages does not need a primary key. You might feel safer with one, but that does not erase the fact that there is no need for one. All that matters is that we do not oversell them. A primary key does not assist in this process at all. Would it hurt to have one? Probably not. If you want the overhead, be my guest. I can just see kindergarten class: "Now if Jimmy has five apples, and we take away 3 apples.." "Ooh, ooh, wait! Which three are we taking away?" "It doesn't matter, just subtract three." "But which three?" "It doesn't matter!" "Oh, it most certainly does. Why don't these apples have barcodes so we can tell them apart? Billy, go get the label maker! Susie, find us a workbench and clamp! Margaret, you start cutting the crystal for the laser...we need a reader..." Overkill. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10
Post #108,369 by CRConrad 7/3/03 3:51:15 PM Reply	Then you don't need a thirty-line table at all... ...but a numeric variable `NumberOfToolSetsRented` somewhere, that fluctuates between zero and thirty. Worst case, you have to have a (one-line) table to keep it in. Then you can just update that table (increase or decrease the value by one) every time a toolset gets rented out or returned... And the way to do that is, of course, by using a stored procedure. Oh, and if you ever find you only have twentynine toolsets on hand, and want to know which customer didn't actually return his as he was supposed to, then what do you do? Then you wish you'd bar-coded your toolchests and used the `ToolChestID` value of the barcode as a primary key, that's what do you do. [link\|mailto:MyUserId@MyISP.CountryCode\|Christian R. Conrad] (I live in Finland, and my e-mail in-box is at the Saunalahti company.) Your lies are of Microsoftian Scale and boring to boot. Your 'depression' may be the closest you ever come to recognizing truth: you have no 'inferiority complex', you are inferior - and something inside you recognizes this. - [link\|http://z.iwethey.org/forums/render/content/show?contentid=71575\|Ashton Brown]
Post #108,404 by mmoffitt 7/3/03 10:56:12 PM Reply	Thank you. bcnu, Mikem The soul and substance of what customarily ranks as patriotism is moral cowardice and always has been...We have thrown away the most valuable asset we had-- the individual's right to oppose both flag and country when he (just he, by himself) believed them to be in the wrong. We have thrown it away; and with it all that was really respectable about that grotesque and laughable word, Patriotism. - Mark Twain, "Monarchical and Republican Patriotism"
Post #108,435 by FuManChu 7/4/03 1:18:46 PM Reply	Should I answer? This is becoming religious and I don't see anyone convincing anyone else. You've all got your methods; use them. I have an extra tool that I can use with or without your blessing. So I'm not going to beat this example to death; it was a bad example from the beginning because it only gains significance if scaled up, which was part of the issue I was trying to avoid. If you can't generalize the concept from the example, then again we go nowhere, and the gain frankly isn't worth it even if one of us happened to convince another. The only point I WILL make at this juncture, is this: Then you don't need a thirty-line table at all...but a numeric variable `NumberOfToolSetsRented` somewhere, that fluctuates between zero and thirty. Um... get thee behind me? This is, IMO, a far WORSE solution, since you are now hard-coding minute details of the transaction INTO A FREAKING VARIABLE NAME. What was that about scaling up? I give it...six months before such a horrible hack would get taken back out of the code. Make an invoice object with a `Qty` property, maybe, but `NumberOfToolSetsRented` is going too far. I'm gonna go build my own theme park! With Blackjack! And hookers! In fact, forget the park!
Post #108,395 by a6l6e6x 7/3/03 9:56:56 PM Reply	But, but, a tool of any value has a serial number and they.. are different. Warrantees or support, if any, are tracked by serial number by the manufacturer. Suppose, on return, your clients substituted a barely functioning tool of the same model for one they rented. How would you know? Are we talking hammers here? :) Alex The tendency to turn human judgements into divine commands makes religion one of the most dangerous forces in the world. -- Georgia Harkness in "Conflicts in Religious Thought" (1929)
Post #108,436 by FuManChu 7/4/03 1:24:29 PM Reply	In this particular case Suppose, on return, your clients substituted a barely functioning tool of the same model for one they rented. How would you know? Are we talking hammers here? :) We are talking hammers, at my company. So "barely functioning" is no different than "functioning". Components of the toolkits (like hammers, shovels, etc.) get damaged and lost all the time, and that gets noticed and dealt with by humans at the time of restock. The cost of numbering would far exceed any benefit, since the toolkits often get moved from site to site "on the fly", without time to come back to base camp. I'm gonna go build my own theme park! With Blackjack! And hookers! In fact, forget the park!
Post #107,606 by boxley 6/27/03 7:15:57 PM Reply	Started with no index on either field thought indexing them might help. Since Diane my co-worker will be monitoring this thread for advice next week anything else you could think of would help. Me I will be a Nix admin again starting Monday. Apreciate all the advice. thanx, bill will work for cash and other incentives [link\|http://home.tampabay.rr.com/boxley/resume/Resume.html\|skill set] questions, help? [link\|mailto:pappas@catholic.org\|email pappas at catholic.org] As the Poets have mournfully sung. Death takes the innocent young, The rolling in money, the screamingly funny, And those who are very well hung. W.H. Auden
Post #107,630 by tseliot 6/27/03 9:37:44 PM Reply	It's probably not a factor in your problem... ...but I have not personally used a single INNER JOIN in the last four or five years. If you are looking up details in one list based on the other list, use a LEFT or RIGHT JOIN, if only to make explicit your own logic. Many fears are born of stupidity and ignorance - Which you should be feeding with rumour and generalisation. BOfH, 2002 "Episode" 10

Welcome to IWETHEY!