Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

MS Access Need help building a report with two date queries

I have a report that looks like the following:

Calendar
(List of employee's that are out)
Date      Employee
1-7-13   Patrick
             Spongebob
1-8-13   Squidward

I would like to add to this report:

Date      Employee                             Project                          
1-7-13   Patrick                                   Window Replacement   (this record = Date 1-7-13)  
             Spongebob
1-8-13   Squidward

Problem:
When I tried adding Project and Project due date to the query that supplies this report with a relationship - Project due date - Employee out Date:
I can only receive records if one date equals another. It will list properly on the report but it will not show all the records.

I would like to see all records of Employee's and Projects listed by one date on the left side of the report.
0
DJPr0
Asked:
DJPr0
  • 5
  • 5
  • 3
  • +2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How is Project related to the "Date + Employee" fields? are they in two different tables? If so, how are those tables related?
0
 
DJPr0Author Commented:
I guess there not related - I only created a relationship to show the project &  employee info under the same date in the report which does not work properly.

The Report is derived from 2 different queries derived from two different tables.

query Employee:
Date      Employee                                                      
1-7-13   Patrick                                  
             Spongebob
1-8-13   Squidward

query Project:
Project                            Due Date
Window Replacement      1-7-13
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If they're not related, then I don't understand how you could build a query that would "relate" the data.  If you're just interested in showing Projects in the same Date range as is represented in Query Employee, then perhaps you could relate the "Date" field from the Employee query to the "Due Date" field in the Project query ... but I don't think that's what you're after.

If you could explain a bit more about the data, and how it's structured (and related) perhaps we could help futher.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DJPr0Author Commented:
I would like to create a calendar Report with employee & project data displayed.

If I create a report with the two queries it will look this:

Date      Employee              Project                           Due Date
1-7-13   Patrick                   Window Replacement     1-10-13 (dates don't match)

I would like for project due date to equal the employee's date, and if there is a project and no employee record - just show the project record.
Relationships will not work due to the records will show if they match (if I only have a project record for a particular date it will not show).
0
 
Jeffrey CoachmanCommented:
<Relationships will not work>
Then like LSM, then I am at a loss as to how this will work...

This report has been floating around here for a while...
Perhaps you should have gotten all the data in the report first, ...then worried about things like hiding dupes.
Because I am not sure if Hide dupes has any bearing on the issue here.

LSM is typically better at these "architectural" questions than I am, so please continue with him.
But at some point you may have to post a sample db for him to examine what you have so far, and what you now need.

JeffCoachman
0
 
hnasrCommented:
If link field is date, then a project with no date will produce a record with no assigned employees.

The record source of the report on this case:

Select * from projectQuery Leftjoin empQuery on projectQuery.theDate = empQuery.theDate

As boag2000 suggested, a database will help clarifying the issue.
0
 
DJPr0Author Commented:
Sample database is attached.

There is no relationship between the two tables/queries.

I just want to view the data together in one report.

I would like to add the project name to the report by due date I.E.
Instead of having two reports:
Employee Report:
Date      Employee
1-7-13   Patrick
             Spongebob
1-8-13   Squidward

Project Due Report:
Project                            Due Date
Window Replacement      1-7-13

Have one report:
Calendar
Date      Employee                Project
1-7-13   Patrick                      Window Replacement (due date = date on the left)
             Spongebob
1-8-13   Squidward
1-10-13                                 Replace Doors
1-11-13 Squidward
1-13-13 Patrick
1-15-13                                 Fix Basement Leak
                                             Repair Roof
ExpertsScheduleInputExample--2-.accdb
0
 
Jeffrey CoachmanCommented:
I am confused by this design.

In a typical database like this (Employees and Projects)
You "assign" a project to an employee.

You seem to be wanting to create employee dates and Projects dates totally seperate from each other, then create a query that matches the date ranges...

With your current design you run the risk of having no matches, or worse having issues where either data range in either table can change.

Are you quite sure this design is optimal?

I mean we can do what you are asking, but it may wreak havoc when you require yet another modification...

JeffCoachman
0
 
DJPr0Author Commented:
The employee task input schedule was an after thought and I may not be most favorably accomplishing my goal.

I don't really see a reason right now that the employee schedule needs to be tied with the projects table although It may be advantageous in the future.

Please explain how its advantageous to tie these tables together when you have two different dates to display on a report.

I do have the employee's tied to projects in the projects table for the reason of who is directing each project. Using separate employee tables due to employee's working for different company's and located in the same office. Instead of using these separate tables I thought in may be easier to create a new table with everyone together, sort of a separate module if you will.

Please let me know if you see room for improvement.

My current goal is just to view multiple information sources in one report. How could I add even more info dates I.E.
Employee schedule
Project due date
Project start date
Project actual completed date

I.E. I would like a versatile report that I could add events easily.

Previously you supplied me with a report that shows all dates with a sub report - this may be a method of producing my desired report, but I would like the option to just display the important dates without displaying all.
0
 
Jeffrey CoachmanCommented:
I'll let the other experts work with you here.
Perhaps I am not understanding something...

It seems like the design may need to be changed in either event...
Again, please remember that if you ask for something exact, that is what we will give you.
However, if we do not have a full understanding of the entire scope of your app, ..what we do as a solution in one place, may cause unforeseen problems somewhere else in the app.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Unfortunately I too am at a loss. I don't see how you can tie these two unrelated items together in a single report.

In cases like this, often the only way to accomplish your goal is to use a temporary table. Build a table that shows the data the way you need it:

Date
Employee
Project

And then fill that table using VBA. For example, you could INSERT The Date and Employee information from your Employees table, and then use VBA to lookup the project based on your criteria (I'm not sure what those criteria are, so really cannot help you with that).

Once you've built this table, you then build a report based on that table. You'd have to clear out the table and rebuild the data each time you need to run it.

On a side note: Access does not work well with unrelated data - and in fact, no relational database engine works well with unrelated data. In almost every case, we find that the root cause of this is an unnormalized data structure. If you want to pursue that, I'd encourage you to open a new question where you ask for input on the structure of your database. If you do this, be sure to provide (a) a copy of the database, with sample data and (b) a full writeup of the purpose of the database.
0
 
Matthew OzogCommented:
I agree with LSMConsulting.  You need to create a temporary table with the dates from both the employee and project tables.

You could build a table with just the dates and then do a join to the 2 tables in the report.

Either way, you will need to do some VBA coding to create the temporary table before displaying the report.
0
 
Jeffrey CoachmanCommented:
Agree #2...
;-)

That is why I questioned to original design.
As I stated, typical systems like this will have the project details listed in one table (ProjectID, Start date End date, ...etc)
Then have the employee data (EmpID, EmpName) in another, ...then a "Junction" table is used to assign an Employee to a Project:
(ProjID, EmpID)

Again, I am not sure I understand if you are trying to link (aka "Associate") EmpTimeOff with The task Start/End date...?
(If an employee is "Off" , then assign them to a Project?)

Again, these types of "design" issues I leave to experts who's specialize in this, ...as there is always a chance that I am just not understanding something here...

JeffCoachman
0
 
Jeffrey CoachmanCommented:
Or perhaps you should post more relevant sample data (in both tables)
Then post a clear graphical example of the exact output you are expecting *based exactly* on the sample data
0
 
DJPr0Author Commented:
Thanks to everyone for their input.

I will re-post to continue this question, with a new explanation and illustration strategy.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now