Ok, I read-up on how to create tables and forms (see my last post, i couldn’t figure out how to continue that post so i started this one), created two tables, one for work order requestor, and one for maintenance departments response, and form with both tables.
Now i don’t know how to show both users inputs.
The first table has a primary work order number starting with 1700, but don’t know to set up 2nd table?
Tu
Might be an example database will help.
Do you need a query to combine both tables?
[Might be an example database will help.]
If you mean screenshots etc., I’m away but should be able to get some tomorrow.
[Do you need a query to combine both tables?]
In my old paradox work orders all fields were in a single table (one primary work number field), which i divided into two separate areas on a single form.
Paradox allowed me to have different permissions on each field inside that table, which meant I didn’t have to figure out how to link two separate tables.
So yes, I think, I need to know how to show both tables in a single form, where the work requestor can enter a password and edit the upper part of the form and then exit.
The workers then would log in with their pasword and be able to respond by editing the lower part of the form which contains the second table.
They could read the upper part of the form but not edit it.
Screen shots to follow .
Thank you!
Screenshots are completely useless unless you are struggling with display problems.
Linking tables needs to be done in the database. There are one-to-one, one-to-many and many-to-many relations. This is all the same SQL stuff since 50 years. In the user interface, forms with subforms and listboxes allow editing related records from many tables.
[Example] Relations reflected by list boxes in forms
Ok, it would be a one to one relationship. How do I relate the first table which has the primary field [work order] to the second table only has the response to the first table?
Tu
First thing that occurs is a work order. This work order needs an integer auto-ID.
Then you create the response table with an integer non-auto-ID, open the menu:Tools>Relations window and drag the response.ID to the order.ID connecting both tables’ primary keys. Now you have a setup that enforces referencial identity, making it impossible to enter any response.ID that has no order.ID, and it is not possible to enter more than one response.
By double-clicking the connection line, you may define what should happen when some order.ID updates or is going to be deleted. “Set null” will not work because response.ID is a primary key but options “update” or “delete” will update a changed ID and delete any response with its order.
P.S. slightly off-topic but useful in this context
menu:Tools>SQL… and run this:
ALTER TABLE ORDERS ADD COLUMN TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE RESPONSE ADD COLUMN TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
Don*t forget View>Refresh Tables to update the Base GUI.
1 Like
Wow, thank you so much! I can’t wait to try it!
Tu