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 -- "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 _______________________________________________ General mailing list [email protected] http://brlug.net/mailman/listinfo/general_brlug.net
