How to build a dynamic filtering report?

[chriswrcg]

I built my first report and had it looking pretty good. Here is an example below.

His was the only data in the system so I thought I would add someone else in and test everything. Everything looked good up to the report. When the new data went in the report just added them in and now both individuals are in it. This makes total since this would happen and I kind of expected it. I hoped it wouldn't but thought it might.



How can I filter through employees and dates so that the report only displays one individual and date at a time? Is there something I can do with this one to get it to do that or will I have to start over and design another kind of report?

[June7]

Options to open report filtered to single employee and date:

1. dynamic parameterized query

2. apply filter criteria in: DoCmd.OpenReport "reportname", , , "EmpID=" & Me.tbxEmp & " AND WorkDate=#" & Me.tbxDate & "#". Review http://allenbrowne.com/ser-62.html

Both options should reference controls on form for the EmpID and date values.

If you want to show multiple employees and/or dates, use report Sorting & Grouping features.

[GinaWhipp]

Have a look at...
https://www.access-diva.com/r6.html

[chriswrcg]

I am at a complete loss. I have read all the articles you guys have suggested and looked online for videos and tutorials on how to create a dynamic report but nothing I try works. The easiest way is to combine all the fields I need into one query and then create a search form that will allow me to filter the data and see what I want to see. I can then create a report based on the filtered data. The problem is that I have so many queries and fields I need for the report that when I try and combine them into one query I get the query is to complex error message.
I have tried combining queries and removing queries but I keep getting the same message. At this point I don't think it is possible to have all the data I need and print a report in access.

[GinaWhipp]

Hmm, let's start with the report. Is the report you posted in your first post one report or more?

[chriswrcg]

A Frankenstein's monster of sub reports to build the report I need. When I tried to combine queries i got error message after error message. I know this is not going to work for what I need but right now I cant get everything I need on one query.
This give me this.

[June7]

If you want to provide db for analysis, we might be able to advise alternative. However, it just might be what you have is the best approach.

[chriswrcg]

Here you go. It is still rough but is coming along. If I could just lick this report mess.

[GinaWhipp]

chriswrcg

Okay, you should not have jumped to reports because I see the Tables are still not right. I have made a mock up of what they look like they should be...


I also can't seem to find what you said tried and failed. The only Form for filtering the report I see is SearchF and that has no code behind it and a button that opens a query. Am I missing something?

[chriswrcg]

I found this and was able to follow along and create what he did. I tried to apply it to a report I will be needing with limited success.

https://www.youtube.com/watch?v=CTiA_4Me0cI.

I am abandoning the attempt

[chriswrcg]

Here is the updated Relationship Chart.

[CJ_London]

Not necessarily connected to your problem, but you also have two lookup fields

TaskExceptions>ExceptionCode
TaskActivities>TaskStandard

The first links to ExceptionList per the relationships
The second links to TaskList, same as TaskListID in the relationships

You may need both but since TaskListID is not populated I question whether this is correct.

I would remove the lookups so you can see what you actually have, and then I would rename these fields with a more accurate name to reflect the actual contents (i.e. ExceptionID, TaskListID). If you are concerned about having to manually change label captions to something more user friendly, use the field caption property.

Other things - put an index, no duplicates against TaskCode in TaskList to prevent accidentally entering the same code twice. There may be other fields where this would be of benefit

Bit concerned about your workdays table. What if someone clocks out then in again after visiting the dentist? Or similar scenarios, what if they take more than two breaks?

[GinaWhipp]

@chriswcg

I am sorry to hear you are abandoning this.. Let me just say, I know you are probably getting frustrated, building relational databases is hard... Access is hard in the beginning (not a one and done kind of situation). And even those of us who have been doing it for years get that way from time to time but if you stick with it it WILL get easier.

Now, I see you posted the Relationships, does that mean you had coffee and decided to give it another shot?

[chriswrcg]

i meant i was abandoning the search form for now untill i get everything else straight. After this I need to work on my query's.

[chriswrcg]

Not necessarily connected to your problem, but you also have two lookup fields

TaskExceptions>ExceptionCode
TaskActivities>TaskStandard

The first links to ExceptionList per the relationships
The second links to TaskList, same as TaskListID in the relationships

You may need both but since TaskListID is not populated I question whether this is correct.

I have two lookup tables one for task list and one for exception list. right now I am only using them to populate my drop down lookup fields on my data entry form.

Other things - put an index, no duplicates against TaskCode in TaskList to prevent accidentally entering the same code twice. There may be other fields where this would be of benefit

I am not really sure what you need me to do with this. Isn't a lookup table by default an index?

Bit concerned about your workdays table. What if someone clocks out then in again after visiting the dentist? Or similar scenarios, what if they take more than two breaks?

In those situations the time away form the job would be handled as exceptions and the time removed in that way. As long as we know how long the associate was away from the work and during what task the exception happened in then we can remove the time.