Many thanks Master. I will try it tomorrow! Note: I don't make the rules. I simply respond to what the people that sign my check want. When I was a Platoon Sergeant, I constantly revolted against stupidty. I do not hear any rounds going off, so the lady with the ink pen must know what she wants. They want all the researchers in one field, they get all the researchers in one field, even if MS Access knows it is retarted.
What is a guy to do? I wrote this really neat append to record form and nifty on-the-fly report. No soap. Never let the programmer design the user interface; never let the users define the parameters. Dim Sigh as string Dim MS-still-here as boolean... Well, you can gues the rest... I appreciate the OT help! Doug --- John Hebert <[EMAIL PROTECTED]> wrote: > First off, you must be nuts. This is way off-topic > here. Haven't you tried > any MS-Access forums or mailing lists? But, to be > nice, I'll answer it > anyway. Just had to poke you with a stick for asking > this. ;) > > Second, this is not an Access question, it is a > relational database > architecture question. > > Third, what you are doing defies the common rules of > database normalization. > Don't pursue this strategy. Don't ever use values > with meaning as a primary > key, even if it is unique. Autonumbering ID columns > are your friend. Shoving > values together in a text field with a delimiter is > sick, so stop the > madness. > > Fourth, the solution is to create a third table that > expresses the many to > many relationship between inventors and disclosures. > So, you will have > tables with the following schema: > > Disclosure Table > > ID | DisclosureNum > ------------------ > 1 | 2003-16 > 2 | 2003-17 > 3 | 2003-18 > x | yyyy-nn > > > Inventor Table > ID | InventorName | InventorOtherInfo > ------------------------------------- > 1 | Joe Blow | blahdeblah > 2 | John Doe | boopboopboop > ... > > > DisclosureInventor Table > > ID | DisclosureID | InventorID > ------------------------------ > 1 | 2 | 1 > 2 | 2 | 3 > 3 | 1 | 1 > 4 | 3 | 1 > 5 | 3 | 2 > 6 | 3 | 3 > ... > > Note that in the DisclosureInventor Table, you can > show that a disclosure > can have multiple inventors and likewise, an > inventor can have multiple > disclosures. > > Now, I am not an Access programmer, but I do know a > little bit about good > table structure. My wife, who is a damn good Access > programmer, says that > the hard work for you will be in figuring how to do > the forms to properly > show the many-to-many relationships. She suggests > using subforms, or > possibly using two forms for each relationship > (disclosure to inventor(s), > inventor to disclosure(s)). > > If you need more info, google around for "Access, > many to many". There are > lots of good sites out there for Access developers. > > Good luck! > > John Hebert > > -----Original Message----- > From: Doug Riddle > To: Libranet Debian; General BRLUG > Sent: 8/14/03 6:57 PM > Subject: [brlug-general] OT: MS Access Question, > Combining multiple lookups > from one table into a single field in another table. > > Sorry about this off-topic request, but I am in a > bind > at work and need some help. > > The Question involves MS Access XP/2000. > > A little background: > I am creating a database for Intelectual Property. > 98% of their press starts with a process called > Disclosure. The Inventors meet with a panel of > experts and lawyers and introduce their ideas. The > experts evaluate the idea and see if they know of > existing work in the area, and evaluate science and > soundness of the idea. The lawyers take notes and > follow-up with searches for prior-art. Meaning, > does > someone already have a patent on a idea close enough > to make the new idea not worth pursuing. A > disclosure > may be presented by one inventor or a team of > inventors. Often during the course of a disclosure > a > group of inventors may be directed to other experts > that would be valuable team members. > > Each Disclosure is assigned a unique number in the > for > of YYYY-NNN, where YYYY is the current year, and YY > is > the sequential number in order that the disclosure > happened. Meaning, that the 16th disclosure in 2003, > would have a disclosure number 2003-16. This number > indicates the file number, and if the idea pans out, > will become the license number. This system has > existed in paper form for decades. IP (Intelectual > Property) is picking up, and they want me to put it > into a datase. > > The Problem: > Each disclosure number is unique. The inventors are > kept in a table with all of their information. One > Disclosure can have one or many Inventors. > > I need a way to select Inventors from a Lookup > form/query/table and combine them all into a single > text field in a record in the disclosure table. > > One of the reasons we need to use a lookup to the > Inventors table is that we want the data to be > uniform, searchable, and consistant. There are > others, but I won't bore you. Data entry errors are > justification enough. > > As far as I can tell, MS Access never conceived of > this need. While I'll be the first to admit I am > not > a MS Access guru, I do know it better than the > average > bear. > > I have wasted two days messing around with clunky > and > largely unsuccessful work arounds. I cannot believe > there isn't an AppendField object. There has to be > a > better way to add data to a field without opening > the > field and typing the data in. My best effort so far > has me filling a table, exporting the contents, and > importing the data, and it fails without rhyme or > reason half the time. Timing issues on when the > table > is locked no doubt. With more people using the > database, it wil only fail more often. > > I haven't done VB since VB3 that is worth talking > about. (I did write a program that even Exxon hands > couldn't crash with that though! Geeze, I soaked > them > for it too. It was great.) > > Anyway, that is the long (very long) and short of > it. > I want a way to make to multiple selections from one > table and combine them into one field in another > table, comma separated, if at all possible. I KNOW > this is possible. I am certain it is fairly simple. > > I am also certain that I will NEVER find it by > myself. > > Please help. I would like to be able to wake up anf > go to sleep without coming up with some Rube > Goldberg > workaround evey three minutes that I can only check > when I get to work. Besides, I have a host of other > stuff I am also supposed to be doing. > > Any help is much appreciated! Again, sorry about a > Micro$loth question, on this list, but I am at wit's > end. Save it. I know what you are thinking. Just > answer my plea!!!! > > ===== > Warmest Regards, > > Doug Riddle > http://www.dougriddle.com > http://fossile-project.sourceforge.net/ > http://www.libranet.com > === message truncated === ===== Warmest Regards, Doug Riddle http://www.dougriddle.com http://fossile-project.sourceforge.net/ http://www.libranet.com -- "Firearms are second only to the Constitution in importance; they are the Peoples' Liberty Teeth." - George Washington -- __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
