Solved

MS Access Need help building a report with two date queries

Posted on 2013-01-07
15
366 Views
Last Modified: 2013-01-09
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
Comment
Question by:DJPr0
  • 5
  • 5
  • 3
  • +2
15 Comments
 
LVL 84
ID: 38751909
How is Project related to the "Date + Employee" fields? are they in two different tables? If so, how are those tables related?
0
 

Author Comment

by:DJPr0
ID: 38751957
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
 
LVL 84
ID: 38752470
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
 

Author Comment

by:DJPr0
ID: 38752856
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38752942
<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
 
LVL 30

Expert Comment

by:hnasr
ID: 38753490
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
 

Author Comment

by:DJPr0
ID: 38754583
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38755521
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
 

Author Comment

by:DJPr0
ID: 38756111
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38756128
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 38758129
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
 
LVL 1

Expert Comment

by:Matthew Ozog
ID: 38758672
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38759038
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38759517
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
 

Author Comment

by:DJPr0
ID: 38759586
Thanks to everyone for their input.

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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now