Linking all fields to each record (1 Viewer)

wsuds

New member
Local time
Yesterday, 23:31
Joined
Nov 17, 2017
Messages
7
Hi, I am trying to create a database to record quality checks against each project. I have one table called "Projects" which contains all current project details and another called "Checks" which contains all quality checks required for each project, plus comments, dates etc.

Basically, I need a form to select a particular project from the "Projects" table which will then show me all the records from "Checks" table to allow me to tick them off/comment on them (every project needs the same checks carried out). The results need saved against the particular project.

I have tried to combine these tables with a query but I cannot edit the results on a form. I have tried creating an Append Query to transfer date into a new table which will let me change entries but if I add another record in "Projects" and run Append Query again it adds in the new record and duplicates all existing records. Also, any changes to the new table do not update on the "Projects" and "Checks" tables.

Hope this makes sense. Any help will be much appreciated.
 

plog

Banishment Pending
Local time
Today, 01:31
Joined
May 11, 2011
Messages
11,613
First, action queries (APPEND, UPDATE, DELETE) are generally hacks around a poor table design or a misunderstanding of how databases are to work. Second, forms that interact with data (add/edit/delete) should be based on a single table.

So, for your 2 table situation where there is a 1 (Projects) to many (Checks) relationship you should have a main form which will be based on Projects and show the data from just 1 record and a sub-form that will be based on Checks and show all records related to the Project being displayed in the main form.

Here's a link on how forms/subforms get linked together:

https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b
 

isladogs

MVP / VIP
Local time
Today, 06:31
Joined
Jan 14, 2017
Messages
18,186
Hi Plog

First, action queries (APPEND, UPDATE, DELETE) are generally hacks around a poor table design or a misunderstanding of how databases are to work. Second, forms that interact with data (add/edit/delete) should be based on a single table.

You know I don't totally agree with the second sentence.

As for the first, I can think of many situations where action queries (APPEND, UPDATE, DELETE) are a perfectly valid thing to use in a WELL designed database.
I've seen you write this before, so I would be interested in your reasons for saying that.
I'm also happy to give valid examples of their use.
 

wsuds

New member
Local time
Yesterday, 23:31
Joined
Nov 17, 2017
Messages
7
Thanks for the quick responses.

Firstly, I created two separate tables because I have 60+ projects each requiring 60+ checks, so to create one table would be huge. As each project requires each check it seemed the sensible approach.

I have created a main form and sub-form as suggested but when I open up the sub-form it needs to show all required checks to allow me to tick them off otherwise I will have to manually select 60+ checks for each project, including entering dates and comments if required.

Is there a way to pull up the sub-form with all checks already in view without having to manually enter them each on each project?
 

plog

Banishment Pending
Local time
Today, 01:31
Joined
May 11, 2011
Messages
11,613
The first is more of an observation than a rule. It was created from my experience on this site and the databases I've had the misfortune of taking over. Action queries are way over-used by people who can't set up tables, or who can't visualize data and need to have a physical table to help them along. I'd be willing to bet that if you searched this form for "UPDATE QUERY" at least 75% of them were hacks around a poor table structure or an inability to visualize data.

The 2nd is a great, practical rule. Great chefs don't have to use measuring cups. 99.9% of the posts started on this site are not by great Access chefs. So, you give them instructions that include using measuring cups because they can hold those instructions in their mind.

Anyone (even great chefs) who follows rule #2 will have their recipe come out correctly and not go wrong. Search this forum for "my form is uneditable" and that rule eliminates all those posts. Add to that the hours spent by those people before throwing up their hands and generating those posts and the people who said "screw this" and just moved back to Excel without posting. Access is all about making life/data more efficient and if everyone passed around rule #2 the world would be a more efficient place.
 

plog

Banishment Pending
Local time
Today, 01:31
Joined
May 11, 2011
Messages
11,613
s there a way to pull up the sub-form with all checks already in view without having to manually enter them each on each project?

God, I'm so glad I gave myself some wiggle room and said that 75% of action queries are hacks. Because I think you need one. This is how I would do it:

Main form based on Projects, sub-form based on Checks as described in my first post. On the main form would then be a button, let's call it 'Generate Checklist'. When it is clicked it runs an APPEND query that populates 60 records in checks (1 for each check) for that Project. The screen refreshes and the subform would then be populated with all your data which you could then manually edit (add notes/dates, etc).
 

isladogs

MVP / VIP
Local time
Today, 06:31
Joined
Jan 14, 2017
Messages
18,186
The first is more of an observation than a rule. It was created from my experience on this site and the databases I've had the misfortune of taking over. Action queries are way over-used by people who can't set up tables, or who can't visualize data and need to have a physical table to help them along. I'd be willing to bet that if you searched this form for "UPDATE QUERY" at least 75% of them were hacks around a poor table structure or an inability to visualize data.

The 2nd is a great, practical rule. Great chefs don't have to use measuring cups. 99.9% of the posts started on this site are not by great Access chefs. So, you give them instructions that include using measuring cups because they can hold those instructions in their mind.

Anyone (even great chefs) who follows rule #2 will have their recipe come out correctly and not go wrong. Search this forum for "my form is uneditable" and that rule eliminates all those posts. Add to that the hours spent by those people before throwing up their hands and generating those posts and the people who said "screw this" and just moved back to Excel without posting. Access is all about making life/data more efficient and if everyone passed around rule #2 the world would be a more efficient place.

God, I'm so glad I gave myself some wiggle room and said that 75% of action queries are hacks. Because I think you need one. This is how I would do it:

LOL!!!!

