Single form

[June7]

Yes, post #6.

The queries worked for me with the db provided. What happened - error message, wrong result, nothing?

I did forget something in the second query. Here is revision:

SELECT Query1.*, tblRecProcess.ProcessedOn, tblRecProcess.ProcessedBy, Not [Process_FK] Is Null AS Required
FROM tblRecProcess RIGHT JOIN Query1 ON (tblRecProcess.Process_FK = Query1.Process_PK) AND (tblRecProcess.OrderID_FK = Query1.OrderID_PK)
ORDER BY Query1.OrderID_PK, tblRecProcess.Process_PK;

 

[deletedT]

Yes, post #6.

The queries worked for me with the db provided. What happened - error message, wrong result, nothing?

I did forget something in the second query. Here is revision:

SELECT Query1.*, tblRecProcess.Process_FK, tblRecProcess.ProcessedOn, tblRecProcess.ProcessedBy, Not [Process_FK] Is Null AS Required
FROM tblRecProcess RIGHT JOIN Query1 ON (tblRecProcess.Process_FK = Query1.Process_PK) AND (tblRecProcess.OrderID_FK = Query1.OrderID_PK);

Million thanks for your effort.
I edited above post too.

No error at all. it worked. But your last warning Be aware that Cartesian query can perform slowly with very large dataset. made me hesitate to use it. As I explained later, performance is a main factor here and maybe I better use denormalized tables.

I'll accept that query as the correct answer if there's no other suggestion.

 

[deletedT]

I will use the queries suggested by June7 in #6 & #21 posts for a while to see the difference in performance.

Million thanks to June7 and Plog for putting their time on this and sharing their knowledge and experience.

 

[June7]

The only alternative I can see is UNBOUND controls and code fills data into textboxes. Code could be VBA looping recordset or DLookup() expression in ControlSource property. Domain aggregates can also perform slowly when large dataset involved.

 

[deletedT]

June7, I appreciate for any further advice.

I used your queries, and add a count() field to query2 to show if a process is necessary or not. If you open frmOrders and move through the records, you will see that I have a single form for each order and a continuous form at right to show a list of all process and who has done what.
I added a checkbox to show if that process is necessary for manufacturing that part or not.

Everything is working perfect. Just as I have asked.
The only problem is that my main tables have about half a million records and adding everyday. Using Count() function makes a delay when I move through records.

Is it possible to have the same result by using a join in the queries? I prefer to remove the count() field and use a join in the query to speed up the result. A simplified copy of the database is attached.

Thanks for any kind of advice.

 

[June7]

I sorta lost track of this thread. Did you want an editable dataset on form? I don't see how can do a Count() in query and still have an editable dataset.

 

[deletedT]

I sorta lost track of this thread. Did you want an editable dataset on form? I don't see how can do a Count() in query and still have an editable dataset.

No, I don't need the query be editable. It's only used to show the current situation of the current record. I need a checkbox to show if that process is necessary or not. (for manufacturing that order)

Open frmorders.
DrawingNo for this record is Part1. DrawingNo for the first record is Part1(Master_PK=1)
in the subform the checkboxes shows if any Process_FK for this part and that process is saved in tblProcesses_Master.

In fact a boolean does the same. I couldn't manage to add a boolean field to your queries. So I used a count() function and used it as a boolean.


Thanks again.

 

[June7]

Seemed to have changed table/field names so have to relearn your db some .

If you don't want form editable then why have combobox? Why have ProcessedBy and ProcessedOn boxes that are empty?

I did not fully understand what made a process 'necessary'. Apparently multiple records must be considered to determine this status, hence the DCount() expression you used. This does seem to be working. Alternative would be an aggregate query that does a Count() then join that query to Query1. This might be faster than DCount().

Query: CntProc

SELECT tblProcesses_Master.Master_FK, tblProcesses_Master.Process_FK, Count(tblProcesses_Master.MasterProcesses_PK) AS CountProc
FROM tblProcesses_Master
GROUP BY tblProcesses_Master.Master_FK, tblProcesses_Master.Process_FK;

Query2:
SELECT Query1.*, tblProcesses_Receiption.ProcessedOn, tblProcesses_Receiption.ProcessedBy, Nz([CountProc],0) AS Expr1
FROM CntProcess RIGHT JOIN (tblProcesses_Receiption RIGHT JOIN Query1 ON (tblProcesses_Receiption.[Receiption_FK] = Query1.Receiption_PK) AND (tblProcesses_Receiption.Process_FK = Query1.Process_PK)) ON (CntProcess.Process_FK = Query1.Process_PK) AND (CntProcess.Master_FK = Query1.Master_FK);

 

[deletedT]

Sorry for the change of tables and fields name. I'm away from my desk now. I go back to the office as soon as possible to see how it works.

Million thanks for your time and help.

 

[June7]

Even if the aggregate query is somewhat faster, a Cartesian query is still involved and this will likely be source of speed issues.

 

[deletedT]

June7, It's working. Not that fast. But much better than DCount().


Million thanks for your help.

 

[June7]

Should a check mark mean process is required or process is completed?

 

[deletedT]

Should a check mark mean process is required or process is completed?

Is required.

It means there’s a record for that part and that process in tblProcesses_Master.

Thank you.