Originally Posted by
Philip68
What i am attempting to do is then take the output figures from the query for, lets say, yesterday and have them shown in a column in a new table in the access database but these figures should not be overwritten when the figures in the linked tables refresh next day.
As you did mean warehouse and customers, then I assume the talk is about product deliveries (i.e. you need to store the daily history of deliveries to every customer any products). Then probably to what you need is a table like:
tblDeliveryHistory: tblDeliveryHistoryID, DeliveryDate, CustomerID, ProductID, ProductQty, ...
NB! No new columns for every day!
You create a monthly report based on query like
Code:
SELECT YEAR(DeliveryDate) AS DeliveryYear, MONTH(DeliveryDate) AS DeliveryMonth, CustomerID, ProductID, SUM(ProductQty) AS MonthlyQty
FROM tblDeliveryHistory
GROUP BY YEAR(DeliveryDate) AS DeliveryYear, MONTH(DeliveryDate) AS DeliveryMonth, CustomerID, ProductID
How to update this history table, depends on database the tables are stored at.
In case your database is unsplit, or you use Access back-end, the safest way will be to have database or back-end stored on some server, and to run there Windows Scheduled Task at some time daily, when all sources are refreshed (probably at night-time). This quarantees, that the history table will be updated, even when you havent used the database for some number of days. And you don't have to check every time you open the database, is it refreshed currently or not;
In case your database has e.g. (full version) SQL Server back-end, you simply run a daily Job there, which reads the needed info from source database(s), and updates your history table.
All this was for case, the sources you are getting daily info at don't have any history for their production. When they have it, then why you don't read it from there directly?