[AccessD] Cross referencing records - ideas

rusty.hammond at cpiqpc.com rusty.hammond at cpiqpc.com
Wed Jun 18 10:29:16 CDT 2003


If it were me, I think I would do away with the one memo field and make a
table of latest pricing levels for my items.  Then have another table linked
to the pricing table in a one-to-many relationship that lists the entries of
sales people that want that item at that price and how many they want.  Once
the price level is met, a purchase order is created.  The first table could
have a notes field to give information about the item/pricing, the second
table could have notes about the order for that salesperson.  This could all
be tied together so both sales people could see the list of their orders
tied to a pricing entry, as well as anyone else who has an order tied to
that pricing entry.

HTH

Rusty

-----Original Message-----
From: Bruce Bruen [mailto:bbruen at bigpond.com]
Sent: Wednesday, June 18, 2003 9: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


More information about the AccessD mailing list