Create Report with one user name and dates

[dtolemy]

I have a report that I have created and I have a VBA as well but it is only pulling in the dates like start and end it is not allowing me to pull in the employee name as well so that I can produce a report by specific dates and by a specific employee. What am I doing wrong.

[NeoPa]

DTolemy:
What am I doing wrong.

You have asked a question without first giving enough thought to what it is you're trying to ask for. As it stands it makes very little sense. It suffers greatly from a lack of information and context.

[dtolemy]

I have a DB that I created that contains Outages that we have. I have the techs assigned to the outage along with their manager that they report to. I also have the date the incident occurred and the date it was resolved. The issue I am having is that I want to be able to report the outages by the Tech that was assigned to them along with the start and end date of each outage.

I have created a report and I can either pull in the start and end dates or the tech, but not both at the same time. I have tried multiple things by creating different Events with VBA Code and can not get them to work together. I know it can be done, I am just not sure how to do it. That is why I am asking for help. I also understand your response and hope that this is much clearer.

[NeoPa]

Hi.

It's still not fully clear, but the attempt is always appreciated. I suspect with more practice and experience you'll see more easily what is relevant and needs to be included. One point to realise is that this work of stipulating a question correctly is nearly always extremely helpful to the original poster of the question (You in this case). We here who've seen a few questions over the years can tell you that so many questions become unnecessary once the poster has fully worked out what they need to ask.

In this case it seems like there are multiple tables. I'm guessing to a certain extent so tell me if I go off-piste at all. What is stored in which table is critical to understanding how the tables should be joined within a query to produce something the report could use.

I assume you have an Outages table of some sort. I'll refer to it here for simplicity as [tblOutage]. It would have two date fields for the start and end we'll refer to here as [StartDate] & [EndDate]. It would also have a PK (Primary Key) that identifies each item uniquely (Possibly [OutageID]). Another table contains personnel for holding the tech and their manager. Again, we'll refer to it here as tblPersonnel unless/until we hear otherwise. If the table contained a PK field ([PersonnelID]) then we could also use that to indicate who is the manager of each entry.

Do you have data in roughly that setup? That's the sort of basic understanding we'd need in order to get to the query, which is your requirement.

[dtolemy]

You are correct, I have a table called outages that holds my dates and all of the data as it is input. I also have a table called Employees that holds my Tech Names and their managers. My outage table also has the tech and manager name, but when I set them up in the outage table, I set them up as numerical fields so when I look at the data in a datasheet view instead of seeing the actual names I see the numbers associated to them.

All I want to do is be able to select and report on the tech by select dates. I really appreciate your help and understanding on this. I am really trying to get it right.

[NeoPa]

So, we have two tables; Dates, Tech ID & Manager ID in the [Outages] table and Employee ID & Employee Name in the [Employees] table.

You want to see Techs and all Outage data that falls between two dates entered onto your form as filter parameters. You make no mention of wanting to see the manager data so I'll ignore that for now.

BTW A better approach (See Database Normalisation and Table Structures) would be to have the manager associated with the tech directly rather than entered every time in the Outages data - unless you are worried about techs changing their managers over time. I'll leave that with you to consider.

The following SQL should give you what you need. To understand what I did with the dates have a look at Time Interval Overlapping (MS Access 2003) :

SELECT [Employees].*
     , [Outages].*
FROM   [Employees]
       LEFT JOIN
       [Outages]
  ON   [Employees].[EmployeeID]=[Outages].[TechID]
WHERE  ([Outages].[FromDT]<[EndDT])
  AND  ([Outages].[ToDT]>[StartDT])

NB. [FromDT] & [ToDT] are both points in time. Date fields holding both date and time. This is also true of [StartDT] & [EndDT] which I assume are extracted from a form somewhere, but if not set elsewhere they will be prompted for automatically when the query runs. A date without any time element is essentially the midnight at the start of that day.

Obviously, you know the fields you need better than I so I've left all fields in the query. Once you have it running you can change that easily to specify just what you need from each table.

[TDawg]

Not sure if that query will work. I don't believe that it will return any records. If the StartDT and EndDT are listed as May 1 through May 31 and in the table we have a record that was opened (FromDT) on May 5 and closed (ToDT) on May 7, then you are saying return all records where May 5 < May 31 and May 7 < May 1. I think it needs to be ToDT >= StartDT.

[NeoPa]

TDawg:
I think it needs to be ToDT >= StartDT.

I could have sworn I fixed that before posting. Thanks for catching it.

I don't believe the "=" is needed in this instance as I don't imagine a job finishing at the time the window starts would be considered to be within the window, but if you or the OP disagree then that's an option.

[dtolemy]

I wanted to thank you for your help. I have decided to go back to the drawing board and completely redo my DB. I think I can stream line it more now and really get it to do what I need it to do. As you stated plan, plan plan. Thanks again

[NeoPa]

That's a courageous (I saw your database don't forget), but ultimately wise decision. It will mean a deal of work, but what you will learn during that process will certainly serve you well going forward.

Best of luck.