Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2006
    Posts
    67

    Unanswered: Exporting, importing back and forth

    General Goal:
    I have to export a report from access to excel, then in excel, data are being inputted into the excel file, after that's done, the updated excel file is going to be imported back to access. Somehow I need to make the new inputted data show up on the report. (I am trying to update the report, but the people I am getting the information from, does not have access, they only have excel.)

    Failed Attempt: I first imported the report into excel - using analyzing with excel function. Then I linked the excel worksheet (file) to the report. I entered some new data into the excel file and saved it, but when I ran my report, the new data didn't show up.

    How should I approach this problem and solve it? I am trying to get the new inputted data in excel to show up on the access report.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What does your table structure look like? There's about seventeen million ways to create a "report"...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Report Format:
    [It somewhat looks like an annual report or financial report].
    On the report, I have 3 groups/headers: Industry, businesses, and products. I have the years: 2005, 2006 and 2007 as the column headings (just plain labels on the top of screen – page header). I have the data for let’s say 2005, and under yr 2006 and 2007 there are just a list of zeros listed under it.

    I need to export this report into excel, and let people update/fill in the 2006 and 2007 column in excel. The updated excel file need to be imported back into the same access report with the 2006 and 2007 data. That’s why I thought by linking the excel file to the report, the new data inputted in to the excel will automatically show up in the linked access report or is that just wishful thinking…T_T.

    Hopefully this will give a general idea…how the report looks like.

    Thanks in advance.

  4. #4
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by tialongz
    Report Format:
    [It somewhat looks like an annual report or financial report].
    On the report, I have 3 groups/headers: Industry, businesses, and products. I have the years: 2005, 2006 and 2007 as the column headings (just plain labels on the top of screen – page header). I have the data for let’s say 2005, and under yr 2006 and 2007 there are just a list of zeros listed under it.

    I need to export this report into excel, and let people update/fill in the 2006 and 2007 column in excel. The updated excel file need to be imported back into the same access report with the 2006 and 2007 data. That’s why I thought by linking the excel file to the report, the new data inputted in to the excel will automatically show up in the linked access report or is that just wishful thinking…T_T.

    Hopefully this will give a general idea…how the report looks like.

    Thanks in advance.
    I skimmed your messaged too fast, at least the first part of it.
    Table structure:
    The main table has like the usual field names, ID, industries, businesses, products, year, main cost and part cost.
    I need to have the years as the column heading, so I generated a crosstab. I need to do a left join to the crosstab (a query) because I want to list all the possibilities for each ID. The report is generated based on this query.

    Please bear with this novice explanation.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by tialongz
    I skimmed your messaged too fast, at least the first part of it.
    Table structure:
    The main table has like the usual field names, ID, industries, businesses, products, year, main cost and part cost.
    I need to have the years as the column heading, so I generated a crosstab. I need to do a left join to the crosstab (a query) because I want to list all the possibilities for each ID.

    Please bear with this novice explanation.
    That's where you're going to run into issues. Crosstab queries are inherently non-updateable because there is no logical way to update aggregated values.

    If I said "I added up an unknown number of records to get this value". Then you say, "Oh, well just add four to that total value and put it back in the database", how do you know which records are supposed to get updated? The database has the same logical issue...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by Teddy
    That's where you're going to run into issues. Crosstab queries are inherently non-updateable because there is no logical way to update aggregated values.

    If I said "I added up an unknown number of records to get this value". Then you say, "Oh, well just add four to that total value and put it back in the database", how do you know which records are supposed to get updated? The database has the same logical issue...
    Oh I see. How do I work around this problem?

    After I read your message, I thought of doing a make-up table query on the query (with the left join and crosstab). But the problem is that the existing data is subjected to changes in the future, so for updating purposes I couldn’t do that.

    Second thing I thought of was - what if I query upon that query, it is going to slow down the process, but would it work then?

    This is a novice question, I don’t have any access logic whatsoever. Could you link an excel file to a query?

    When I linked my report to the excel file, a table was inserted into my database. The changes I made to the excel file showed up in that table (I think it is called the linking table). BUT the changes didn’t show up on my report. Is because of the crosstab? If I do a query upon that crosstab would it work then?

    Thanks in advance.

  7. #7
    Join Date
    Nov 2006
    Posts
    67

    Unhappy

    So, there is no way to update an access report using excel?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would stop thinking of it as updating an access report. You're updating the TABLES that access reports on. figure out the logic required to make the underlying table contain the right data to create your report.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by Teddy
    I would stop thinking of it as updating an access report. You're updating the TABLES that access reports on. figure out the logic required to make the underlying table contain the right data to create your report.
    Ok, fine if I am updating on the tables that access reports on. How could I make the excel document look exactly the same as the report?

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Hard to say without knowing exactly how your report is being generated. You mentioned that your Access report is based on a crosstab query. It's possible you can duplicate the same crosstab by using a pivot table in excel.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •