Arthur Fuller
artful at rogers.com
Wed Jun 18 10:03:30 CDT 2003
IMO stuffing everything into a single memo field is the problem, not the solution. Every new note should be a new record in a table with a memo field. Then you can forget about all this concatenation stuff and concentrate on displaying the results suitably. Not that it's necessarily black and white: I have several times included both a normalized version and a non-normalized version in an app, simply because the non-normalized version was the client's dictate. That doesn't mean I have to live with its limitations, however. In such a case I do the actually important writes to the normalized table, then execute some code to write to the non-normalized table. In your case my normalized table would have a date and a memo and requisite FKs, then some code would concatenate new memos to the non-normalized table's memo field. My $.02 :-) Arthur -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bruce Bruen Sent: June 18, 2003 10:24 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Cross referencing records - ideas I am after the concept for a solution. I guess I'm having as much trouble describing it as conceiving it. However, you have created a germ of an idea here. Lets see if I can explain it more clearly. A part of the (much) larger job quoting/pricing system involves 'actioning' pricing on parts. That is, once they have figured out a configuration to suit the customers needs of a machine, they search around for the best component supply price both locally and on the web. This actioning may take several days and involve several different people in several different offices. As they traipse around doing this, they enter notes regarding the component supply for the job. To do this, we use a popup form off the jobcard form into which the user enters his notes. When they close the popup, the note is appended to the "job_history" field on the job card. (In fact, its prepended so they appear in reverse order.) For example, Joe rings his mate at ShonkyPCParts and gets a quote for $98 for a mini-keyboard. He opens the job card form up and clicks the "ADD a note" command button. Up comes the popup and he enters "Spoke to Fred down at Shonky's, they can give us the kbds at $98 if we can use 250 of them" and closes the form. The job card job_history is updated and now reads: "19 Jul - Joe Bloe - Spoke to Fred down at Shonky's, they can give us the kbds at $98 if we can use 250 of them 18 Jul - Sam Snoe - some prior comment by Sam ...... Etc" Now, lets say Ben Crow also needs mini keyboards for another job, he and Joe can make up a need for a total of 280 of them. Joe adds a new note to the file. The history now reads: "19 Jul - Joe Bloe - Ben C can help us make up the kbd order for his POS job for Bigbank 19 Jul - Joe Bloe - Spoke to Fred down at Shonky's, they can give us the kbds at $98 if we can use 250 of them 18 Jul - Sam Snoe - some prior comment by Sam ...... Etc" This is where the issue lies. What they would like, is some way to easily link Joe's job with Ben's job and vv. Now, there is no natural key for the job cards - we excluded this on purpose in the design. You can see one of the ways a jobcard may be referenced above: (broker=Ben, client=Bigbank, jobtitle=...POS...). This is a typical (not the only) way these guys refer to a job. Now Ben, over the course of time, may do more than one POS job for Bigbank, but usually only one is active at a time, if there were more than one they would call them differently. What the germ of the idea is, is this. There must be some way of letting them create a hyperlink to the job and attaching the hyperlink to the referencing phrase. That is, in the above hstory : "POS job for Bigbank" would be the link text and the hyperlink would be to the PK for Ben's job card. The question now becomes, "How feasible is this? I could easliy add a cmdbutton on the ADD a note popup to open a search for other jobs. I can use selstart etc to position the link. I can build the html easily enough. What I would need to do is change the textbox for note entry to some sort of edittable html control. Any ideas? And change the history display textbox on the jobcard form to a webcontrol (as its not directly edittable). Is this clearer? What do ou think of the solution? Should I go back to gardening? Tia Bruce -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka Sent: Wednesday, June 18, 2003 3:34 PM To: 'accessd at databaseadvisors.com' Subject: RE: [AccessD] Cross referencing records - ideas Are you asking for a method of providing an identifier, or a whole solution? I think you are saying that they want to refer to other memo fields, from within a memo field they are creating? What I would do, would be to provide them with a 'refering' option, that let them 'search' the current memo fields. Take the ones they selected, and either add them to the end of the memo, or keep the data in a separate table. (You could add it to the existing memo field, by putting the primary keys for the table after a null character, then change your displaying forms to display only data left of the null character). Then, in the form that users display the memo fields, just add a subform, or listbox that is titled 'related job cards', with portions of the text in them. Drew -----Original Message----- From: Bruce Bruen [mailto:bbruen at bigpond.com] Sent: Wednesday, June 18, 2003 12:06 AM To: accessd at databaseadvisors.com Subject: [AccessD] Cross referencing records - ideas Hi List, I'm looking for ideas. I have an application that involves fairly simplistic action history tracing, so simple it is done by a memo field. That is, the yewsers completes a textbox for an action and when they save/exit the contents of the textbox are appended with a timestamp to the memo field. Works completely fine for the need. I have been asked whether it would be possible to do the same thing for the "occasional" need to cross reference rows in the master table. They would like to include a note of the ilk "See also job card xxxxxx". Simple! Except... There is no readily available natural key for the job card. Depending on the situation the natural key is nearly all the card! I don't want to use or expose the surrogate key for all those good reasons. My initial reaction is to include a realtionship table that would be used to hold "Related Job Cards" and build a popup based "Link to Card" form and a popup based "See related Cards" form to support this. However, I don't think the usage warrants the expense. Has anyone had a similar experience and found an inexpensive answer? Tia Bruce _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com