Queries/reports acting strange

[Forbes]

Okay, so I have 2 separate reports with 3 columns, these queries count for a sum column based on where the employee works, and who reported/conditioned them.




The following Queries count

Lets say this is the query for Dinner department reporting management

SELECT reports.cond, Count(reports.cond) AS CountOfcond
FROM reports
WHERE (((reports.condname)="QA-Jennifer" Or (reports.condname)="QA-Jen" Or (reports.condname)="QA-Lauren" Or (reports.condname)="QA-Mary" Or (reports.condname)="QC-Danielle" Or (reports.condname)="QC-Summer" Or (reports.condname)="QA-Nicholas") AND ((reports.condemp)='Michelle' Or (reports.condemp)='Robyn') AND (reports.timestamp Between Eval("[Forms]![frmhome]![sbfrmprint]![txtStart]") And Eval("[Forms]![frmhome]![sbfrmprint]![txtEnd]") ))
GROUP BY reports.cond;

Lets say this is the Lunch Department reporting management

SELECT reports.cond, Count(reports.cond) AS CountOfcond
FROM reports
WHERE (((reports.condname)="QA-Jennifer" Or (reports.condname)="QA-Jen" Or (reports.condname)="QA-Lauren" Or (reports.condname)="QA-Mary" Or (reports.condname)="QC-Danielle" Or (reports.condname)="QC-Summer" Or (reports.condname)="QA-Nicholas") AND ((reports.condemp)='Jessica' Or (reports.condemp)='Brian' Or (reports.condemp)='Chris') AND (reports.timestamp Between Eval("[Forms]![frmhome]![sbfrmprint]![txtStart]") And Eval("[Forms]![frmhome]![sbfrmprint]![txtEnd]") ))
GROUP BY reports.cond;

On the reports themselves, the following code creates concatrelated data to show all employees in those dept who were reported by the specific dept the reports being pulled from:

So if Lunch Dept reports MGMT and you need the report this runs:

=ConcatRelated("[condname]","reports","[cond] = '" & [cond] & "' And ([condname]='QA-Jennifer' Or [condname]='QA-Jen' Or [condname]='QA-Lauren' Or [condname]='QA-Mary' Or [condname]='QA-Nicholas' Or [condname]='QC-Danielle' Or [condname]='QC-Summer') AND ([condemp]='Jessica' Or [condemp]='Brian' Or [condemp]='Chris') AND timestamp Between #" & [Forms]![frmhome]![sbfrmprint]![txtStart] & "# And #" & [Forms]![frmhome]![sbfrmprint]![txtEnd] & "#")

And the Dinner Dept report on MGMT:

=ConcatRelated("[condname]","reports","[cond] = '" & [cond] & "' And ([condname]='QA-Jennifer' Or [condname]='QA-Jen' Or [condname]='QA-Lauren' Or [condname]='QA-Mary' Or [condname]='QA-Nicholas' Or [condname]='QC-Danielle' Or [condname]='QC-Summer') And ([condemp]='Michelle' Or [condemp]='Robyn') AND timestamp Between #" & [Forms]![frmhome]![sbfrmprint]![txtStart] & "# And #" & [Forms]![frmhome]![sbfrmprint]![txtEnd] & "#")

When the reports come up This is what I see on the reports themselves, and I'm having a hard time figuring this out.

Lunch Report:


Dinner Report:

For some reason the Lunch report is picking up the conditions from the dinner report, but doesnt display the name, granted they shouldnt be there at all. any suggestions?

[June7]

None of the output shows count greater than 1. Are you sure that situation can occur?

Since the queries are almost identical, I would expect the output to be almost identical.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Forbes]

Yes up until recently the reports ran fine, this report only shows the value of 1 because thats all the conditions those employees had, but the Count works perfectly, the problem is in the code for which employees reported the mgmt wether it be dinner or lunch dept

One report I have it set to only show the employees conditiond by jessica, chris, or brian, and the other should only show conditions from the names robyn and michelle.

But on the chris, brian, and jessica report the conditions are showing for the Robyn, Michelle report.

