I'm in need of some help in designing a solution to my problem.
What i want to do is take some data from a ERP system into Access (by way of linked tables and queries) so that I have a view of all the relevant transactiona data.
I then need to add additional fields onto this data. These fields will be manually populated and need a user friendly front end (e.g. excel) for this task.
I then want to have a table of data (transactional and manually entered) for each record that I can then link into other tables on the ERP database (probably via access linked tables) and build reports in excel.
The tools I have are Office (i.e. Access, Excel etc).
Any help or suggestions would be greatly appreciated.
Here's a solution. In my example, the source table 'dbo_CF_Data' is a linked (attached) table in an Access database (the original data reside on a SQL Server). The Identity column (primary key) for this table is named 'SysCounter' (Long numeric, seen as "AutoNumber" in Access)
In the same Access database, we create a (local) table: 'Tbl_dbo_CF_Data_Extra' which also have a column named 'SysCounter' of type Number (Long Integer), Indexed: Yes (No Duplicates). A second column 'Additional_Column' will be used to store the additional data for the source table.
The evident solution to link both tables would be to create a relationship and to enforce referencial integrity. Unfortunately, this is not possible for a linked table. We then need queries to synchronize the rows of both tables (i.e. to always have one and only one row in the local table for each row in the attached table).
The first query ('Insert_Into_Tbl_dbo_CF_Data_Extra') will be used to create new rows into the local table:
INSERT INTO Tbl_dbo_CF_Data_Extra ( SysCounter )
FROM dbo_CF_DATA LEFT JOIN
Tbl_dbo_CF_Data_Extra ON dbo_CF_DATA.SysCounter=Tbl_dbo_CF_Data_Extra.SysCounter
WHERE Tbl_dbo_CF_Data_Extra.SysCounter Is Null;
The second query ('Delete_From_Tbl_dbo_CF_Data_Extra') will remove the rows in the local table ('Tbl_dbo_CF_Data_Extra') that no longer exist in the source table ('dbo_CF_DATA')
DELETE FROM Tbl_dbo_CF_Data_Extra
WHERE Tbl_dbo_CF_Data_Extra.SysCounter IN (
FROM Tbl_dbo_CF_Data_Extra LEFT JOIN
dbo_CF_DATA ON Tbl_dbo_CF_Data_Extra.SysCounter = dbo_CF_DATA.SysCounter
WHERE dbo_CF_DATA.SysCounter Is Null
Depending on how often rows are added or deleted in the source table, you can chose to run both queries when the database is open ('AutoExec' macro), and/or use a timer to periodically "refresh" the local table.