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?

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

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;

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

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.

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


mmcdonal