Haven't been able to force an error yet after some testing, so this must OK for now
The above issue is part of a little time clock database that I am doing at work. Its purpose is to keep track of which productionsorders are beeing worked on. Now.. this i work in progress and and feature requests from colleagues keep coming faster than I can implement them
So... I have another issues which might belong i another thread. I don't know. Anyhow here it comes.
Right now I have a continuous form [frm_ctn_List] which record source is a query showing production related stuff (Item no., Description, quality, costumer etc.) Clicking a specific production order no. opens up form [frm_ctn_TidsSkemaPanelfilter]. The production order no. is passed to this form. This form has table [tbl_TidsTabel] as record source. When the employee fill out this form they "Start" the job and select employee from a combo box. The information is saved in the table, and when the form is closed an append query updates a Yes/No field in a status table [frm_ctn_Status] to Yes, indicating that the job has started. Conditional formatting colours a textbox based on this yes/No field. From the form [frm_ctn_TidsSkemaPanelfilter] the employee open form [frm_ctn_Status] from where they can complete/finish the job. This is a Yes/No check box which filter out this specific productionorder from the continuous form [frm_ctn_List].
So to sum up... as of now the employee can start and stop a production order job. When the job is started by any employee a box turn blue indication the job is started. When finished the job disappear from the form [frm_ctn_List]. That's it!
Now I would like make visible (live) which employee are working on which order. An order can be started but that doesn't mean that anybody is working on it. They could have stopped the job and started another one. This isnt visible at the moment.
I cant figure out if I have enough information in my table [tbl_TidsTabel] to accomplish this.
A record is added to this table [tbl_TidsTabel] every time a employee start or stop a job. So start and stop aren't stored i the same record.
Well that became a little longer than intended. Hopes it makes sense.
Yes, it looks like you haven't encountered incorrect results just through luck!
For instance, with the record where No_ = 570258 and antalemnerproduceret = '800', if you were to change '800' to '80', see if your DMax() formula gives the result you expect.
It would be worth trying to get the revised DMax() expression in post #21 working.
I have 3 records with the same production order number. If I change for example 800 to 80, the value on the form change to 700, which is the correct behaviour.
I know what you mean
So know that you are fueled up with coffee maybe you could guide me in a query issue.
I have a reservation table "dbo_KonfAir DRIFT$Reservation Entry" from where I need to filter out.
Entry No_: need to be distinct but is shown in pairs
Source Type "37" and "5406" needs to be in separate columns
The corresponding "Source ID also needs to be in separate columns
I also need the describtion field but the values here are always identical.
See attached image. Top is source - Bottom result
I have something like this but it doesn't seem to work
Code:
select
tabA.Entry_NO,
tabA.Reservation,
tabA.[Source Type] as "Source Type_ProductionOrder],
tabB.[Source Type" as "Source Type_SalesOrder],
tabA.[Source ID" as "Source ID_ProductionOrder],
tabB."Source ID" as "Source ID_SalesOrder"
from
[dbo_KonfAir DRIFTSReservation Entry] tabA,
[dbo_KonfAir DRIFTSReservation Entry] tabB
where
tabA.Entry_NO_ = B.Entry_NO_
and tabA.Entry_NO_ = 33206611
and tabA."Source Type" = 37
and tabB."Source Type" = 5406
The sql below give an error. Image attached.
Maybe the sql is wrong build compared to what I would like to accomplish (Try looking at the attached image Query.png)
"Entry No_" come in pairs in this table. The information in the 2 paired records should be populated in to one record for each pair.
The criteria is that "Source Type" is either "37" or "5406"
Hope it makes sense
Now I think I need a coffee refill
Code:
SELECT
tabA.Entry No_,
tabA.Reservation,
tabA.[Source Type] as [Source Type_ProductionOrder],
tabB.[Source Type] as [Source Type_SalesOrder],
tabA.[Source ID] as [Source ID_ProductionOrder],
tabB.[Source ID] as [Source ID_SalesOrder]
FROM
[dbo_KonfAir DRIFTSReservation Entry] tabA,
[dbo_KonfAir DRIFTSReservation Entry] tabB
WHERE tabA.Entry No_ = B.Entry No_
and tabA.Entry No_ = 33206611
and tabA.[Source Type] = 37
and tabB.[Source Type] = 5406
;
[Entry No_] always comes in pairs and the corresponding [Item No_] will also be the same 2 values... But a specific value in [Item No_] can be represended multiple times.
Actually it’s the other way around
37 is the sales order table number and
5404 is the production order table number.
That’s always stay the same.
The reason for this query is to find which production order comes from which sales order.
The only place in our ERP system where they are linked together is in this reservation table.
Hope this makes sense
In the ERP system Microsoft Dynamics NAV we can order track our production orders but when used as linked tables in Access I have to make the connection in this query