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

    Question Unanswered: Updating Report via Excel

    I am trying to update an existing report using excel. I imported the existing report into Excel (Tools – Office Links – Analyze It with MS Excel). Then I linked the report to the Excel file. (File – Get External Data – Link tables) - a link table was inserted into my database.

    I wanted to test the link, so I inputted new data into the excel file and saved it. Then I opened my report in access; the new data I inputted in Excel was not there. But the new inputted data did show up however in the “link table”, but I wanted the new inputted data to show up on my report.

    Anyone knows how to solve or do this? Am I missing a step in between?

    Thanks in advance.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is the record source of the report? Nothing you described changed the record source from whatever it was to the newly linked Excel file.
    Paul

  3. #3
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by pbaldy
    What is the record source of the report? Nothing you described changed the record source from whatever it was to the newly linked Excel file.
    Record source (I am still new to Access, I hope this is what you are asking)
    Main Table -> Crosstab (I had to do something sum calculations on a yearly basis) -> I did a left join query (a query that contains all the possibilities left joined to the Crosstab) to ensure the crosstab has all the possibilities listed. -> The report is generated from this left join query.

    If my process created a problem, how do I solve it?

    Thank you
    -m

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, I wouldn't say it's created a problem but you've basically got:

    Table>Query>Report>Excel file>new table

    The report still looks at the original data, so the changes you made to the Excel file will never show up in your report. I don't know the overall situation or the "business problem" you're trying to solve, but from the sound of it, you probably need to get those changes into the original table. You could base the report on the Excel file, but it doesn't exist until you've run the report, right? I think more info about the overall goal here would help.
    Paul

  5. #5
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by pbaldy
    Well, I wouldn't say it's created a problem but you've basically got:

    Table>Query>Report>Excel file>new table

    The report still looks at the original data, so the changes you made to the Excel file will never show up in your report. I don't know the overall situation or the "business problem" you're trying to solve, but from the sound of it, you probably need to get those changes into the original table. You could base the report on the Excel file, but it doesn't exist until you've run the report, right? I think more info about the overall goal here would help.
    The Goal:
    This report I created need to be updated, not changing the existing data on the Report, but get new data. Let's say I am trying to get estimated values for yr 2100 from the people. These people do not have access, so the report has to be imported into excel and then (link) back into the same report after it is updated with the new data. The updated data has to show on the report.

    The reason why I didn't import the main table or the orginial data is that this report is summation of let's say 200 fields into 20. The people only can provide data for the 20, not all 200 (dont have that specific details). Compressing the number of the fields is like one of the main purposes of doing this report.

    I think I know the problem, but I don't know how to solve it.
    Like when you import a report into excel, the formatting in excel looks very Ugly!!! There are lot of empty spaces in diffferent columns. The link table that's generate from the link between the report and the excel file looks exactly the same. When I try to generate a report from the "link table", I am missing a lot of information. The only thing that is being updated is the link table only. I need to somehow connect the link table to the existing report.

    I hope that clarify...a little bit.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I've had better luck exporting a report's source query to Excel than the report itself. As you've experienced, that's often a mess. Try exporting the query instead.

    Connecting the report to the linked table would be easy, but then how do you run it the first time (since there's no Excel file yet)? Maybe a solution would be to have a second report based on the Excel file?
    Paul

  7. #7
    Join Date
    Nov 2006
    Posts
    67
    [QUOTE=pbaldy]I've had better luck exporting a report's source query to Excel than the report itself. As you've experienced, that's often a mess. Try exporting the query instead.

    Connecting the report to the linked table would be easy, but then how do you run it the first time (since there's no Excel file yet)? Maybe a solution would be to have a second report based on the Excel file?[/QUOTE]

    First of all, I want to thank you for your replies. I am still new to access, so bear with me a little longer please.

    I am not 100% certain yet, but I don't think I could import the query, I have to import the report, sadly.

    Maybe there is a misunderstanding, if we go back to the scenario of importing the report into excel. I have data for let's say yr2005, but zeros for 2006 and 2007. I could just replace the zeros with random values into those 2006/07 columns and lock my existing data from being changed (2005). So there is sample excel file.

    Connecting the report to the linked table would be easy, but then how do you run it the first time (since there's no Excel file yet)? Maybe a solution would be to have a second report based on the Excel file?[/QUOTE]

    Let's say I have the updated excel file, how do I link or connect the link table with the existing report?
    Also if the link table is derived from the report and not the query, the report generated from the excel file (i think you mean the link table?) would not be accurate because of the problem I mentioned above.

    Thanks

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    My point on exporting the query rather than the report is that when you export a report, you often see all kinds of gaps and empty columns that don't seem to make sense. When you export the query, you should get the same data but in a more sensible format.

    In any case, to connect the report to the linked table, go into its properties and change the record source to the linked table. Of course, the field names would have to match up.
    Paul

  9. #9
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by pbaldy
    My point on exporting the query rather than the report is that when you export a report, you often see all kinds of gaps and empty columns that don't seem to make sense. When you export the query, you should get the same data but in a more sensible format.

    Yes, I agree with you.

    In any case, to connect the report to the linked table, go into its properties and change the record source to the linked table. Of course, the field names would have to match up.
    Yes that worked.

    Just say, the linking issue is solved.

    Different question:
    The (main) excel file will have like 200 different customers. I cannot sent the entire excel file to every one of the 200 customers - the data need to be broken down into individual customers. Privacy issue. The only way I know how to do this is to do a lot of copying and pasting and generate 200 excel files. (breaking the main excel file into 200 excel files). After the customers updated the excel files, I will basically do copy and paste the updated information into the main excel file.

    This is definitely one of the stupidest way of doing it, I was wondering if there is a much efficient way of doing this?

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would probably do what I did for my wife's employer. They were selecting a customer on a form, running a process to export a query to Excel, then manually creating and sending an email, attaching that file. They then repeated the process for each customer. It literally took all day.

    I wrote a little thing that loops through all the customers, exports each customer's data to Excel and sends the email, all automatically. Takes about 20 minutes, and most of that is because their system is slow and the queries take 10-20 seconds to run.

    I say that to tell you what may be possible. It would require a decent knowledge of VBA and recordsets to pull off.
    Paul

  11. #11
    Join Date
    Nov 2006
    Posts
    67
    Quote Originally Posted by pbaldy
    I wrote a little thing that loops through all the customers, exports each customer's data to Excel and sends the email, all automatically. Takes about 20 minutes, and most of that is because their system is slow and the queries take 10-20 seconds to run.

    I say that to tell you what may be possible. It would require a decent knowledge of VBA and recordsets to pull off.
    Is it something I could master in a week? Or is that just wishful thinking?

    My final instructions were finalized. As long as I could get the excel file to look somewhat exactly(close) like the report, and the new data entered in excel shows up on the report, I am fine. [It almost like copying the report and pasting in excel] The process of doing it is not important.

    Now, I know you have emphasized many times that importing the report is a bad way of doing it. But in the end, if I still have to import the report into excel and have the ugly formatting. I know it is hard to fix, but how should I approach this problem - fixing it.

    Attached below: the report and the excel file. (maybe it could help you a bit):
    This is an old file - but the structure of the template is somewhat the same. The new file, I just have more groups and sub groups.
    Attached Files Attached Files
    Last edited by tialongz; 12-07-06 at 10:36.

Posting Permissions

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