Need help with an advanced append query

[jpihpa]

I have an old database and a new database in which I would like to move data from the old to the new. I found articles and posts on here about the append queries but I didn't find anything on the problem that I have. The old database was set up with a subform linked to it so that for each project, since every one of them has the same basic steps, the person logging in the information could just say when the date received, approved, etc. was and then also leave a comment about each step if need be. What I am looking to do is get the main data, like the address, city, state, etc. out of the form and put it into the newer database but also take all the information that is linked with each record along with it. I have no idea how to make sure that each individual project "received, approved, etc." subform moves to the new databse and is still somehow linked to the project it belongs with. I know that the subform is linked by the project number only but can I somehow do an append query on both the form and subform and get them both into the new fields?



Thanks to anyone who can point me in the right direction to get started on this.

[R_Badger]

Firstly, the basic understanding you need will be from your tables and their relationships would be the first thing I would look at, as this will ultimately determine how the form/subform interact.

From what you appear to be saying in the first line, you want to move data (tables) from lets say Old_DB.mdb to New_db.mdb, is this correct?
However later you are talking about moving form/subform fields to new fields what sounds like you are just creating a new table in an existing database.

The two things can both be done, however they are both done differently
The first can be done via the import wizard or a docmd.transferdatabase method reference:
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx
(I have never needed to attempt this myself, so this is just an educated guess)
The second can be performed by an append query, however i cannot help you write that as I have no idea how your tables are structured. However the following reference may help:
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx


If you could attach your project for scrutiny that would help, (remove confidential data, insert a few sample entries so we can see it work,compact & repair, zip if still large)

Failing that a jpg of your relationships diagram might help.

[jpihpa]

Yes, your assumption of the first line is correct. I did (and already have) want to move (copy) data from the old to the new which I have gotten that to work. As for the second statement, I am not creating a new table in an existing table, what it actually is is a form where you enter in data (owner, address, city, etc.) and then at the bottom there is a subform inserted into this form where you enter all the dates and actions that have occured (action date, action that took place, comments about the action).

As for attaching the file, I think the best thing I will be able to do is to take a screenshot of the form and see if that will help you. If you need more screenshots of what is occuring, you'll just have to tell me what you need and I can get it.

Just as a clarifier though (I hope), the main thing I want to get out of this is having all of the information in one record without having subforms and links. I have already gotten the data out of the main form, that wasn't an issue, but the next step is to take the information that is in the subform (which is one table linked to the main form where the action date, action, etc. is entered in), pull it out, and then put it into the new database based on the indexed field (project number).

I have uploaded 4 images showing the database. I hope you can help me because this is just agrivating me right now. Thank you!!

[jpihpa]

I posted a question earlier today and I think I need to change the way that it is worded into another format. What I have is a database that has three tables linked together and what I want to do is pull the data from one of the linked tables and have access put it with the corresponding field in my new database based on the field in which it's linked to the main table.

The only description I can give is I have a table for keeping records of the project name, address, etc. and a subform where you enter the part of the application process that was received or approved and then the date when it was received or approved. I just want to get the info from the subform to be put into fields in my new database so that I can combine the two or three tables into one table to avoid the problems we have had in having the owner of database retire and then not being able to change anything. (We have talked to our "IT" people and they can't figure out the permissions on the database)

I apologize for posting two new threads about the same thing but I don't think I did a very good job of expaining this in the first post and the title of the first post is rather unclear as to what I want to do.

[June7]

Moderator note: I merged your two related threads.

Why not able to provide project? This would be resolved much faster if we could analyse directly.

Possible option: build MakeTable query that joins the tables. Import the new combined table to the new project.

[jpihpa]

I guess my question would be what exactly do I need to do to get you the copy of the database that you need? We have about 2000 records in this database and I don't know enough about "removing the confidential data" from it so that you can use it and I can still keep all the records...do I just need to create a copy of it and then remove all records? Or what do I need to do?

[June7]

Follow instructions at bottom of my post. You can remove just the columns that have confidential info (address, phone, ssn)