Updating Access Report using Excel

[somanyusernamesaretakenal]

What I am trying to achieve:
Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data)

What I did was 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) and a link table was automatically generated.

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.

Many people have suggested that I import the query (used to generate the report) into excel instead of the report itself. It would be much easier – and it worked. But the problem is that the excel file has to look like the report on access. If I import the query into excel, it would look like a table in excel.

Although, it didn’t work, but the excel file derived from the imported report is the format and design I need.

I tried to change the report source to the link table. And it triggered another problem. Since the link table looks exactly like the excel file, but it is in an access table, there are a lot of empty spaces and when I ran the report (the source is now the link table), a lot of information went missing.

Questions:
The new data inputted to the excel file has to show up on the report in access. So what could I do to make it work?

I have scans but I don't know how to attach it. Tried to use insert image, but it did't work.

Thanks in Advance!

[somanyusernamesaretakenal]

What I am trying to achieve:
Basically I have generated a report in access. This report needs to be updated using excel. (Updating the new data, not changing existing data)

What I did was 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) and a link table was automatically generated.

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.

Many people have suggested that I import the query (used to generate the report) into excel instead of the report itself. It would be much easier – and it worked. But the problem is that the excel file has to look like the report on access. If I import the query into excel, it would look like a table in excel.

Although, it didn’t work, but the excel file derived from the imported report is the format and design I need.

I tried to change the report source to the link table. And it triggered another problem. Since the link table looks exactly like the excel file, but it is in an access table, there are a lot of empty spaces and when I ran the report (the source is now the link table), a lot of information went missing.

Questions:
The new data inputted to the excel file has to show up on the report in access. So what could I do to make it work?

I have scans but I don't know how to attach it. Tried to use insert image, but it did't work.

Thanks in Advance!

I am using Access 2003, I forgot to mention.

[NeoPa]

I would never input data from an Excel spreadsheet that had been created from an Access report.
It may not seem helpful at the moment to you, but my advice is don't do it.

[somanyusernamesaretakenal]

Thanks for the reply.
I spent like 3 days trying to do it, but couldn't get it to work.

It is not my choice to do it or not to it.

Something just came to mind, since on this entire report, I only need to update 1 field. Could I first import the entire report on to excel, but only link the report to the field that needs to be updated. [And freeze or lock the rest of the fields.]

Then I would link the report to excel. Then I would run a query on the link table, and pull only the field that has been updated. And somehow link this query to the report. But how to make sure the values match the record on the report?

I just started access couple days ago, I have no access logic. Is this do-able? Would there be a lot of manual labour?

Thanks in advance.

[NeoPa]

It is not my choice to do it or not to it.

I do sympathise. IT is an area where you're too likely to be interfacing with users who can't grasp (searching for the right words here...) anything!
My advice, continue as you are, trying to oblige where possible, but work on suggesting alternative solutions which are better supported by the tools. If you're lucky and can find one, start educating one of your users to problems of doing things the 'wrong' way. This depends on their being open to such education.

Something just came to mind, since on this entire report, I only need to update 1 field. Could I first import the entire report on to excel, but only link the report to the field that needs to be updated. [And freeze or lock the rest of the fields.]

Then I would link the report to excel. Then I would run a query on the link table, and pull only the field that has been updated. And somehow link this query to the report. But how to make sure the values match the record on the report?

I just started access couple days ago, I have no access logic. Is this do-able? Would there be a lot of manual labour?

Thanks in advance.

This is where life becomes difficult. Linking to Excel is a great little facility, but really expects the data in simple columns. Your data may fit that layout, in which case you should be able to get what you want from it. If not, that is the sort of complicated issue that is hard to assist on remotely.
In Access you can run a query to append the data you want from Excel into an Access table. This is not absolutely necessary but should make life easier for you, especially in the area of finding problems.
Without really know much about your actual data I can only say that from there you should have the tools necessary to update your data (that the report runs off) or alternatively, introduce it into your reports query.

If you need more help then you will need to bit more specific with what you're working with (record layouts; field names and types; etc).