Data extraction

[aronw]

Hi, this might be a simple bogal but, I created a query that selects some records bases on 3 fields. (I have a number of fields in a number of tables. All my data is linked together via Accesses very own ID auto-number PK.)

Out of my 75,000 odd records 251 pop up fitting my criteria. Now, I wish to copy those records and paste them into another DB. (The of DB is just a backup, so tables and fields are the same).

If I highlight the records in my 3 fields query, will all the other connected data (record) be copied as well?

Can anyone suggest a better method if this is not cricket?

TIA

Aron

 

[Ally]

When you copy query results, only those results will be pasted.

Rather than copying and pasting - (I'm not sure how you will actually "paste" into another database) - you can export via a macro using TransferText and in the properties using Export Delimited. This converts the results to a text file and you then need to import it via opening your other database.

Alternatively, and this is probably easier, you can make your query into a Make Table query and then import your new table (or just copy and paste) into your other database.

HTH

 

[aronw]

does Access link the fields?

The second option sounds much more like my Access skill level.
Thanks, there are about 20 fields attached to each record spread over 3 tables. If I created a query which I have done, would it need to include all the fields? Or would Access link the relevant data to the records?
Does the same apply to 'Pasting' for want of a better word into the other DB?

Ideally I wish to lift these fields that correspond to the records and overlay them in the same place in an identical DB. Any help would be greatly appreciated,

TIA

Aron

 

[Ally]

You only need to include those fields that you wish to view in the results of the query or those that are required to enter criteria (which don't need be visible in the results, which you can do by unchecking the "Show" tick box) in the query design.

If you want the results in another database, why don't you link the tables? In your second database you just need to go to File > Get External Data > Link Tables and choose the database and table(s) you require. Then you can set the query up in that database as a make table query, which will always overwrite the original - I presume that's what you meant by "overlay" the records.

You will get dialogue boxes come up with, "You are about to run a make-table query. This will overwrite" etc etc. You can turn these warnings off when you're confident it's doing exactly what you want by running the via a macro.

 

[aronw]

Some of the users on the DB have been working on the backup version of the same DB whilst others have correctly been using the original copy. Here lies my problem, I don't want to overwrite the work that has been done on either DB. Simply update a few hundred records.

 

[Ally]

Well it sounds like you need an append (append records to an existing table) or update (update records that are already there) query then. When you said "overlay" I thought you meant overwrite. Now I'm not entirely sure exactly what you'd like to do!

 

[aronw]

Just wanted to know if by creating a simple query i could get / copy all the details attached to a record even if they are not displayed in the query. Thanks for the help, I now have a number of methods to exprienment with. If they don't work I will be back.

Aron

 

[neileg]

Queries work on a WYSIWYG basis. There is no additional data that isn't displayed other than the reults you don't show (unselect the show tickbox in the design pane).