hi dudes
i m learner and newbie can any body help me
i need a query to get data from attendance sheet i am attaching excel file and output(required) table sample
Thanks
hi dudes
i m learner and newbie can any body help me
i need a query to get data from attendance sheet i am attaching excel file and output(required) table sample
Thanks
Link or import spreadsheet. Fields will possibly all be text type and will have to convert date and time values.
Query1: qryINOUT
SELECT TimeSheets.Name & ":IN" AS IN_OUT, CDate([Date]) AS Dte, Min(CDate([Time])) AS Data
FROM TimeSheets
WHERE (((TimeSheets.Status)="C/IN"))
GROUP BY TimeSheets.Name, CDate([Date]);
UNION SELECT TimeSheets.Name & ":Out", CDate([Date]) AS Dte, Max(CDate([Time])) AS Data
FROM TimeSheets
WHERE (((TimeSheets.Status)="C/Out"))
GROUP BY TimeSheets.Name, CDate([Date]);
Query2:
TRANSFORM First(qryINOUT.Data) AS FirstOfData
SELECT qryINOUT.Dte
FROM qryINOUT
GROUP BY qryINOUT.Dte
PIVOT qryINOUT.IN_OUT;
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
is there any way to change field data type in linked sheet?
If you format Excel sheet cells as date and time, Access should recognize them as date/time type and CDate() would not be needed.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks alot i vll try this
Thanks dear
query is working fine
let me know if there is any way to search by name through form?
if i want only specific person then how can it possible
thanks
I suppose could have dynamic parameter in either query that references a control such as a combobox on form. Because a CROSSTAB is involved, will have to use PARAMETERS clause. Review:
http://allenbrowne.com/ser-67.html#Param
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.