Updateable Reference Table (1 Viewer)

smeltzjg

New member
Local time
Today, 14:32
Joined
Aug 16, 2016
Messages
2
I have several tables that all have the same column headers. I understand normalization and the need to have one "main" table that differentiates between what the data pertains to with an additional column.

Each week I need to update my table with an additional few hundred rows.

I'm sure this is not the best way to do this but as of now I converted all of the data to one large Excel file and update it in there using an Excel macro. Once all of the rows are there I load it into Access via a linked table. I imagine there being an easier way to do this. My excel file is growing too big and slow and it doesn't serve the purpose I am looking for from Access.

My questions:
- what is the easiest way to update from an excel file to an existing access table? append query, linked table, etc.
- I don't fully understand the capabilities of Access Macros but could one be used in this situation?

Any response would be GREATLY appreciated.

Thank you!
 

Minty

AWF VIP
Local time
Today, 18:32
Joined
Jul 26, 2013
Messages
10,355
Some examples of your data and its structure , along with the fields in the spreadsheet would assist in providing an answer.
Generally an append query from a linked table will be the best assuming you can identify the records in the linked table as being unique to import.
 

smeltzjg

New member
Local time
Today, 14:32
Joined
Aug 16, 2016
Messages
2
To simplify:

The Main file in the database is now linked to includes:
Employee ID, Date, City, Revenue, Quarter and Year

These are across several years now. I use the Quarter and Year column to differentiate between the reports. They are now one table.

Each week I add the additional rows but I do all of this in excel.

I would love to get it to a point where I can not have to rely on excel macros and just use a navigation form to hit an "Update Reference Data" button.

I am thinking that is not possible.
 

Users who are viewing this thread

Top Bottom