Is the INSERT INTO Query best to use to Add To (not just append) XLS ??

[kev100 via AccessMonster.com]

I've just learned a little about the INSERT INTO Query.

I'm needing to do the following and was wondering if it would be the best
function to try:

I have an existing Excel Spread sheet with a line for each employee (around
250 different Employee)

The unique identifier for each name is the Employee ID.

Throughout the month, sales figures for each employee will begin to trickle
in....20-30 one day, 15-20 the next, etc.

There are 5 different items for each employee (there is a column for each
item - 5 columns total)

These 5 sales figure items are produced by a single query. When the query is
run, you enter which employees you want to produce the figures for, and it
displays them (a line for each employee with 5 columns)

The spreadsheet always has ALL the employees listed, in order by employee ID,
if there are figures or not.

I needed to UPDATE that Excel spread sheet with Only the data for the
Employees that I specificed when running the query.

Example:

The spreadsheet may already have the figures for employees 2,4,8,120,134
(produced on a previous day)

I run the query later for only employees 15,56,90, 150.

The Excel spreadsheet needs to update with the figures for those employees
without disturbing the figures for the existing employees (which were
produced on a previous day)...so it needs to be able to "insert" data at
different points within the spreadsheet....not just add it to the bottom.

So....there has to be some function which will match the Employee ID in the
Query results to the correct line (and cells) of the Employee ID in the
Spreadsheet.

Can an INSERT INTO Query do this?

ALSO....I'd read a post concerning this that mentioned Importing a query's
results FROM WITHIN an the Spreadsheet (sort of "pulling" the data)...as
opposed to Exporting it TO the spreadsheet ("pusing" the data).

Would this also be a factor/consideration as well?

Any advice appreciated.

Thanks very much.

 

[Guest]

Your best bet is to link the Excel into Access and then you can run an update
query.

 

[kev100 via AccessMonster.com]

Your best bet is to link the Excel into Access and then you can run an update
query.

I've just learned a little about the INSERT INTO Query.

[quoted text clipped - 49 lines]

Thanks very much.

The way this work is processed, that would actually be best....

But, I'm completely unfamiliar with how to match the values in the query
(which will contain the unique Employee ID) with the lines in Excel (which
will contain the Employee ID) as well as specficy which value goes in which
cell.

Is there some sort of Wizard...etc. in Excel which allows such a linkage
between the spreadsheet and the query to be created (and all the little
details specified)?

Thanks

 

[Guest]

You do know this is a discussion group for Microsoft Access a relational
database.

Just make sure the source of your update data has the Employee ID and join
the tables in the query on the Employee ID.

Your best bet is to link the Excel into Access and then you can run an update
query.

I've just learned a little about the INSERT INTO Query.

[quoted text clipped - 49 lines]

Thanks very much.

The way this work is processed, that would actually be best....

But, I'm completely unfamiliar with how to match the values in the query
(which will contain the unique Employee ID) with the lines in Excel (which
will contain the Employee ID) as well as specficy which value goes in which
cell.

Is there some sort of Wizard...etc. in Excel which allows such a linkage
between the spreadsheet and the query to be created (and all the little
details specified)?

Thanks

 

[kev100 via AccessMonster.com]

You do know this is a discussion group for Microsoft Access a relational database.

Yes, the data exists IN a Microsoft Access relational database - I'm was just
needing a little help knowing the best way to get it to a spreadsheet.

Thanks very much for the info....I just realized one issue, however....

If I understand this correctly...I'd need to set up a link between the Excel
SS and Access in the Access MDB?

If so...that may not work....or may be awkward....in that there will be
several versions of these spreadsheets each month with slightly different
names (but they will otherwise have an identical structure).

If the link between the Access query and the ss must reside in the Access mdb.
....then there would need to be a link created for each ss (?)

If the link is created and stored within the spreadsheet...then it may work
fine. Hopefully, that is the case.

Thanks very much.