Single form (1 Viewer)

This is a simplified version of a database:

1. tblOrders is used to register incoming orders.
2. Each Order has a drawing that is registered in tblParts.
3. For manufacturing each order, there are 24 possible processes that's saved in tblPorcess.
4. Each order needs 1 or more process to be manufactured.
5. tblRecProcess keeps a list of processes for each order, who has done it and when it's been done.

I'm failing to add a single form for tblOrders, that shows details for each order, and a list of all possible (24) processes. And only the processes for that order, who and when it's been done is filled.
It means when I open this single form, I need to see this:



In this form the Red Rectangle shows the information from tblOrders and details for each record.
Blue Rectangles shows all the possible processes.
And green rectangles shows who and when each process is done.
Blank text boxes shows that processed is not done.
The checkboxes show that process is necessary or not.
One look at the above form shows that Painting, Checksheet & Spot Weld is necessary for this order, but nobody had done it.



And apparently when I move to second record this would be the result. Because saved processes in tblRecProcess is different for this record:




Note:
The above screen shots are two forms based on two tables with 1 to 1 relationship.
Based on @Plog's post #23 in response to post #21 in this post I'm trying to correct normalization in this database.
Years ago, when I was working on this database, I had no success in a 1 to many relationship to make this form work. I had to go with two tables with 1 to 1 relationship because of a time limit.

Note 2:
This form is needed per customer's request, but I'm open to any possible change that can work similar to this.


A simplified version of this database is attached if you need to see how it is. (without any form)

Any kind of advice is much appreciated.


-

Why show processes that are not needed for an order? Accomplishing the layout you show is complicated by that requirement. I very much doubt there is a simple way to do it - at least not with normalized data structure.

June7 said:

Why show processes that are not needed for an order?

Click to expand...

They want it to work like a check list.
Later a report like this will be printed and passed to the customer.
They want each order and it's details in above, and a list of all processes, which is done and which is not done (or not necessary) bellow.

By showing all processes, in one look you can say which process is necessary and has not been processed.
For example if an order needs 5 processes and only 4 of them is done, how can you guess which one has been omitted?
Look at the second image in my post above. A look at the result, shows three of the processes has not been processed.

So no matter what was ordered, which process was done and which wasn't, the format of the sheet is the same. We have more than a hundred thousand orders
of different parts per year. A look at the same format of printed paper is much easier.

Thanks for your input though.

It would be fairly simple to 'batch' create a set of tblRecProcess records for an order when that order is created. But why create records for processes that are not required for that order? This is the complication with your requirement to show all possible processes (required or not) in line with the actual process data without creating unnecessary records.

It would not be a problem to show all processes with actual data on a report without having to create extraneous records in table. It's on data entry interface where this gets difficult.

June7 said:

It would be fairly simple to 'batch' create a set of tblRecProcess records for an order when that order is created. But why create records for processes that are not required for that order?

Click to expand...

This is exactly why I was using a 1:1 relationship and didn't normalized the data. Because adding unnecessary records to tblRecProcess, will cause more than a million unnecessary records per year.

Maybe it's much easier to let this table be not normalized.

June7 said:

It would not be a problem to show all processes with actual data on a report without having to create extraneous records in table. It's on data entry interface where this gets difficult.

Click to expand...

Lets forget about the data entry for now. Just to be sure I'm understanding what you say.
Do you mean without 'batch' adding unnecessary processes to tblRecProcess?

