Manualss to Append Query's

[Schwimms]

Hi,

I am in the need of a manual I can use to help me build queries from files
that I update. For example: I have a historical file in Access, I then also
have a file that is linked from outside of access that I drop into a folder.
I clean up this file in access and give it the same headers that the
historical has, I then want only the new entries to be added into the
historical and I beleive this happens from an append query. I then have a
excel file outside of access that is linked. I want to be able to just drop
in the new data into the folder, DONT go to access, open the excel file that
has the linked access, and hit refresh, thus showing me the historical and
the new data.

 

[John W. Vinson]

Hi,

I am in the need of a manual I can use to help me build queries from files
that I update. For example: I have a historical file in Access, I then also
have a file that is linked from outside of access that I drop into a folder.
I clean up this file in access and give it the same headers that the
historical has, I then want only the new entries to be added into the
historical and I beleive this happens from an append query. I then have a
excel file outside of access that is linked. I want to be able to just drop
in the new data into the folder, DONT go to access, open the excel file that
has the linked access, and hit refresh, thus showing me the historical and
the new data.

I'm confused.

You want to have Access import the data from this external (text??) file, and
make it available to Excel... without ever using Access???

I suppose you could write VBA code in an Excel macro to open Access in the
background, run the import/append, and then close Access... but you can't
avoid updating the Access database!

 

[Schwimms]

I didn't realize you couldn't import appended queries into excel. I think my
head hurts... Time to break, thanks for your response John.

 

[John W. Vinson]

I didn't realize you couldn't import appended queries into excel. I think my
head hurts... Time to break, thanks for your response John.

You can't import an Append query... but you can import a Select query. Sorry
if I was misleading, and have an aspirin (or ibuprofen, or good cold beer) on
me!

 

[Schwimms]

Ok, one more chance. I have the table's built with historical and current
data and I have the appended query set to append on DMAX"Shipped date", Can I
create another query that has those two combined, then go into excel and
import that file.

This would allow me to just drop the current data into a folder that access
see's, Go to my excel file that imports the cleaned up data in access, and
everything is perfect. Cause we live in a perfect world?

 

[John W. Vinson]

Ok, one more chance. I have the table's built with historical and current
data and I have the appended query set to append on DMAX"Shipped date", Can I
create another query that has those two combined, then go into excel and
import that file.

This would allow me to just drop the current data into a folder that access
see's, Go to my excel file that imports the cleaned up data in access, and
everything is perfect. Cause we live in a perfect world?

If the two tables are of the same structure (same number of fields of matching
datatypes) you can create a UNION query that will stitch the two tables
together "end to end", showing all the historical and all the current records;
that UNION query can be exported to Excel.

A UNION query must be created in the SQL window; see the online help for the
term, it's pretty good.

 

[Schwimms]

John,

I cannot import this Union query from excel, is there a way to import it in
when I am in Excel?

 

[John W. Vinson]

John,

I cannot import this Union query from excel, is there a way to import it in
when I am in Excel?

I expect so, but I'm no sort of expert on Excel or Access-Excel interaction.
Maybe you should repost with a subject like "Access UNION queries and
TransferSpreadsheet" with an indication of what you've tried and what happened
- perhaps both here and in an Excel group.