Create new static field in table

[Philip68]

Hi - i need some help. I have created a query based on a number of linked tables which shows the production output for a number of customers in our warehouse. The linked tables are based on daily figures so the result of the query is the output for just that particular day. The day after when a new set of figures is uploaded to the linked tables means that the figures in the query changes. 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. I want to compile the figures on a daily basis in this new table for each day of the month. How do i do this? I hope i have explained this clearly
I appreciate your time in helping me solve this

[Welshgasman]

Update a table with the result of the query each day.?

[orange]

Philip68,

Can you show us some sample data -and the SQL of your query? My understanding is you want each day's production stored as a column in a table?? Strange in my view, but we need more info on "production output for a number of customers in our warehouse". How many Customers/Products etc for context. Please provide a structure for your proposed output.
Have you considered saving a copy of your database each day, since you don't want to overwrite the daily values?

[Micron]

I'm with WGM. If you want any kind of history, you append to a table for each period. The details in the post suggest to me that this is Excel thinking - putting data in columns rather than rows. The query uses a date field to return whichever period you want to see.

[Philip68]

Thanks and here are a few more details
The query created gives the following results per customer for the 1st april.
If the data in the linked tables is refreshed (these tables include working hours and number of orders processed in production) then the figures in the date column (which is hours/orders) will be replaced by figures for 2nd april (just showing a few of the rows in the query output.

Project	Customer name	Number of orders	Activity code	Sum of time	Date	KPI Order
001	Customer 1	595	500	13,10	2024-04-01	45,41
011	Customer 2	1667	500	17,03	2024-04-01	97,89
018	Customer 3	28	500	1,70	2024-04-01	16,43
021	Customer 4	5	500	0,15	2024-04-01	33,33
041	Customer 5	40	500	3,52	2024-04-01	11,36
046	Customer 6	22	500	0,83	2024-04-01	26,50

Now what i want is for the first 2 columns and the final column to make up the table that will finally compile the KPI figures for each day of the month without having to cut and paste every day.

Something like this (see below)but my problem is that the figures in column 3 (2024-04-01) will refresh once the linked tables are refreshed with new data for 2024-04-02

Project	Customer name	2024-04-01
001	Customer 1	45,41
011	Customer 2	97,89
018	Customer 3	16,43
021	Customer 4	33,33

How do i keep the data in the 3rd column as raw data and at the same time be able to update the table with the refreshed data for the 2nd april with that being entered in a new column?
There are too many customers to be able to use the customers as columns
There is probably a simple solution to this but i am unfortunately no expert in MS A so have become a little stuck
Is copy and paste the only solution?

[Welshgasman]

You would insert whatever fields you want to keep track of into let's call it a KPI table.
Then run a query that would group and sum per day, to get the values you want.

[mike60smart]

Hi
Are you able to upload a copy of the database with some sample data?

[June7]

So you when you say 'refresh', data is replaced not appended in the linked database? That seems odd. What is that database? If that database is not maintaining history, your only option is to save data to a local table each month. However, aggregate data is not 'raw' data. If you want detail records then have to import raw data to local table and build queries with it.

[Micron]

To me it seems you're going about this all wrong. If you can retrieve what you want with a query for yesterday, you can do it for tomorrow as long as the data for tomorrow is there - as it should be. You append to the source table on a daily basis, not create new tables/databases for those periods. Nor do you aggregate data for periods and then store those calculations anywhere.

[ArviLaanemets]

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

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?