Appending Queries

[Kenln]

I have two seperate queries each comming from seperate tables. The queries and tables have identical structure. One is used for archived data and the other for open projects.

How can I run a query (or VBA SQL statement) that will, in essence, append the open projects to the end of the archived data. No project will exist in both tables so a relationship gets wierd.

I could build a temp table but I'm trying to avoid that which end any normalization.

Thank you in advance for any help,

 

[gemma-the-husky]

you need two queries

one to append the projects to the archive table. There is no relationship - simply produce a query that extracts the data you want to archive, and change the query to an append query, Access will ask you which table you want to append the data to. As the fields will match, Access will load them straight in to the archive table.

then you need a second query to delete the projects from the live table.

1. its a good idea to count the archived projects before and after the xfer, and count the expected transfers, and make sure the numbers tally before you delete the projects from the live, as you won't be able to get them back once you delete them!

2. now most posters here would probably say don't archive the projects in this way. Simply have a yes/no flag in the live table saying archived or completed, and set this to true for the archived projects. Of course that would mean changing quite a lot of your forms/queries etc to select only live projects, so its up to you.

 

[Kenln]

I agree that usually it is best to have a single table(s) with a completed flag. However I'm pulling data of an AS400 and it gets bulky, time comsuming and I need to change some data (which I cannot change on the AS400). So an archive table works best.

However doing an append query is what I was trying to avoid. Is there a way to have a query pull data from multiple tables where they act as an append but for display not to actually append to anything?

 

[Summerwind]

You might like to have a look at using an ADODB command with an INSERT INTO command text.

As you are needing to change data you could then use a further command with an UPDATE clause to make your changes.

There is a lot of help available for these two SQL commands but if you need some more help, come back

 

[Kenln]

Thank you, I see that INSERT INTO is append. I'm trying to avoid that.

My original question was poorly worded. I should not have used the word append. OOPS. I would like to display the data from two different tables without appending the information into a single table?

Thank you again,

 

[Rich]

Use a UNION query

 

[Kenln]

WOW! I didn't know you could do that.

BIG THANKS. UNION query is exactly what I need.

Thanks, I'm still learning...