Avatar of DJPr0
DJPr0
 asked on

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.
Microsoft Access

Avatar of undefined
Last Comment
DJPr0

8/22/2022 - Mon
Scott McDaniel (EE MVE )

How is Project related to the "Date + Employee" fields? are they in two different tables? If so, how are those tables related?
DJPr0

ASKER
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
Scott McDaniel (EE MVE )

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DJPr0

ASKER
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).
Jeffrey Coachman

<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
Hamed Nasr

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
DJPr0

ASKER
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
Jeffrey Coachman

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
DJPr0

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jeffrey Coachman

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.
ASKER CERTIFIED SOLUTION
Scott McDaniel (EE MVE )

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Matthew Ozog

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.
Jeffrey Coachman

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

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
DJPr0

ASKER
Thanks to everyone for their input.

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