MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

problem: I've done this in ACCESS, but not in EXCEL

  Asked By: Matilda    Date: Feb 08    Category: MS Office    Views: 1155

One of my customers wants to run a daily query against our HR Database
and extract only that day's transactions. They want to output an XLS
and then update the previous day's XLS only with the records extracted
during the current run.

Is there a function or topic in Excel which would control an update
process, from one spreadsheet to another, with an "add" function to
the destination sheet? Would the CONSOLIDATION process do this in XLS?
Thanks for any help.



4 Answers Found

Answer #1    Answered By: Kellie Bishop     Answered On: Feb 08

I'm a little confused, so a few questions..

1. is your HR Database done with Access?
2. do you want to create a new .xls file each day?
3. -- or just a new worksheet in the same workbook?
4. -- or just constantly update  the same worksheet?
5. I don't understand "update prev day's xls  with..."

Answer #2    Answered By: Mona Wagner     Answered On: Feb 08

How are you doing the extract  in Access?

It all depends on what your HR Database is. Is it an Access database, a SQL
database, Adabas or something on a mainframe?

If you cannot work out a native way of doing it in Excel, you can call an
Access routine from Excel and have it write to an Excel spreadsheet. I would
definitely keep the extraction and the update  processes separate.(Extract to
a new spreadsheet) If something happens during extraction, there will be no
updates to back out and you can just rerun the extraction.

Answer #3    Answered By: Eloise Lawrence     Answered On: Feb 08

Query to XLS - is already done.
Example: Run Monday's transactions  = XLS Master Sheet
Run Tuesday's transactions = want to merge them onto XLS Master Sheet via Macro
Run Wednesdy's transactions = merge them onto Master Sheet via Macro
Is there a way to do this? Is this any clearer?

Answer #4    Answered By: Doyle Gonzalez     Answered On: Feb 08

It is not at all clear what the master ss would look like. One possibility
is that there is a row for each day. In this case you would just use a copy
function to copy the data from the daily  sheet to the master one.

Can you give us a bit more detail of what you want to end up with (layout of
Master SS) and where you are getting the data (layout of daily SS).

Didn't find what you were looking for? Find more on problem: I've done this in ACCESS, but not in EXCEL Or get search suggestion and latest updates.