Stumped and I'm sure it's simple

[eburgtech]

Ok, so, we have one major "Files" table where most of the data resides. There are fields on this table to identify which user has processed what piece of each file. Currently, there are separate reports to track who has processed how many of each piece of the files. A manager wants a report that shows how much of each piece of the file a specified user has processed for a specified date range. The current configuration is that there is a query with a where clause that is connected to a form where the user can input a date range and it returns all records with a count for each employee for those types of pieces of file. I need to be able to make a form that will pull all three types of data from the one table, by specifying a username and date range. I've spent several weeks on this and frankly my brain hurts and I'm sure it's something extremely simple!

Here's a select statement I wrote to try to simplify what I need to do for myself:

Select EnteredBy, DateReceived, ProcessedBy1, Received1, ProcessedBy2, Received2 from Files where EnteredBy, ProcessedBy1, ProcessedBy2 = [criteria specified in unbound field] and DateReceived is between [dates specified on unbound field]

Any help would be greatly appreciated.

Thanks! =)

 

[boblarson]

Well, sorry to give you the bad news, but your table structure is flawed for one. You should not have repeating columns. There should not be ProcessedBy1, ProcessedBy2, ReceivedBy1, ReceivedBy2, etc.

for those they should be in another table which stores the many to the one in a one-to-many relationship which you have there.

 

[Galaxiom]

The data structure is denormalised. Having multiple fields for the processing information makes it more difficult to produce the report.

There should be a table for the processing events with fields for FileID, ProcessBy, ProcessType and ProcessDate.

Entering is just another process. This structure also allows an unlimited number of processes on each file.

 

[eburgtech]

While I completely concur withe the normalization issues...unfortunately, I inherited this database from my boss. I do know that there is no possibility of me being authorized to restructure the core of the database. Is there a work-around for crappy situations like this?

Also, the ProcessedBy1, ProcessedBy2, ReceivedBy1, ReceivedBy2 fields are not the actual names. I work for lawyers and was trying to use generic field names for my question. =)

 

[Galaxiom]

Use a Union query to rearrange the data in to a normal structure. Then the results you need are just a matter of grouping by file and user then counting the records in the groups.

 

[eburgtech]

Wanted to say that I did figure out a way to do this. I created a report with 3 sub-reports. This way, I was able to create 3 separate queries for 3 separate reports but put them all on one visually stunning report. ) I was also able to create a selection form where the manager could select a date range and type in the username of the Analyst and run the report. The report then runs all 3 queries and compiles them. It's a rather complicated and round-about way, but it worked. Thanks.

 

[Galaxiom]

First rule of database design:
Happy Manager = Success

 

[boblarson]

Yep, sometimes the people above you choose your path regardless of how sane or rational it may be. One can only attempt to help them steer but in the end if they insist, there's not a lot you can do. Sometimes it isn't to your benefit to win the battle.