when it works the Lunch Dept Report should only show the "OC Error" because the others were conditions and even show on the Lunch report.


Unfortunately due to the DB being in long standing use it cannot be uploaded for privacy concerns, hence the random names generated.

[Micron]

Go back to the queries and run them, checking for correct results. You haven't said if it's the same report. If so, you can't reopen it with a new record source without closing it first, so is that it? While you're at it, consider that mixing AND and OR can produce unexpected results if not bracketed properly. I don't see ambiguity, but I didn't create the query either. Maybe also entertain the idea of
...And ([condname]IN('QA-Jennifer','QA-Jen','QA-Lauren',...))
Pretty difficult to solve this one if you can't post some sort of watered down db copy.

[June7]

Right, would have to make copy and sanitize. Maybe someone else will jump in with some insight because it certainly escapes me.

If they worked until recently, what was changed? Could you retrieve an earlier working version you could explore?

[Forbes]

SO yes it is two separate reports the form to pull these reports(reports are all on one table) uses cmd buttons to pull the different reports and the code runs on the query and the report itself,

The Lunch Report should only show employees conditioned by jessica, chris, or brian
The Dinner Report should only show employees conditioned by Michelle and Robyn (which is working fine)
Currently the Lunch report is showing employees conditioned by both,

The table columns on the report are sorted Condition (#) Number of times this condition was submitted (#) all employees conditioned for this

The first set of code is the code that counts the number of times that specific condition was reported by those specific employees (Dinner/Lunch departments) which the count is also working fine.

The second code block for both departments is CONCATRELATED() to show "all employees conditioned for that specific condition, by that specific department.

The Lunch Department Report however is showing the conditions from the dinner report and the count but not the employees conditioned for it, this means;
The code for CONCAT() is working to show the employees conditioned for those conditions
But the Lunch Report shouldn't show the conditions from the Dinner Report, because the conditions weren't submitted by Chris, Brian, or Jessica\

So it leads me to believe it has to be in this section:

And ([condemp]='Michelle' Or [condemp]='Robyn') AND timestamp Between #" & [Forms]![frmhome]![sbfrmprint]![txtStart] & "# And #" & [Forms]![frmhome]![sbfrmprint]![txtEnd] & "#")

AND ([condemp]='Jessica' Or [condemp]='Brian' Or [condemp]='Chris') AND timestamp Between #" & [Forms]![frmhome]![sbfrmprint]![txtStart] & "# And #" & [Forms]![frmhome]![sbfrmprint]![txtEnd] & "#")

but I still could be wrong

[Forbes]

Also, sorry I didn't actually answer your questions June7, unfortunately the DB is working in the older releases, it has been revised compiled and visually updated 3 times now, and during this version release it worked initially, which is why I am stumped. I do not have a scrubbed version to share unfortunately, the DB has become rather large and slightly complex in nature as well as split and shared. I went to the original backup of the release and played with it and all the code seems unchanged.. Micron, I will give your though a try here soon, because me and Pbaldy collaborated a lot on these queries (he saved me) and it took a lot of getting the right AND Or's to get it to stop doing this stuff when the first version released, and like I said haha until now it worked like a charm.

For some reason the "Lunch Report" feels the need to show conditions from the Dinner employees, also if this helps this is the base process from front to end:

1) Throughout the day "objects" come through the business and through multiple departments, each department checks on what the last one did essentially, and conditions them if they have made a mistake using a drop down form input that sends the data to the "reports" table, the Lunch and Dinner departments are separate therefore the Lunch report should not have conditions made by the Dinner employees.

[Condemp] = Conditioning employee(the one inputting the condition)
[cond] = condition name (OC Error)
[condname] = conditioned employee
[timestamp] = time of condition

2) The mgmt every month pulls the reports from each section and the overall reports (hense the multiple queries & report criteria)

3) So lets say Lunch messed up and has an OC Error, Dinner would condition them for it, the mgmt would pull the Dinner Report and see that they conditioned lunch for an OC Error.

If this makes more sense or you have any other questions let me know

[Forbes]

Closing thread unsolved.