 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008query construct question
I have a two tables that look like: Emp table Punch table employee------------employee lastname date firstname time department inorout The link between the tables is employee Each employee might have multiple ins and outs for a given date. I'd like to somehow create a query that would display something like: department employee date time in/out time in/out time in/out (left to right have multiple times shown along with whether an in or out. Is this a "crosstab" query and if so, how do I code it so it sequentially shows the time and in/out for say up to 6 or 8 punches? If there are more than 8, ignore and go to next employee date or employee...if less than 8 show them and go on to next employee Query would look like: Dpt Emp Date Time I/O Time I/O Time I/O 99 2000 01/01/08 8:30 In 11:30 Out 12:30 In... 99 2000 01/02/08 8:15 In 11:00 Out 11:45 In... 99 4000 01/01/08 8:00 In 12:00 Out 14:00 In... display up to 6 or 8 times with corresponding in/out flag... Or, if it's easier to somehow do it, it could go like this (and would even be better) Dpt Emp Date Time In Time out Time In Time out 99 2000 01/01/08 8:30 11:30 12:30 4:30 99 2000 01/02/08 8:15 11:00 11:45 16:00 99 4000 01/01/08 8:00 12:00 14:00 17:30 say up to 3 or 4 ins/out (6 or 8 total punches) displayed. Is this possible? thanks, ga George Applegate gappleg8@nospam.fmctc Doable, yes. . Assuming the dateStamp contains and the date and the time (of the IO) Assuming field IO contains "I" or "O" (for in or for out) kind of: employee dateStamp IO john 2001.01.01 09:00:06 i john 2001.01.01 11:58:12 o john 2001.01.01 12:49:16 i john 2001.01.01 17:02:08 o Rank the data by employee, by IO, by date, accordingly to the time, which can be done with the following query: ---------------------- SELECT a.employee, DateValue(a.dateStamp) AS theDate, TimeValue(a.dateStamp) AS theTime, a.io, COUNT(*) AS rank FROM tableName AS a INNER JOIN tableName AS b ON a.employee=b.employee AND a.io = b.io AND DateValue(a.dateStamp) = DateValue(b.dateStamp) AND a.dateStamp >= b.dateStamp GROUP BY a.employee, DateValue(a.dateStamp), TimeValue(a.dateStamp), a.io ------------------------- Save it under the name, say, qr1. Then, make the crosstab: ------------------------- TRANSFORM LAST(theTime) SELECT employee, theDate FROM qr1 GROUP BY employee, theDate PIVOT io & rank IN ("i1", "o1", "i2", "o2", ... , "i8", "o8") ------------------------- should produce something like: employee theDate i1 o1 i2 o2 ... i8 o8 john 2001.01.01 09:00:06 11:58:12 12:49:16 17:02:08 ... null null Hoping it may help, Vanderghast, Access MVP >I have a two tables that look like: > Emp table Punch table [quoted text clipped - 39 lines] > George Applegate > gappleg8@nospam.fmctc Michel, Thanks a ton for your help! You have me close, I think. The qr1 query puts out the data as you indicated emp# date time IO rank 2000 01/01/08 11:30 IN 1 2000 01/01/08 14:40 OUT 1 2000 01/01/08 15:30 IN 2 But when I do the Crosstab, I am not getting any times to actually show. The Cross tab has 2000 01/01/08 blanks blanks blanks blanks, blanks 2000 01/02/08 blanks blanks blanks blanks blanks I don't know if something is wrong with the PIVOT statement? It shows the headings but no times in them. Otherwise it seems to be working as advertised. One note - the field for InOut is "InOut" and second, the value in there is IN or OUT I made the Pivot statement: PIVOT InOut & rank IN("IN1", "OUT1", "IN2", "OUT2", "IN3"... Any ideas what I did wrong? The headings are there, just no times showing. Or is it something with my Transform statement I am just using the field which is Logtime and it seems to be in qry1 okay. Qry1 looks exactly right. It seems to be more of a problem with my PIVOT statement?? I get the employee # and the Log date in the crosstab query, just no times... Any ideas or suggestions?? By the way, THANKS A MILLION FOR YOUR HELP!!! ga >Doable, yes. . > [quoted text clipped - 94 lines] >> George Applegate >> gappleg8@nospam.fmctc George Applegate gappleg8@nospam.fmctc Try the crosstab without the IN list in the PIVOT: TRANSFORM LAST(theTime) SELECT employee, theDate FROM qr1 GROUP BY employee, theDate PIVOT io & rank That MAY creates more than 8 in or 8 out column, but you can fix that later. Be sure to take note ot the fields name created. They must be typed exactly as they are created, if we use the IN( ) list of the PIVOT clause (as string, ie, delimited by double quotes). Vanderghast, Access MVP > Michel, > [quoted text clipped - 35 lines] > > Any ideas or suggestions?? Michel, THANKS A TON! That did the trick. I think the problem is, I would have to put it as "IN 1" and "OUT 1", etc. The rank is apparently too many characters. But just doing it this way works great! Thanks much for all of your help. You take something extremely complicated and make it look like a piece of cake! ga >Try the crosstab without the IN list in the PIVOT: > [quoted text clipped - 50 lines] >> >> Any ideas or suggestions?? George Applegate gappleg8@nospam.fmctc You can use the VBA function TRIM to remove trailing spaces, before making the concatenation to rank : TRANSFORM ... PIVOT TRIM(io) & rank Vanderghast, Access MVP > Michel, > [quoted text clipped - 67 lines] > George Applegate > gappleg8@nospam.fmctc One other thing. The employee name comes from a different table...how do I tie that in? Do I connect that in the crosstab query or in the original query, if I want that to show. link is on employee # Can I do that??? thanks, ga >Doable, yes. . > [quoted text clipped - 94 lines] >> George Applegate >> gappleg8@nospam.fmctc George Applegate gappleg8@nospam.fmctc You can make another query: bring the crosstab and the table that will supply the name instead of the id number, and join them on their common id_number fields. Vanderghast, Access MVP > One other thing. > [quoted text clipped - 5 lines] > thanks, > ga .fmctc |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.