Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 15th, 2007, 12:48 PM
Authorized User
 
Join Date: Nov 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default Copying Databases & Foreign Keys

I'd like to get advice on two topics.

1) What's the best way to migrate data between test and production databases. I tried having them open in two Access sessions, and dragging objects from one to the other, but this causes Error Message 3734. It's feasible for me to delete and recreate the object database, if that's a good option. The production database is on a server.

2) I have a "People" table whose key is email address. One of the fields is manager id (who the person reports to) which contains the email address of the manager. So that's a foreign key that refers into the same table. I chose to put the managers in a separate table with an identical structure. I have a union query to provide rows from both tables. I'm not sure that separate tables is the best solution, but it seemed to simplify reporting. What are the tradeoffs?

 
Old February 16th, 2007, 08:26 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

1. If you just want to move data, then import it to an existing table. Open the Tables object, select Insert > Table > Import table, and then import each table to its counterpart in the production database. If you want the structure, then import the table as a new table. This is a good one time solution, but if you are doing this frequently, you will want to code it.

2. You should be using the first method. Put all of your people in one table, and then refer to the PK in the supervisor column. You will have to build this by hand, since the last time I did this I don't think the look up wizard support this kind of reference. You may also want to put a Yes/No field in your table for Supervisor, so that certain people can be given Supervisor status, and then use this to limit the selections for Supervisor to only those where Supervisor=Yes. How does your method simplify reporting? Or, how is the proper method hampering your reporting?

mmcdonal
 
Old February 16th, 2007, 02:35 PM
Authorized User
 
Join Date: Nov 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, that's very helpful. However, the import created a new table with "1" added to the end of the name, so I had Registration and Registration1. I deleted the old table, renamed the new one, and renewed the relationships. Also, in merging the Manager table rows into the people table, I exported the manager table to a spreadsheet, then imported the spreadsheet into the people table, using the first row to provide field names.

Regarding the separeate manager and people tables, I have a report "People by Manager" that uses a query which uses a join where People.ManagerID = Manager.Email. I've copied the SQL below. Can I join a table to itself?

SELECT Managers.Last AS Managers_Last, Managers.First, People.Email AS People_Email, People.Last AS People_Last, People.First AS People_First, People.Role, People.Hire, People.ManagerID, People.Location, People.Mailstop, Managers.Email AS Managers_Email, People.HireDate, People.Phone
FROM Managers INNER JOIN People ON Managers.Email = People.ManagerID
ORDER BY Managers.Last, Managers.First, People.Last, People.First;


 
Old February 16th, 2007, 02:50 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yes. This is called a reflexive join. It is common practice. I would suggest looking this up to do what you want. As I said, the Access look up wizard (field look up) will not support this, but you can di it by hand. Then when you do the query, you MAY have to use an alias and treat the table as 2 tables like "select ... FROM tblYourTable, tblYourTable As Table2. There are plenty of articles on this on MSDN, and also in Henderson's "The Guru's Guide to Transact-SQL".

Let me know if you need more help with this. I have a couple of these in my asset management database for the Supervisor look up, the same as you are doing. Here is an Access query to the Supervisor field:

SELECT tblUserName.LOGIN_NAME, tblUserName.LastName, tblUserName.FirstName, tblUserName.SupervisorPOC
FROM tblUserName;


mmcdonal
 
Old February 16th, 2007, 07:02 PM
Authorized User
 
Join Date: Nov 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I added a Yes-No "IsManager" to the table. I opened the query in design view, removed the manager table, and add People, which appeared as "People1".
I drew a relationship from People1.Email to People.ManagerID. Then added the fields from People1, with condition IsManager = True. Works like a charm.

 
Old February 20th, 2007, 08:31 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This is standard procedure in SQL design, but Access books usually don't discuss reflexive relationships. Glad to have helped.


mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Design: Foreign Key to Multiple Primary Keys? kalel_4444 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 10 May 8th, 2008 04:14 PM
list foreign keys in sql 2000 jomet SQL Server 2000 2 May 1st, 2008 11:15 PM
list foreign keys jomet Oracle 2 March 28th, 2008 04:17 AM
inserting records with foreign and primary keys tdaustin Classic ASP Basics 0 October 4th, 2005 12:49 AM
transfering identites as foreign keys santaji SQL Server DTS 1 September 23rd, 2003 09:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.