Linking all fields to each record

[wsuds]

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.

 

[isladogs]

So your query is READ ONLY ...

See this link for reasons so you can modify it accordingly
http://allenbrowne.com/ser-61.html

 

[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.

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]

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]

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]

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]

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]

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

 

[wsuds]

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]

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]

Can you set up the relationship tool in Access and post a screenshot so we can see your tables?

 

[wsuds]

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.

 

[wsuds]

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.

 

[wsuds]

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.