Query help

[printsol]

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

[June7]

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;

[printsol]

is there any way to change field data type in linked sheet?

[June7]

If you format Excel sheet cells as date and time, Access should recognize them as date/time type and CDate() would not be needed.

[printsol]

Thanks alot i vll try this

[printsol]

If you format Excel sheet cells as date and time, Access should recognize them as date/time type and CDate() would not be needed.

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

[June7]

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