Please help! I'm teaching myself access from some old books and am trying to make a employee database for my work. I created an employee table, an hours table (listing their hours worked), and a table showing when hours have been paid out. When I try to make a summery query to total all the hours racked up and then debited by each employee, I always get double records for the 2nd employee listed. For example, if I say the employee worked one shift of 4 hours, the summary query will say the employee worked 8 hours, and I can even get it to show the double records. What am I doing wrong?? I am so frustrated I want to throw my computer at the wall. If anyone can help me through this issue I would be SOOO greatful. My office is counting on me. Thanks a bunches.
-Celes

Hi Celes,

Employee databases are quite difficult. You might want to work through some simpler tutorials before you tackle the project. They have some nice tutorials on the Microsoft office site (http://office.microsoft.com/en-us/training/CR101582831033.aspx), for starters.

Can you post the SQL of your query and a printout of your relationships? Or just post your DB here (http://www.access-programmers.co.uk/forums/showthread.php?t=140587) and somebody will take a look at it.

It might be a while because this is a national holiday in the US, and I'm about to go out of town where I'll have no Internet connectivity. I'm sure some of the non-US folks would be happy to help, too.

Here are a couple of links that might help:

How To Upload A Database To The Forum (http://www.access-programmers.co.uk/forums/showthread.php?t=140587)

How to get your questions answered promptly (http://www.access-programmers.co.uk/forums/showthread.php?t=149429)

How To Ask Questions the Smart Way (http://www.access-programmers.co.uk/forums/showthread.php?t=85042)

Okay, I attached the file/data base file. If anyone can ever point in the right direction I would be SUPER greatful!!!

Okay, I looked and two things:

1. You should not use lookups at table level (see here for more: http://www.mvps.org/access/lookupfields.htm)

2. I had to pull each part of the query together separately and bring them together. See attached. (qrySumOfBoth)

Celes, database administators make top dollar for a good reason. This stuff isn't easy. It's challenging enough to design a database. Querying it can be even more challenging, especially since SQL syntax offers fewer tools than a full fledged programming language such as Visual Basic. For example you cannot easily use sql to loop through the records of the database, whereas in VB looping is standard.

In fact our company has a team of some 8 programmers and yet they outsource payroll to another company !!!

Sql is even more limited if you are using the graphical screens. If you actually learn the sql language, you get more flexibility because you put subqueries all over the place, wherever it is convenient.

Secondly, here is a tip for beginners. Don't try to do too much in one query. Instead accomplish one goal, because you can then write another query which makes use of the first query to get the second goal (you are allowed to query a query in the same way that you query a table). And then the third query will use the 2nd query to get the third goal done and so on.

For example, in your case you want a SUM. First work on getting that sum - don't worry about whether all the columns show up in the output. Because a GROUP BY - which usually doesn't lend itself to selecting all the columns at once - is used to accomplish a sum. Just use two columns (empID and Hours) to get this total.

I think you can succeed, but you will probably need (unless you only have one goal) to have a good grasp of

GROUP BY
HAVING
WHERE
INNER JOIN
LEFT JOIN
RIGHT JOIN
subqueries

Maybe I'll upload you some of my notes to serve as tutorials.

Jal, outsourcing of payroll is done for two reasons.
1. No one wants to keep up with the tax tables.
2. Security. You don't want internal programmers to have access to payroll records due to the temptation to peek and for the same reason, you don't want to print checks and paystubs in house.

Thanks everybody for your help.

Bob, what you sent me is great and I sort of understand about the lookup field, but now, this requires the user to know the VolID # for each employee when imputing/debiting their hours. Is there a way to put in the Vol Name and have it find/auto bring up the Vol ID. Is this something that can be done in a form, maybe?

Ok, I put together a little tutorial to get you started learning sql.

Thanks everybody for your help.

Bob, what you sent me is great and I sort of understand about the lookup field, but now, this requires the user to know the VolID # for each employee when imputing/debiting their hours. Is there a way to put in the Vol Name and have it find/auto bring up the Vol ID. Is this something that can be done in a form, maybe?
yes, in a form it is okay to use lookups with combo boxes or list boxes bound to the field.

Lookups are wonderful things and should ALWAYS be used in forms but NEVER at the table level. The fact that they can be applied at the table level is a crutch designed for people who are not really building applications but just working with tables and queries. Once you cross over to actually building an application where you will be using forms, reports, and VBA, lookups at the table level cause significant technical problems.