Create something that will be useful with mail merge and multiple values checked per record

[GCtechjim]

I have set up a database to track items that are issued to employees. I have fields for; First Name, Last Name, Department, Title. Then I have check box fields for the items I am tracking that are labeled "A", "B", "C"... and others.
What I would like for the final product is to have;

1. Any fields that are checked "Yes" ("True"?) that relate to the First and Last name to be compiled showing just what the employee has.
2. Take that information and merge it into a document with the First and Last name in a paragraph and the items that correspond to that employee are listed elsewhere on the same document.
3. Have the ability to search and find one employee record to merge at a time, instead of all employees in the database.(when someone leaves I can just merge the document "letter" for them to sign.



I am almost sure that this is possible but I have a limited knowledge of how to set this up. I am not sure what my first step in this would be. Any help would be greatly appreciated.

[ranman256]

You have a table tEmps for the employee list. EmpID (autonum), FName, Lname, etc

you have a table tInv for the 'inventory' that a person COULD HAVE. InvID (autonum) ,ItemName, etc

you have a table tEmpOwns for the actual item an empoyee has, this table has fields
EmpID
InvID

Then on a form you can dbl-click from a list of Inv to add them to the Emp, (rather than yes/no, you add the code to the tEmpOwns table)
then make a query, qsMailMerge, to join tEmps, tInv, tEmpOwns,...this query is used for mailmerge.

Open Word, mail template, this uses the query qsMailMerge to build the letters document.

[GCtechjim]

Ranman256. Thank for your help. I may not have explained well enough. All of the fields I have are on one table keyinventory_1_Full. For what you are describing, do I have to break up what I have. I have set up several other queries, forms and reports based on what I have and don't want to re-create everything I already have.

[GCtechjim]

I have attached a picture of what I have.

[ranman256]

Looks great visually, but not usefull in database functionallity.
NOW, you can run a few queries that de-construct what you have, assemble relational db tables, and still produce the above grid, and functions you need.

[GCtechjim]

Looks great visually, but not usefull in database functionallity.
NOW, you can run a few queries that de-construct what you have, assemble relational db tables, and still produce the above grid, and functions you need.

That is where I am stuck and need the help with.