Help with query needed

[Rupert Bennett]

After numerous unsuccessful attempts, I have come to the realization that I need some help with this. Although my query returns the results I asked for, it does not present the data that would make it useful for a report or a form. This query lists all instances of each person who has taken vacation this year and how many hours were taken each time. However, for use in a report, I would like the data summarized, so that if someone had multiple instances of vacation taken, that name would be listed once only and a total given for the number of hours used for the year so far for that person.
I have tried a crosstab query, but my lack of knowledge in this area, caused it to return the data in just about the same manner as the select query.
The fields in my query are; Empl#, FirstName, LastName, StartDate, EndDate, HoursUsed. I would be extremely grateful to anyone who could tell me in some detail what type of query I should use and how to structure it to return the data in the manner stated above.
TIA
Rupert

 

[Marbles]

Have you tried a Totals Query. Group by employee number, and you might need DateDiff to total the hours over 24.

 

[MyBoo]

Have you looked into creating a report, based on the query you created? This is a bit easier to manage than trying to do everything from within a query.

You list Empl#, FirstName and LastName as three of your fields. Are these, along with startdate, enddate and hoursused all from one table?

Don't think you have to create one query to get the results you want... you can create one query to pull specific data and then create another query, based on that query to get additional data... and so on, and so on.

You can also use the DISTINCT keyword within your queries to limit your results.

 

[Rupert Bennett]

Thanks for the replies received from MyBoo and Marbles. To respond to both, I have tried to create a report from these queries. However, because the queries list multiple entries, I have trouble combining that into a single entry for each employee. All the fields are from one table.

I have not tried a Totals query, as my Ms Access skills are somewhat limited. I will research this and see what I can accomplish with it. The datediff and other calculations are not a problem. The only problem at the moment is, if someone took 3 days vacation in March, 5 in June and 2 in August, I do not know how to get my query to to only list one entry for this person and also say 10 days used. A day is calculated as 8 hours.

I have been able to get this done, but only if I restrict the query to one empl# and sum that on a form. I am looking to query the entire database and report one line for each person, accumulating hours used where there are multiple entries for any individual.
In the meantime, I will do some reading on the suggestions you made and hopefully I will stumble onto something.
Rupert

 

[Marbles]

You might have to make a calculated field where the number of days off *8 would give the hours. then to make your query total, click on the total symbol on the toolbar.

 

[Rupert Bennett]

Thank you, Marbles. It worked just like I want it to. I built a test table with lots of data mimicking the situation described, but only showing days used. I then made a query with a calculation for totalHours (days*8). I then made another query with the first query as the source and summing the TotalHours. It gave me one row for each person and a total of all the hours used to date for each person.
I cannot explain how happy this has made me. I have been working on and off for several days now, with only frustration for my efforts. Thanks again to you and MyBoo. This is simply wonderful.
Rupert