asked on

Need help with creating summary queries in Microsoft Access correctly.

There are some questions I have regarding creating a summary query in a Microsoft Access database:

Question 1: Please note sample below provided:

With the query: qQuestion1a - I put in it to show me all time that is not considered "ST" - which is standard time. My criteria is <> "ST"
However I'm missing data as qQuestion2a shows there are some where the TimeType hasn't been filled out - how come those don't show in qQuestion1a?
Seems like a bug on Microsoft Access' part....

Question 2:

Basically I'm building a query that will give me all non-billable time for each employee. Some employees have non-billable time - others do not. My attempt with the qSummaryOfNonBillableperDisney only shows the employees that have non-billable time. However I want a query that lists each and every employee regardless if they have non-billable time or not.

How do I make such a query that would do this:

UserName NBT
Donald Duck        29
Mickey Mouse      27
Minnie Mouse      13
"George Geef ""Goofy"""
Daisy Duck

instead of this:

UserName      SumOfWorkHours
Donald Duck        29
Mickey Mouse      27
Minnie Mouse      13

Just to explain further - how I would attempt to do this...is creating a query that made a table of all Disney employess with the name and another field that has the total NBT hours. Then I would run an update query that would load in the values. Is this the correct method of doing this? Do you have to take these series of steps to create something like this for real? Or can you do this in one query?
ee-question-total-rpting.accdb

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

1st question

SELECT Employees.UserName, TimeSheetData.WorkHours, TimeSheetData.WorkDate, TimeSheetDataTypes.TimeType
FROM TimeSheetDataTypes INNER JOIN (Employees INNER JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID) ON TimeSheetDataTypes.TimeTypeID = TimeSheetData.TimeTypeID
WHERE (((TimeSheetDataTypes.TimeType)<>"ST")) OR (((TimeSheetDataTypes.TimeType) Is Null));

Open in new window

John Tsioumpris

2nd question

SELECT Employees.UserName, Sum(TimeSheetData.WorkHours) AS SumOfWorkHours, TimeSheetData.WorkDate, TimeSheetDataTypes.TimeType
FROM TimeSheetDataTypes RIGHT JOIN (Employees INNER JOIN TimeSheetData ON Employees.EmployeeID = TimeSheetData.EmployeeID) ON TimeSheetDataTypes.TimeTypeID = TimeSheetData.TimeTypeID
GROUP BY Employees.UserName, TimeSheetData.WorkDate, TimeSheetDataTypes.TimeType;

Open in new window

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

stephenlecomptejr

ASKER

Pat,

Appreciate the efforts to fix the sample for me and the suggestions on the design changes.
There are good minders for the future.

I have a continuation of this question in another post which I'll post later. Thanks again.

John Tsioumpris

@Pat the next time you make assumptions & comments for others please don't..My Solutions worked based on the info i had...if what i presented is not what the author wanted is another story...i just posted on what i thought is was the objective.

PatHartman

John, I didn't make an assumption. I actually tested the query. It did not produce the results shown by the picture Stephen posted. There was no summary and no left join so data for everyone was included. I did not have any information except the requested results either. I only tested your query because I thought I would learn something. I wasn't looking for a mistake.