Martin, Donna M
dmart06 at emory.edu
Mon Mar 30 12:24:02 CDT 2009
You are right...It IS ugly. In reality, I had already built this database, and it worked great for years with one minor quirk. The problem? The Finance Team is balking at having to add faculty and staff, even if it is just their name, and three other fields. The ONLY data necessary from the faculty/staff side is their name, nametype (fac or staff), and whether they are active. The UNION was an attempt at the 'Paid to' field. The same faculty/staff data is used for the 'In support of' field as well. The rest all lives in the Accounts Payable side. I just knew this was going to be ugly... Thanks for handing me the truth of it. I'm screwed. Donna ________________________________________ From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com] Sent: Monday, March 30, 2009 1:06 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] A little help, please Your problem here is that you are trying to display data about two completely different entities. Faculty / staff is not Vendors. This kind of problem always rears its head when you try to handle two entities in a common manner. I think given what you are trying to do you need to create a payee table (which is probably what you are in the process of doing). This table will have all of the common data that needs to be present in order to process a payment. Things like name, tax id, address etc. If that data is required to issue a check, then it has to be present in the source table, whether t1 or t2. Now... pull all of that data into the combo as hidden fields. When you select an entity in the combo have the code copy the hidden fields into the payee table fields. You might (probably do) want to also copy the PKID in the source table into a ExternalPK field and have another field which is a number representing the source - 1 for T1, 2 for T2. This basically makes a COPY of the relevant table into the Payee table, complete with the PK back in the source table in case you need to get back there. In other words, abandon the idea of storing the PK of the foreign tables t1/t2 as the "bound field" of the combo. That just won't work. It MIGHT (kinda sorta) work if you used the foreign PK and the "source" field as a 'multifield FK', but it would probably be ugly and Access combos cannot be "bound" to two fields simultaneously. Alternately, you could append the external table PKID with a dash and a "source number", IOW 1324-1 (PKID 1324 from t1) or 5376-2 (PKID 5376 from t2) and use that STRING as the FK in the payroll table. This does work, and as long as the number of source tables is not going to grow... You would need to go to both of the source queries for the union, and create a new PK column where you create this string. This method has the advantage of "not storing the data twice", along with eliminating all of the update stuff you would have to do to stay in sync with the external tables. Let the source tables continue to be the place where the data is kept. Of course now all of your queries everywhere has to recover the data from different places. Ugly no matter which way you go. John W. Colby www.ColbyConsulting.com Martin, Donna M wrote: > Perhaps my entire approach is wrong. Here's what I am trying to do: > I am trying to create a list of payees from two tables: 1) Faculty/staff info is added by HR personnel to database1.T1; Vendors and external people would be entered into database2.T2. Only T2 would ever be updated from this source. And yes...it is a concern that both have autonumber pk's, which I have no idea how to resolve. FYI...a new record would require more than just the name of the vendor (e.g., phone #, etc). > > I am using Access2007. > > Thanks! Donna -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com This e-mail message (including any attachments) is for the sole use of the intended recipient(s) and may contain confidential and privileged information. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this message (including any attachments) is strictly prohibited. If you have received this message in error, please contact the sender by reply e-mail message and destroy all copies of the original message (including attachments).