Lookup column based on table value

[greenkevin86]

I’m in the process of designing a database that will track documents (e.g. current revision number, approval status, and distribution to internal employees, supplier personnel, and customer personnel). The purpose is to be able to track what documents are required to be sent to what personnel so that when a document is revised, we will know who needs to receive the next revision.

My Tables are: Documents, Suppliers, Supplier Personnel, Customers, Customer Personnel, Internal Employees, Internal Distribution, Customer Distribution, and Supplier Distribution.

I envision having a main form with the document information, and subforms for distribution to internal employees, supplier personnel, and customer personnel.

However, after looking at my tables, to avoid data duplication and improve efficiency, it makes sense to just have one table for distribution. A column in the table could be distribution type and the values could be limited to Internal, Supplier, and Customer. The reason I orginally designed with three is because I knew the lookup values in the table would be directly linked to their respective tables (e.g. Customer distribution table would lookup Customer names/email addresses from the Customer Personnel Table).



My question is how to design the form/table so that a combo box on the subform will look up the appropriate columns in a table based on the distribution type in the Distribution Table. For example, if the document goes to supplier personnel (distribution type= "Supplier"), I would want a combo box on the subform to display only supplier personnel name and email addresses for me to select. Similarly, if I want the document to go to internal personnel (distribution type = "Internal), I would want the combo box to display internal employee names and email addresses. A document could have any combination of distribution (e.g. maybe one document is required to be sent to 3 internal employees, 1 supplier contact, and 2 customer contacts whereas another document might just have to be distributed to 5 internal employees).

I also have been using the lookup wizard to establish relationships between tables. Is this ok? I read where this could cause some problems but I don't know how else to logically create the relationships with drop-downs.

[June7]

Relationships can be established with the Relationships builder. Lookup is a technique to record primary key value as foreign key by letting user view and select from a list that has meaning to them, as opposed to a list of autonumber IDs. As developers normally don't let users work directly with tables and queries, there is no need to set lookups in tables, but instead comboboxes and listboxes in forms. I, as developer, prefer to see the real value, not the lookup alias, when I work with tables.

Are the tables for customers, personnel, suppliers the same in structure? This database only tracks document distribution? No sales, inventory, purchasing, invoicing, payroll? If tables are same then can probably use one table called Contacts. Have a field that identifies record by type (Internal, Suppllier, Customer). Then you can have cascading comboboxes to select contacts for the distribution. First combobox would have the three types as options. The second combobox would list records that meet the type criteria of the first combobox. Review tutorials at http://datapigtechnologies.com/AccessMain.htm especially the 3 on comboboxes in the Access Forms: Control Basics section. However, be aware that comboboxes with RowSource that involves a Lookup don't work well in forms set for datasheet view.

[greenkevin86]

Thanks for your help. This is what I have done. The tables are basically the same structure so I merged the tables into one "Contact" table like you have suggested. However, I do want to store additional information (the Supplier or Customer Name that is related to the contact), so I made another table called "Company". My Contacts table has the columns, Contact ID (PK), First Name, Last Name, Email, Title and Company ID as a foreign key. The Company table has the columns Company ID (pk), Company Type (Supplier, Customer, Internal), and Company Name.

Yes, the database just tracks distribution, we have a large scale system for inventory, sales, etc. I haven't had the chance to review the tutorials yet, but if I use the approach you have suggested, are the values actually stored in the combo box the key values? When doing the lookup wizard, the dispayed lookup values (e.g. First Name, Last Name) are different than what is actually stored in the field (the key value).

[June7]

Right, the key value is stored so the name data is not replicated accross tables. Retrieve the name data in reports by making the RecordSource a query that joins the related tables on the pk/fk fields.

[greenkevin86]

Thanks I got the combo box but working but ran into another dilemma:

Right now, I have a document form set up with a distribution subform. The document form contains attributes about the document and the pk is an autonumber field called "Document ID" that isn't shown on the form. The distribution subform shows the name/email address of the people the document goes to among some other things. In the distribution sub-form, I also track whether we have received back their receipt acknowledgement (A signed document from the individual stating they have received the distributed document, this is a requirement when sending out controlled documents) as a yes/no data type and the receipt acknowledgement date. My problem is that when a document gets revised (e.g. from Rev. 0 to Rev. 1), the subform will still show the old receipt acknowledgement data, these values will have to be cleared out until receipt acknowledgement is received for the new rev. My planned solution is re-structuring the key so that I have a composite key composed of document number, title, and Rev (and maybe even Rev. date to be safe). Now, separate records will have to be entered when a document is revised and I will still have data from the old revs, is this the right appraoch? However, a shortcoming is that I want to automatically bring the distribution list over (e.g. if it went to 5 people, populate that information into the subform of the new rev.), is it possible to do this? Maybe I need another subform and leave the personnel distribution information (i.e. name, email) on the first subform and the receipt acknowledgement on the second so that for a given person, I could see their receipt acknowledgements for each rev. I would need to add another table to do this. As a side note, you would think document Number would be the logical key choice but some of our documents don't have numbers, which is an inherent flaw but I have to work with what I got.

[June7]

You want to retain history of document distribution at the revision level? Need to know which revision was distributed?

You have documents that are repeatedly sent to the same distribution?

Yes, previous distribution information can be copied. Will require running an INSERT SELECT sql action.

[greenkevin86]

Typically, the revisions get distributed to the same people but not always the case. It would be nice just to have a "create new rev." button on the form and a new record with all the information is created (including the subform child fields) except the Rev. goes to +1. Only difference for the subform child fields is I would want to set the Reciept Acknowledgement field to "No" and clear out the receipt acknowledgement date. Then, I could add or delete distribution personnel from the subform if any of that changed, but typically the distribution will be the same. Could you give me some more direction on how to implement the INSERT SELECT for this application?

[June7]

Review http://msdn.microsoft.com/en-US/libr...=SQL.105).aspx

The INSERT SELECT example doesn't show WHERE clause in the nested SELECT which you will need.

[ssanfu]

PMFJI,
you might also look at combining these tables if they have the same structure: Internal Distribution, Customer Distribution, and Supplier Distribution. Name the table Distribution and add a column for "Internal", "Customer", or "Supplier"

[greenkevin86]

ssanfu, I have already combined the tables as you have suggested, thanks for the help.