Solved

Access 2007 Database Query/Report Help

Posted on 2014-01-03
5
368 Views
Last Modified: 2014-02-02
Hi Everyone,

I need some help figuring out how to Create a query for an access database. The data is in two table "Employees" and "Trips". Employees contains 3 simple columns ID, FirstName, LastName; Trip contains Date, TripID, Emp1, Emp2, Emp3, Outcome.

I need to create 2 Reports. First is a report that is a list of Employees and under each employee it should like each outcome and a count of the trips with that outcome. Second is a list of TripID with each Employee that was assigned to it.

What is the best way to accomplish this?

I really need this urgently, thanks everyone.
0
Comment
Question by:Jokeefe1012
  • 2
  • 2
5 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 200 total points
ID: 39755852
Your challenge is that since your Trips table is not normalized, you will have to play with it a bit to get what you want.  The easiest way to do this is to create a normalizing union query.  It looks like:

SELECT TripID, [Date] as TripDate, Emp1 as EmpID, Outcome
FROM Trip
WHERE Emp1 IS NOT NULL
UNION ALL
SELECT TripID, [Date] as TripDate, Emp2 as EmpID, Outcome
FROM Trip
WHERE Emp2 IS NOT NULL
UNION ALL
SELECT TripID, [Date] as TripDate, Emp3 as EmpID, Outcome
FROM Trip
WHERE Emp3 IS NOT NULL

Save this query as qry_Normalized_Trips

For the first report, you need to join the Employees table to qry_Normalized_Trips and then group by employee and outcome to get the count, something like:

SELECT Employees.ID, Employees.FirstName, Employees.LastName,
             Trips.Outcome, Count(Trips.TripID)
FROM Employees
LEFT JOIN qry_Normalized_Trips
ON Employees.ID = qry_Normalized_Trips.EmpID
GROUP BY
SELECT Employees.ID, Employees.FirstName, Employees.LastName, qry_Normalized_Trips.Outcome

I used an outer join for this one because you probably want to identify the employees who have not been on a trip as well.  For the second, use:

SELECT Trips.DripID, Trips.[Date] as TripDate, Trips.Outcome, Employees.LastName, Employees.FirstName
FROM qry_Normalized_Trips
INNER JOIN Employees
ON qry_Normalized_Trips.EmpID = Employees.EmployeeID
ORDER BY Trips.[Date], Employees.LastName
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 100 total points
ID: 39756126
I go with fyed, but modify:

For maintenance purposes, use the union query in the join:

Select ....
From (Select ... Union All select ...) As qry_Normalized_Trips
ON ...
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 39756539
Then as far as normalization should go.

EmployeeTable
EmpID, FName, LName, [other details]

TripTable
TripID, Location, Reason, [other details]

TripEmpXRefTable
TripID, EmpID, Date, Results

The last table Then this gets into requirements for your setup:
Do you have a limited list of locations?
Do you have a limited list of trips?
Do you limit the results?
Is it a one time trip or repeating?
Do all employees have the same outcome?

Just throwing some side thoughts out for what you want to do in the future for your DB design.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 200 total points
ID: 39756552
I agree with Jim regarding the need for the third table, this is a common flaw in db's that are built by people who are used to working with spreadsheets (got a second phone number, just add another phone # column, ...)

However, based on the way you have the design setup now, I think the TripDate belongs in TripsTbl.  And depending on whether you individual assessments of the results, or a single result, you could put that field in TripsTbl or in TripEmpXrefTbl.
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 200 total points
ID: 39756604
Agreed Dale. But I was trying to get to the idea of building on what is known and unknown.

For example, if you (the company) are responsible for sending sales staff for your butcher knives to various stores in multiple parts of the country, then the date probably belongs to the trip.

But if your company is a chain with xx locations, and you are sending out the HVAC, electrical, XXX team to that location then the date probably belongs to the XREF table.

And that is another design Q. Is the trip table and/or the location tables limited? As in the possible locations limited. Then maybe that should be a separate table and added into the XREF table.

A lot of times to really get a good normalized DB design you need to have a high level meeting with the involved departments (users) asking what they expect from the DB. Then there is the second meeting between the DBA(s) and tech types on what the data is that can be captured and how the layout should be.

My last company built numerous Excel Spreadsheets by department and some Access DBs that were all stovepiped. Some of it even occurred in separate chunks of the same department.

We (as in the IT staff) came in and slowly integrated the day to day stuff into a flow pattern that took 80% of re-keying  of duplicate data off the table.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

12 Experts available now in Live!

Get 1:1 Help Now