Help with query/subform

[Flora2021]

Hi,
I am trying to create a subform in a form for employee records. This subform will pull from a table and use a query or a filter . On each employee record, there is a multivalue field that will contain dept codes. sometimes there is only one but other times there are multiple departments in this field.
In the CDI table that i want the data pulled from, these department codes are also present for each document type. Some might be a single department or others may also be for multple departments. Basically I want the filter to only pull the document types that match any of the dept codes listed in the employee record. So say Julie Smith is in dept A, and B any records that would contain A or B would need to be returned and show under this subform. I can attach screenshots but does this sound doable? Thanks so much

 

[Micron]

IMO best approach is to base any form on a query and not a table, and most often, subform relates to one table/query. If your sf records will be fields from more than one table, a query is a must but it might be read only. Start by creating this query and if you get it to return what you want, base the form on it. However, if it must be editable, test that in the query first.

mv fields are not a great idea in spite of the fact that M$ makes them enticing to use.
See what you think.

Multivalued Fields - Mendip Data Systems

Database applications for businesses, schools and individuals

www.mendipdatasystems.co.uk

 

[Flora2021]

Hi, How would you suggest I run the query? My boss is insisting that the "sub form"? be editable and be able to write back to the main table. The records for the sub form will all be from the same table called CDI though. I got this to half work but the "sub form" only pulls back one of the values on the multiform and not all of the data in the multivalued field. I appreciate any help.

 

[Flora2021]

The 1st image shows the form and subform- It looks like its pulling the data that matches in the "dept" field from the employee form, but the fields for the subform(cdi) are not populating the values.

 

[Micron]

It looks like its pulling the data that matches in the "dept" field from the employee form,

Not knowing your data, I'll have to take your word for that.

the "sub form" only pulls back one of the values on the multiform

Perhaps it is only seeing the first value in the multi value data.

Not sure what you're asking. If main table means the one side (e.g. customer) of the many e.g. (orders) it doesn't work that way. The main form is based on the one, the subform is based on the many. The subform control links the 2 forms through its Link Master and Link Child properties. As long as there is a parent record, you can add child records in the subform, but those records go into their own table - there is no such thing as editing/creating subform records which affect the "main" table. It just looks like that to those who don't know any better. In conjunction, the relationship between these 2 tables should be set to Enforce Referential Integrity, otherwise you can end up with orphan records (on the many side) who have no parent record (the one side).

Those are the basics. Doing research on form/subform relationship would probably be a good idea. However, when it comes to mv fields that you mentioned, I can't assist because I'd have to figure out how something I wouldn't use affects all of this. Best I can say is (using customer as the main table field for example)
- create the query that returns main records and include that in a new query (as if it were a table)
- equal join the subform table to the main query as appropriate and put only subform table fields in the query design grid
- run the query just to test. If you get records try to edit one. Then try to include main query fields in the grid and run query
- test if you can edit either main query field or subform query field

If at any point you can't edit or add records there could be several reasons. I suspect your first roadblock will be getting records at all, or if you get past that, being able to edit them - due to the mvf's.

 

[Flora2021]

Hi, I am confused. So I create a new query for the main record which is the CDI_master table but I am not sure what you mean by equal join a subform table. Would this be to create a new subform for the employees table? Thanks

 

[Micron]

If you're saying you don't know how to join tables and queries, see if this helps.

Join tables and queries - Microsoft Support

Use joins in Access queries to combine records from different data sources so that each pair of records from the sources becomes one record in the query results.

support.microsoft.com

 

[Flora2021]

If you're saying you don't know how to join tables and queries, see if this helps.

Join tables and queries - Microsoft Support

Use joins in Access queries to combine records from different data sources so that each pair of records from the sources becomes one record in the query results.

support.microsoft.com

Hi, I know how to join queries and tables and relationships. I just was confused by the steps you mentioned. I am not sure what tables you are saying should be made into sub forms or queries.
I initially just wanted a "employee form" that is from the employee table. Then there would be a subform in the employee form that would pull the all records and all fields back from the CDI_Master table that contained any of the same values in the dept field in the employee table. I have done this previously with no issue when the matching value was just a singular value such as "Corn" or you defined multiple specific critera, such as "two", "three" etc. but I am not sure not sure how to do the query expression that would return records that contained one or all varying values in that field. I thought it would be something like... IF cdi_master.assigned to dept contains * employee.data but I am lost and could not find anything similar searching.



- create the query that returns main records( I assume this would be the CDI_Master that contains the data to pull back?) and include that in a new query (as if it were a table) ? Not sure what you mean by include in a new query?

- equal join the subform table (What would this subform table be? ) to the main query as appropriate and put only subform table fields in the query design grid
- run the query just to test. If you get records try to edit one. Then try to include main query fields in the grid and run query
- test if you can edit either main query field or subform query field

 

[Micron]

Perhaps you're not aware that you can put queries into the design grid of a query - same as if it were a table? Taking a step back might be a good idea because really, a main form should be bound to one query (or table) and the subform to the query/table that holds the related records as noted in post 5 and use the master/child subform control feature. My other suggestions were only to test the impact of the mvf's on your situation but those ideas don't seem to be helping.

 

[jackd]

I see 5 tables in your graphic.
Can you show us
-the designs of each table, and
- A graphic showing tables and relationships with tables extended to display all columns.

I would restructure to remove the MVF also.