Learn how to a build a cloud-first strategyRegister Now

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

Access 2007 Database Query/Report Help

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
Jokeefe1012
Asked:
Jokeefe1012
  • 2
  • 2
5 Solutions
 
Dale FyeCommented:
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
 
hnasrCommented:
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
 
Jim P.Commented:
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
 
Dale FyeCommented:
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
 
Jim P.Commented:
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

Industry Leaders: 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!

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