Need help with Report showing all data ....

[Synses]

I created my first report on a database that has tables with combo boxes and a related form.

The main table the report is supposed to be on is the lesson learned table. The lesson learned table has three fields, client idfk, facilityidfk, and lesson learned.
"Lesson learned" is entered with a form, the form asks user to select client, then select facility, then enter lesson learned.

The data they enter is recorded in the lesson learned table, in the lesson learned field, only for the facility the user selects in the lesson learned form.

The Report is supposed to show the Lessons Learned for a certain facility, but I think I did not create it correctly. The report shows all clients and facilities with lessons learned data and shows the lesson learned data in ALL facilities for each client where any lesson learned data has been entered.

How can I get the report to show only "Lesson Learned" for a certain facility? Is there a way to select one or more facilities for the report to show?

Thanks for any help you can offer!
Synthia
(file attached)

 

[pbaldy]

Here's a way to filter to one or more facilities:

http://www.baldyweb.com/multiselect.htm

 

[plog]

You need to fix your relationships.

There should only be one path between tables. When I open your relationships I can trace 9 different ways to get from tblClient to tblClientStaff. That is wrong. Mostly this issue has to do with you sticking unnecessary foreign keys in every table. For example, tblClientStaff doesn't need a foreign key to both the FAcility and the Client table--only one. I don't know which is the correct one, but I do know you shouldn't have both.

Same concept applies in many places. You need to reduce your relationships such that there is only 1 path between tables, not the spider web you have created. Then you address the issue you posted about.

 

[Synses]

Thanks. That is exactly the kind of advice I need because I obviously am new to access and don't realize the consequences of doing some things that pros like you do and I appreciate you sharing your expertise.
I will do that (cut out extra FKs) now and rethink how to do it.

It also helps that I just I asked my sister, who I volunteered to do this for, if she needs to enter and view Lessons Learned by Client or Facilities or both and she said by Facility. (she volunteered to do it for a PD activity for her job but doesn't have time to learn it/didn't know it would be this hard and neither did I for that matter, but I have more time/am retired/owe her in life big time/and am determined to learn to do this right and well, as I did with Excel a few years back)
Gratefully,
Synthia

You need to fix your relationships.

There should only be one path between tables. When I open your relationships I can trace 9 different ways to get from tblClient to tblClientStaff. That is wrong. Mostly this issue has to do with you sticking unnecessary foreign keys in every table. For example, tblClientStaff doesn't need a foreign key to both the FAcility and the Client table--only one. I don't know which is the correct one, but I do know you shouldn't have both.

Same concept applies in many places. You need to reduce your relationships such that there is only 1 path between tables, not the spider web you have created. Then you address the issue you posted about.

 

[Synses]

Thanks, I am working on this and see/understand that what I think I don't really understand is how to make tables and then make relationships between tables.

I think what you are saying is, for example, that these connections (between,client, facility, clientstaff, phone number) should be step by step step as opposed to connect it everywhere there is a connection, which is how I have it.
So does this seem better?)
1. Create clients table.
2. Create facilities table.
3. Create clientstaff table.
4. Create clientrole table.
5. Create clientphonenumber table.
6. Create NLI(vending company I am doing this for)staff table.
7. Create NLIstaffrole table.
8. Create NLIstaffphonenumber table.
9. Create LessonsLearned table.

Relationships(?):
Client to Facility
Facilities to clientstaff.
Clinetstaff to clientstaffrole.
Clientstaff to clientstaffphonenumber.
Facilities to NLIStaff.
NLIStaff to NLIStaffrole.
NLIStaff to NLIStaffphonenumber.
Lessons Learned to Facilities.

Does that seem reasonable or am I still trying to do too much with table relationships?

I am not proficient at this, and especially not yet at queries and reports.

The point of the database is for easy access to enter/maintain/review/report:
client and client facility info (client location contact info, client staff contact info, client lessons learned info, and the commensurate NLI staff contact info.

I suspect that I am trying to connect all the info that needs to be entered/edited/stored and viewed and reported together through table relationships, while that probably needs to be done moreso with queries/reports, right?

Thanks for any suggestions you might have to figuring out which table relationships to delete and how to replace them with queries/reports, etc.

Gratefully,
Synthia

You need to fix your relationships.

There should only be one path between tables. When I open your relationships I can trace 9 different ways to get from tblClient to tblClientStaff. That is wrong. Mostly this issue has to do with you sticking unnecessary foreign keys in every table. For example, tblClientStaff doesn't need a foreign key to both the FAcility and the Client table--only one. I don't know which is the correct one, but I do know you shouldn't have both.

Same concept applies in many places. You need to reduce your relationships such that there is only 1 path between tables, not the spider web you have created. Then you address the issue you posted about.

 

[plog]

You know your data better than us, but the Relationships you laid out seem correct, except for the last one. Lesson Learned doesn't seem like it should be related to a Facility. Don't people learn lessons? Wouldn't that be better related directly to the Client and/or Staff table depending on who does the learning?

Also, I don't think you would have 2 tables for STaff (NLI and and Client), instead you would have one Staff table and then use a field to differentiate if they were NLI or Client.

 

[Synses]

You are such a great help, yes I see what you mean.

For staff, should I make one staff table and insert a field on it for employer, either "client" or "NLI" ..?

On the Lesson Learned form, my sister asked that the lesson learned be specific to facility - it is "how to do it right" data that is specific to certain facilities learned from previous difficulties with that client's facility and there is no other place for that kind of data in their existing systems. (it is the reason my sister volunteered to make the database, which she found out she can't do, and I owe her my life, literally, so I volunteered to learn how to do it and do it for her)

The quick easy access to lessons learned by facility is the primary purpose of the database, and I still haven't figured out how to generate a report for a lesson learned by facility, showing just lessons learned for the selected facility. (??)

Right now the only way to see lesson learned by facility - just for one facility - is to look on the form....they need a way to print and send via email the lessons learned for a facility.

I am working on cutting relationships now and will fix the staff table tonight or tomorrow.

Thanks again, tons,
Synthia

You know your data better than us, but the Relationships you laid out seem correct, except for the last one. Lesson Learned doesn't seem like it should be related to a Facility. Don't people learn lessons? Wouldn't that be better related directly to the Client and/or Staff table depending on who does the learning?

Also, I don't think you would have 2 tables for STaff (NLI and and Client), instead you would have one Staff table and then use a field to differentiate if they were NLI or Client.

 

[pbaldy]

Right now the only way to see lesson learned by facility - just for one facility - is to look on the form....they need a way to print and send via email the lessons learned for a facility.

Once you get the design updated, try the listbox method I posted, if you want the option to select more than one. If just one:

http://www.baldyweb.com/wherecondition.htm