I need your help in the following:
I have a function that read date from machines, for how many parts the machine did according to date and time. e.g: in 18/8/04,11:02:04 parts=200
in 18/8/04,15:02:04 parts=600,in 19/8/04,11:02:04 parts=1000
I need to build query that tell me how many parts the machine did until now from the beginning, also to get how many parts it did between to days or two hours.
please help me
I can only give you a hint. You should consider converting the Dates to doubles where the integer is the date and the fraction is the time. (Otherwise, you would have to tinker with the Time format of the Regional settings of the control panel). Then you can calculate the formula and re-convert each segment into date and time as needed. If you wish help, upload a zipped sample db with a few records, in Access 2000 format, and I will create the query for you.
In the attachment file you have my application.
Only, I'll tell you some explanation:
Machines table : has the machines numbers witch related to the data table.
Data table: you have all the data that I collect from the machines by the date and the hour.
I want to build a query that calculate the differernce between all the parameters according to two givven dates.
Total Parameter = Second parameter - first parameter.
Second parameter is getted according to the maximum time in the second date. The first parameter is getted according to the minimum time in the second . This way tells my how the machine did in the whole period,because maybe that I did collection between these two times.
I hope you understand me.
I looked at your table and it's not a problem but please clarify which fields do you consider the 2nd dates and 2nd times? The date and time fields are clear. Also, I can only get to it tomorrow morning, this being late at night from where I write. As for your queries, one cannot refer to forms in a query, only vice versa. So my comment to your queries are = Zeh Lo Tov.
I implemented the times as I understood it but your database, in particular the Machines main form is corrupt. Look at the special columns in Query1 which convert your Hour values to time. (Time values must be Double). I created a 2nd form called Machines2 where the Main form/Sub form works.
Thank you my friend to your reply,
I opened your replied file, but it isn't fit.
I will explain my problem clearly this time:
My data table consist of (Date,Time,Good_Parts,...)
The date and the time fields tell us when the information have gotten to the table.
My table looks like :
Date time Good_Parts
22/8/04 10:05:02 200
21/8/04 9:05:02 500
20/8/04 5:05:02 1000
I want to make a query that calculates, how many parts do we have from first day to second day.
The problem is that maybe in a certain day we have more than on reading.
So, to get the right difference,we have to take the latest number in the second date minus the early number of the first day.
How I can do a query that has also day and time to make the right calculation.
Thanks a lot