To be more specific, an order needs only 4 processes. Can you show all processes in a form and only 4 of them being filled with who and when?
( I know I can have an un-bound form and fill them from on-current event with Dlookups. I'm looking for bound form and textboxes.)

If only 5 processes are required then there would be 5 records. Any without date/user have not be completed. There is no 'omission'.

However, consider:

Query1: utilizes Cartesian association of records - there is no JOIN clause - to create a dataset of every combination of order and process.

SELECT tblOrders.*, tblProcess.Process, tblProcess.Process_PK FROM tblProcess, tblOrders;

Query2: joins above query with tblRecProcess

SELECT Query1.*, tblRecProcess.ProcessedOn, tblRecProcess.ProcessedBy
FROM tblRecProcess RIGHT JOIN Query1 ON (tblRecProcess.Process_FK = Query1.Process_PK) AND (tblRecProcess.OrderID_FK = Query1.OrderID_PK);

Be aware that Cartesian query can perform slowly with very large dataset.

These queries are not editable and would only be useful for report, not form.

Maybe in your case non-normalizing is optimal. It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works." Either way, there can be challenges to manipulating data for a desired output. I have a db with non-normalized structure because it was easiest way to accomplish data entry and desired reporting. However, encountered need for normalized data for graphs so utilized UNION queries.

Tera,

I think uve got things a little messed up. Here is what I would do as far as your table setup:

tblOrders
===============================
Add fields DrawingNo, DrawingName
===============================
DROP tblParts completely

change tblProcess to a table with no relationships, and have it serve as a lookup table for process names

change tblUsers to a table with no relationships, and have it serve as a lookup table for user names

add field to tblRecProcess called "processNecessary" (data type boolean)

if you make these changes, you'll have only 1 relationship in your project: tblOrders (one side) and tblRecProcess (many side). also, you can easily create a single form from the table tblOrders and when you do that, the subform "tblRecProcess" will automatically be created for you. then all you have to do is position all the controls for the main form and subform in the proper places, per your desired image.

@vba_php, not clear to me why you suggest those changes.
Possibly a drawing can be associated with more than one order.
See no reason for lookup tables not to have relationships defined.
Are you suggesting records for non-required processes be created in tblRecProcess?

June7 said:

Possibly a drawing can be associated with more than one order.

Click to expand...

that is possible, but in the DB he uploaded this was not the case so I assumed.

June7 said:

See no reason for lookup tables not to have relationships defined.

Click to expand...

doing this allows the user to lookup the data using combos on the form when entering data, but technically it can be the way it is right now as well

June7 said:

Are you suggesting records for non-required processes be created in tblRecProcess?

Click to expand...

of course not, read my post again and you'll see:

add field to tblRecProcess called "processNecessary" (data type boolean)

Click to expand...

tblRecProcess is supposed to contain processes required for an order and track status of processes. There are 24 possible processes, not all are required for each order. Which are needed is most likely determined at time order is received. So still not clear to me why there should be a "processNecessary" Boolean field. If there is a record in tblRecProcess then it is required. Desire is not to create unnecessary records. And the point may that this cannot be avoided with normalized structure.

If OP goes back to non-normalized structure then tblOrders and tblRecProcess could be 1 table and lookup tblProcess would not even be needed.

June7 said:

There are 24 possible processes, not all are required for each order. So still not clear to me why there should be a "processNecessary" Boolean field. If there is a record in tblRecProcess then it is required.

Click to expand...

I don't think ur getting it June. there are multiple records in tblProcesses, and not every record is required. If you add a boolean field, this allows him to include the check marks he wants next to each record on the form. These are his words:

Blue Rectangles shows all the possible processes.


The checkboxes show that process is necessary or not.

Click to expand...

June7 said:

Desire is not to create unnecessary records.

Click to expand...

I'm not doing that, I'm adding one field only. This is the last time I'm going to post until Tera gets back to us.

These are also OP's words:

The above screen shots are two forms based on two tables with 1 to 1 relationship.
Based on @Plog's post #23 in response to post #21 in this post I'm trying to correct normalization in this database.
Years ago, when I was working on this database, I had no success in a 1 to many relationship to make this form work. I had to go with two tables with 1 to 1 relationship because of a time limit.

Click to expand...

The images show forms created with 1-to-1 related tables - tblRecProcess was not normalized - there was a field for each process therefore each record shows all 24 processes, required or not.

And these words:

This is exactly why I was using a 1:1 relationship and didn't normalized the data. Because adding unnecessary records to tblRecProcess, will cause more than a million unnecessary records per year.

Click to expand...

The provided database is attempt to normalize and still achieve the same form design which is virtually impossible without creating unnecessary records.

@vba_php
Thanks for your input. But June7 is correct about my needs.
As I explained I'm open to any suggestion as long as the result is similar to my forms shown above.

I'm at home and can't understand how your suggestion in #7 can help.
I truly appreciate if you add the changes to my database and upload it. (if you have enough time). If not I'll try it tomorrow at work. (though I don't think it'll work).

Thanks.

June7 said:

Be aware that Cartesian query can perform slowly with very large dataset.

These queries are not editable and would only be useful for report, not form.

Maybe in your case non-normalizing is optimal. It is a balancing act between normalization and ease of data entry/output. "Normalize until it hurts, denormalize until it works." Either way, there can be challenges to manipulating data for a desired output. I have a db with non-normalized structure because it was easiest way to accomplish data entry and desired reporting. However, encountered need for normalized data for graphs so utilized UNION queries.

Click to expand...

I'll follow your steps tomorrow as soon as I'm back to work just to learn something. But I don't think I'll use it. even if it could be used for forms. As I said we have more than a hundred thousand orders per year and performance is a main factor. With present denormalized data, we don't have any problem.

Million thanks for your time.

Tera said:

I'm at home and can't understand how your suggestion in #7 can help.

Click to expand...

after a little bit of fooling around in your DB, I don't think you can actually get your form screen to look like what you want, just like June said in post #5. I may have misread your original post and what you really wanted.

but if you do attempt it, you will need to include a boolean field in tblRecProcess cuz you want that checkbox to appear next to the other data in the same table in a continuous form layout. it looks to me that, to get what you want, you'll have to put 2 subforms inside your main form, both with the same dataset from the table "tblProcesses" (although they might have to bound to different table objects), set those subforms' views to continuous forms, and then add yet another 2 subforms to the same area that also have the same dataset from the table "tblRecProcess" but again have 2 different tables as sources. Not only all of that but you may have to write some kind of code behind the last 2 subforms to look up the data you want to display based on the label to the left of the data.

It seems like way too much work, and if I were doing it, I might consider going to the customer and telling them that access just does not have the ability to display data in the way that they want, then offer them some other solutions that look similar.

I think the relationship you posted initially is correct (does not include 1-1 relationships). I also think you can get very close to the form you posted, but not exactly: (A) I don't think data from tblParts and tblOrders should be editable in the same main form. (B) The Process part of the form should be 1 column and not 2.

(A) When you are making input forms for a series of 1-many relationships you should have a series of main forms->sub forms. That means you should have a main form based on tblParts where you can add/edit/delete data in that table, and on it you should have a subform that lists all data from the many records from tblOrders related to that part record on it. Next to each record in tblOrders on that subform is a button that opens a new main form showing just the data from that 1 order. That main form is based on tblOrders which shows just 1 record that you can add/edit/delete and on that form you should have a subform based on tblRecProcess which shows all the records related to that specific Order. That's how you translate your 1-many relationships to forms.

(B)tblRecProcess is now the basis of a subform on the main form of tblOrders. So instead of split into 2 columns of processes, its just one long one where you can edit your date and name data. That subform also needs an area for the process name. In your example it seems hard coded and outside the input area. When you make it a subform it will be attached to each record.

Doing the above gets you to where everyone is trying to get you to go---> having your subform just showing the processes that have data for that order. If a process isn't included it won't show on that subform. I agree with everyone else, but I don't think what you want to do is a horrible idea or cause any issues, so here's how you achieve what you want and have every process listed:

You create an APPEND query that inserts 1 record into tblRecProcess for every process. When a new order is created you run that query, insert that orders ID into the OrderID_FK field for every record. This way you create the necessary records in tblRecProcess for that order and when the form loads it finds those records and populates.

plog said:

(A) I don't think data from tblParts and tblOrders should be editable in the same main form.

Click to expand...

This form is not meant to be for editing data. It's just a search result form to show the situation of orders. There are other forms for adding or editing data.

plog said:

(B) The Process part of the form should be 1 column and not 2.

Click to expand...

It's only a matter of formatting to let us see the whole thing in one look, without scrolling. But if you can manage the form I need and it need to be in one column, it's OK. We can accept this change.

plog said:

(A) When you are making input forms for a series of 1-many relationships you should have a series of main forms->sub forms. That means you should have a main form based on tblParts where you can add/edit/delete data in that table, and on it you should have a subform that lists all data from the many records from tblOrders related to that part record on it. Next to each record in tblOrders on that subform is a button that opens a new main form showing just the data from that 1 order. That main form is based on tblOrders which shows just 1 record that you can add/edit/delete and on that form you should have a subform based on tblRecProcess which shows all the records related to that specific Order. That's how you translate your 1-many relationships to forms.

Click to expand...

Again, this form is not meant to be used for editing or adding data. Actually every machine in the production line is equipped with a Barcode reader. The operator reads the barcode printed on the process sheet of each order. The necessary data (when & who) is transferred to the back end database (Sql server).
The form is locked and no one is allowed to edit data from this form.

plog said:

(B)tblRecProcess is now the basis of a subform on the main form of tblOrders. So instead of split into 2 columns of processes, its just one long one where you can edit your date and name data. That subform also needs an area for the process name. In your example it seems hard coded and outside the input area. When you make it a subform it will be attached to each record.

Click to expand...

I wasn't successful following your instruction. Can you add this form to my database uploaded above? No need to care for the format. A simple one is enough. I just need to see it in action.
If you do, don't forget please. I need the details of each order above, a list of ALL possible process's and which one is done, which one is not done bellow + who has been in charge of the process & when.

I really appreciate your time.

plog said:

You create an APPEND query that inserts 1 record into tblRecProcess for every process. When a new order is created you run that query, insert that orders ID into the OrderID_FK field for every record.

Click to expand...

Does it mean I have to add unnecessary records to the table? To be more clear, if part 123456 doesn't need being welded, should I add a record for this process too?
If yes, read my conversation with June7 above. It causes adding adding 24 process for each order which in turn ends up to more than several hundred thousand unnecessary records per year which not only has negative effects on performance and indexing the table, but also causes delays in showing the search result.

When you post about forms its assumed that it will interact (edit/add/delete) data. Especially since you used input looking areas for the data coming from tables. So, the length of this thread is mostly on you. Honestly, you aren't making a traditional form, but a report. That object would be better suited for this task than a form, but you still can achieve it via forms.

Since this form will not interact with data (just display it) you can forget most of what I said. I think you can achieve the look you want exactly, and without that APPEND query. The processes portion will still be a subform/subreport but you can achieve getting all the processes to show without adding records to tblRecProcess.

So this kinds of brings us back to your first post. What exactly are you having trouble with in building this?

Yes, my impression was for an interactive form. If this form is just to display (like a report) I offered queries to accomplish that (intended for report but a form could use those same queries).

And where do you want to edit process status? Where do you want to manage adding records for required processes?

plog said:

Honestly, you aren't making a traditional form, but a report. That object would be better suited for this task than a form, but you still can achieve it via forms.

Click to expand...

As far as I know, reports are used for printing the result. At this stage, I need a form to show when users search for an order to see which processes has been done, what's the next, if there's some NG parts who has been responsible for it, when it's been done, etc.
I have no problem for data input forms. It's done via barcode scanners and a series of functions.

plog said:

So this kinds of brings us back to your first post. What exactly are you having trouble with in building this?

Click to expand...

I think I've explained my problem several time. Maybe my English is not good enough to explain it better. But one more time:


The following image is as far as I've gone. I need all processes being listed. This part needs only 4 processes for being manufactured. When I search for it, as you see only 4 registered records are listed in the subform. I need all 24 processes being listed, only these 4 being filled, others being empty. Just like images above on my first post.

June7 said:

Yes, my impression was for an interactive form. If this form is just to display (like a report) I offered queries to accomplish that (intended for report but a form could use those same queries).

Click to expand...

I'm terribly sorry for the confusion.
Maybe I've missed it. What exactly was your suggestion? Post #6?
It didn't work. But I was using a remote control to test it. May some mistakes on my side. I'll check it once again.

Edit: Now I remember. It worked but you said too: Be aware that Cartesian query can perform slowly with very large dataset.
And I explained performance is a main factor. If it makes the database slow, I'll give up and use the denormalized table.

June7 said:

And where do you want to edit process status? Where do you want to manage adding records for required processes?

Click to expand...

As I said adding data, is done simply by scanning the barcode that's printed above order sheet.
If you need to know the details: We have a lot of Lathe, Fraise, TulletPunch, Bending, Laser Cut, Waterjet,... machines. Each machine is equipped with a PC and a barcode reader. The person in charge of the machine, scan the barcode. The ID of the order, the ID of the machine being used, and the ID of the operator is sent to a centeral PC and from there the data is saved to a sql server database.
This database is used as the BE of this database. For editing these records, there are several other normal forms. Users login to the system, search and edit the record.
My main concern here is to have a check sheet of all possible processes.