Help with query needed

Rupert Bennett

Active Member
Joined
Nov 20, 2002
Messages
271
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Marbles

Well-known Member
Joined
Feb 7, 2005
Messages
622
Have you tried a Totals Query. Group by employee number, and you might need DateDiff to total the hours over 24.
 

MyBoo

Board Regular
Joined
Aug 9, 2006
Messages
217
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

Active Member
Joined
Nov 20, 2002
Messages
271
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

Well-known Member
Joined
Feb 7, 2005
Messages
622
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

Active Member
Joined
Nov 20, 2002
Messages
271
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
 

Forum statistics

Threads
1,141,924
Messages
5,709,375
Members
421,633
Latest member
Ubergribbler

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top