help with query

[pirate666]

hi, new user here.

i m developing an attendance system for my firm using vb.net to build the frontend and access 2007 as backend. here's my table structure-

table: logins
id
username
password
user category

table: log
id
name
machine-name
entry-type (in/out)
date
time

table: breaklog
id
name
machine-name
date
break-time

now the user has a nice little gui with a login, a logout & a break button.
when a user logs in, an entry is made with entry-type "In", and when he logs out an entry is made with entry-type "Out". once logged in, the break button is activated and when a user goes on a break he has to click on that and once he is back, he has to click that again which makes an entry in the breaklog table with the time he was on break.
in the end i need a table which shows username, login date, login time, logout date, logout time, workhour (time difference between the two), total break-time for the date, final workhour (workhour-total breaktime).
i'm using this query to get that-

SELECT log.name,
log.date,
Min(log.time) AS login_time,
log.date,
Max(log.time) AS logout_time,
(DateDiff("s",Min(log.time),Max(log.time))) AS working_hr,
Sum(breaklog.break-time) AS break,
(((DateDiff("s",Min(log.time),Max(time))))-(Sum(breaklog.break-time))) AS total_working_hr
FROM log INNER JOIN breaklog ON log.name=breaklog.name
GROUP BY log.name, log.date;

problem is when i run this query, the sum(breaklog.break-time) takes all the breaktimes under a user & doubles it. what m i doing wrong here?
i need the query to take only the sum of breaktimes for the particular user for that particular date, what changes should i make to achieve that?

sorry for the long post. please bear with me, i'm kinda n00b at these.

[EDIT: posting original database. thanx GalaxiomAtHome & jdraw for the advice]

 

[Galaxiom]

Time, Name and Date are reserved words. Also avoid using fieldnames with special characters.

Reserved words and the subtraction operator in the names may well be the problem where you have neglected to put square brackets around a name.

 

[pirate666]

those field names were just examples to give everyone an idea about the table structure, real field names r different (name is user_name, time is entry_time, date is entry_date etc, entry-type is entry_type etc...). i'm pretty sure my naming isnt the issue, i used underscore(_) for multi-word fieldnames. besides afaik access doesnt let u use those reserve words for field names anyways.

 

[jdraw]

pirate666,

There's no need to simplify the real names for the forum. As you can see from
GalaxiomAtHome's response, your sample naming has tweaked a concern. He has over 2000 posts and has seen most everything.

Perhaps you could repost with the real field names, or even post a version of your db
that has any confidential/non essential data removed.

Many on the forum do not have 2007 or 2010, so attaching an mdb format db may get you more responses.

Also, I would not count on Access preventing you from using reserved words. As many have said, get a good naming convention and stick with it.

For a list of words you should avoid and some rationale see
http://allenbrowne.com/AppIssueBadWord.html

 

[Galaxiom]

GalaxiomAtHome's response, your sample naming has tweaked a concern. He has over 2000 posts and has seen most everything.

And a similar number from my alter-ego Galaxiom

Also, I would not count on Access preventing you from using reserved words. As many have said, get a good naming convention and stick with it.

Access recommends against the most delicate of the reserved words but does not actually prevent their use. Most of the time they are tolerated so long as they are inside square brackets.

A good many more are functions which in the right context (following a table name and a dot) will be recognised as a fieldname without the square brackets. However since unqualified fieldnames can be used in queries, a moment's negelect can cause trouble.

In VBA the Date, Time and Now functions don't use the empty parenthesis that are required in the database expression so these names become even more dangerous.

Many more have meaning inside database servers and programming languages that use database connections. One day when the humble Access database grows to use a server those words will begin to cause trouble.

It is just a good practice to avoid these words from the beginning, along with spaces in names. Moreover using reserved words, special characters and spaces in names makes the code much more difficult to read. I saw one post here where SELECT had been used as a fieldname and it was a real pain to interpret queries.

Allen Browne has also provided an excellent free tool to check databases for reserved words. It is linked from somewhere not far from that list of reserved words.

 

[pirate666]

Hi. thanx for the advice.
i'm posting my original database with some data & converted to mdb for your convenience. as u'd see, the log & the breaklog tables are the main tables. what i need is a query that will take the first entry with entry type "in" & the last entry with entry type "out", the time difference between the two, sum of breaktimes for the date of "in", deduct it from the previous time difference to calculate the final working hour, & i need that for each agent for each day.

here's what i tried-

SELECT log.agent_ID, log.entry_date, Min(log.entry_time) AS login_time, log.entry_date, Max(log.entry_time) AS logout_time, (DateDiff("s",Min(log.entry_time),Max(entry_time))) AS working_hr, Sum(breaklog.breaktime) AS break, (((DateDiff("s",Min(log.entry_time),Max(entry_time))))-(Sum(breaklog.breaktime))) AS total_working_hr
FROM log INNER JOIN breaklog ON log.agent_ID=breaklog.agent_ID
GROUP BY log.agent_ID, log.entry_date;

this query is summing up all the breaktime entries under an agent's name irrespective of dates & then doubling it before getting deducted from the in & out time difference.

please help me design it rite. thanx in advance.

 

[pirate666]

help...anyone?