I don't disagree with the priniciples in general
1. I use action queries mainly when importing data into a database from e.g. spreadsheets / CSV files / JSON files
2. In terms of guiding others to reduce the risk of read only forms, you are of course correct in saying that using one table prevents that being an issue.

For my purposes though, I often use forms based on more than one table where database design is improved by doing so.
Of course I have to check it is still editable

In extreme cases, I have even resorted to using another action query - MAKE TABLE - to create a temporary recordset which can be edited in a form.
This of course requires that the edited records are saved back to the original table.

One example of this is a student assessment mark form.
The data is in normalised tables but for the purposes of the form I need to use a crosstab query ... which is of course read only
... hence the MAKE TABLE query.

See attached screenshot



As I said earlier, its an EXTREME case but its lightning fast in use.
As far as end users are concerned it's a normal form
 

Attachments

  • AssessmentMarksForm.jpg
    AssessmentMarksForm.jpg
    103 KB · Views: 280

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 19, 2002
Messages
42,981
I agree with the append query concept but I'd like to add more framework.
1. You need a third table that defines the universe of Checks.
2. Add a column to this table that indicates Active/Inactive because you don't ever want to delete from this table (It would break RI) and at some point, you will want to stop using check #12 and start using check #63
3. Add another column that indicates sequence if sequence is relevant.
4. In the AfterInsert event of the main form, I would automatically run the append query. You don't want the user to have to push a button and you certainly don't want him to push it twice.
5. The subform's "Allow" properties need to be set to prevent addition and deletion since your append query handles appending and deleting will never be allowed anyway.
6. You might also want a field on the projects table that indicates that the checks are complete. This may or may not be relevant to your process but if it is, it will be easier to set this with code every time the subform is updated to verify that all the checks are complete and then set the flag on the parent form.
 

wsuds

New member
Local time
Yesterday, 23:31
Joined
Nov 17, 2017
Messages
7
Guys, many thanks for your help. All working now.

On a separate not:, how should I have set up my database in the first instance, i.e. was creating two tables (60+ projects and 60+ checks) the correct way to do it? (to avoid the need for a hack!)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:31
Joined
May 7, 2009
Messages
19,169
you have your tables fine, for me.
you only need another one, the junction.
this table should contain, at minimum, PK of project and PK of checklist and Yes/No field (for checked/not checked).
to fill up this table, create an insert table based on cartesian of the two main table, the project and checklist:


insert into yourJunctionTable (ProjectID, CheckListID, Checked) SELECT Project.ProjectID, CheckList.CheckListID, True From Project, CheckList;


next create a Query using this JunctionTable and CheckList table:


Select ProcjectID, CheckListID, CheckList.Description, Checked From JunctionTable Left Join CheckList On JunctionTable.CheckListID = CheckList.CheckListID;


use this query as subform to main form.
the main form must be bound to table Project.


on this subform's Property (Data):


Link Master Fields: ProjectID
Link Child Fields: ProjectID


set the subform to AllowAdditions to No.
 

plog

Banishment Pending
Local time
Today, 01:31
Joined
May 11, 2011
Messages
11,613
Can you set up the relationship tool in Access and post a screenshot so we can see your tables?
 

wsuds

New member
Local time
Yesterday, 23:31
Joined
Nov 17, 2017
Messages
7
Hi plog,
attached Relationships as they are at present.
I have not made any changes yet to add in the Junction table etc mentioned above.
 

Attachments

  • Database Relationships 2017-11-22.PNG
    Database Relationships 2017-11-22.PNG
    39.2 KB · Views: 68

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 19, 2002
Messages
42,981
1. You have duplicated columns. tblChecklistRecords, in addition to the two IDs that connect the two tables, copies data from those two tables. That is incorrect and those duplicate columns should be removed. Without detailed study, I can't tell which table the columns belong in. They might need to be in the junction table but in that case, they wouldn't be in the 1-side table. So, the ID is copied from the 1-side table to the many side table in order to link the two but NO OTHER COLUMN IS COPIED.
2. Column names should be formed ONLY from letters (upper and lower case), numbers, and the underscore. Names should NEVER include spaces or special characters. Names should also not duplicate reserved words, especially function and property names such as Date, Month, Name, Length, etc.
 

wsuds

New member
Local time
Yesterday, 23:31
Joined
Nov 17, 2017
Messages
7
I have now tidied up my tables and all working perfectly.
Many thanks to everyone for the help.

If you are interested. Updated Relationships attached.
 

Attachments

  • Database Relationships 2017-11-23.PNG
    Database Relationships 2017-11-23.PNG
    27.2 KB · Views: 71

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:31
Joined
Feb 19, 2002
Messages
42,981
Much better. If you change the FK names to match the PK names rather than the text fields, your schema would make more sense. Hopefully you are not using table level lookups because that can cause this type of confusion.

Participant --> Participant_ID should be Participant_ID --> Participant_ID since it is the ID that is being used as the FK NOT the text field. Once you remove the table level lookup, you will understand why the ID name makes more sense. One of the problems with this abomination "feature" is that when someone looks at tblProjectList and sees John Jones as the participant, they will expect the data type to be text and they will expect to be able to search for "John Jones" whereas in reality, the column actually contains 908 which is the ID of the Participant and you actually have to search for 908.
 

wsuds

New member
Local time
Yesterday, 23:31
Joined
Nov 17, 2017
Messages
7
Pat, thanks for the tip. I was using the look-up function but have now changed as you have suggested. All working perfectly now.
Thanks again.
 

Users who are viewing this thread

Top Bottom