column value in row

[waqas]

dear experts,

i have values in rows like in first row 2, second row 8, third row 15, fourth row 21,...
how can i get these values in one row like 2, 8, 15, 21,....

kindly guide me

[June7]

How many different values are there - can a crosstab query work? If not, review:
http://forums.aspfree.com/microsoft-...ry-322123.html
http://allenbrowne.com/func-concat.html

[waqas]

thanks. but i need these value with coma in one cell because i have other information to display in other columns.
but these rows values should in one cell of the query result.

[June7]

That sounds like you want to concatenate the data from related records into a single text string. That is the code in the second link I reference.

[waqas]

i have data as below.

IDNUM	TandA	LastDay
405772	Absent	01-Jun-12
405772	Absent	05-Jun-12
405772	Absent	07-Jun-12
405772	Absent	10-Jun-12
411370	Absent	01-Jun-12
411370	Absent	02-Jun-12
411370	Absent	07-Jun-12
411370	Absent	08-Jun-12

i need result as below.

idnum count dates
405772 4 1,5,7,10

how can i write a query?

[June7]

Need VBA procedure. Like the Allen Browne record concatenation link already referenced.

[waqas]

but i need in access not in excel vba.

[June7]

The Allen Browne procedure has nothing to do with Excel. It is VBA for Access.

[waqas]

ok fine.

but now i am facing an error ( error 3061:too few parameters.Expected 1. )

in Allen Browne procedure i am passing below information as procedure requirement.

AbsentDat: ConcatRelated("format([sday],'dd')","[tblovertime]","[sday] between datefrom() And dateto() and " & "[leavetype] = " & 'Absent' & " and [idnum] = " & [idnum])

kindly check where i am wrong.

[June7]

The function is using the literal string "format([sday],'dd')" as the field name. Since there is no field in tblOvertime named "format([sday],'dd')'', code will fail.

Use a query as the data source if you need to modify the values, such as extract the day from date. Include all native fields needed for the function and construct other fields by expression.

Also, the concatenation in the WHERE argument doesn't make sense.

"[sday] Between " & datefrom() & " And " & dateto() & " AND [leavetype] = 'Absent' AND [idnum] = " & [idnum])

datefrom() and dateto() are custom functions that do what?

[waqas]

i am using below query but result is coming empty and table data as below kindly check where i am wrong.

SELECT TblOverTime.IDNUM, ConcatRelated("[sday]","[tblovertime]","[sday] Between " & datefrom() & " And " & dateto() & " AND [leavetype] = 'Absent' AND [idnum] = " & [idnum]) AS AbsentDat, Sum(IIf(TBLOVERTIME.leavetype="Absent",1,0)) AS Absent
FROM EmpMasterList LEFT JOIN TblOverTime ON EmpMasterList.ID = TblOverTime.IDNUM
WHERE (((TblOverTime.EmpGrade) In ("DPW13","DPW14","DPW15","DPW16","DPW17","DPW18"," DPW19")) AND ((TblOverTime.SDay) Between DateFrom() And DateTo()))
GROUP BY TblOverTime.IDNUM, TblOverTime.EmpCC, EmpMasterList.CardUsing
HAVING (((TblOverTime.IDNUM)=405973) AND ((TblOverTime.EmpCC)=[Forms]![WorkHrs]![lstCC]) AND ((EmpMasterList.CardUsing)=True))
ORDER BY Sum(IIf(TBLOVERTIME.leavetype="Absent",1,0));

IDNUM	SDay	LeaveType	NormHrs	OT	Premium	ActualTHRS	FridayDate	RTime	PoolStaff	EmpGrade	EmpCC	Late	EarlyOrLate	RostimaRole
405973	01-Jun-12		8	0	0	8	Friday	06:00-14:00		DPW16	3121
405973	02-Jun-12		8	0	0	8		06:00-14:00		DPW16	3121
405973	03-Jun-12	Absent			0					DPW16	3121
405973	04-Jun-12	OFF	0	0	0	0				DPW16	3121
405973	05-Jun-12	Absent			0					DPW16	3121
405973	06-Jun-12	OFF	0	0	0	0				DPW16	3121
405973	07-Jun-12	Absent			0					DPW16	3121
405973	08-Jun-12		8.5	0	0	8.5	Friday	22:00-06:00		DPW16	3121	Check Roster	Early
405973	09-Jun-12	OFF	0	0	0	0				DPW16	3121
405973	10-Jun-12	Absent			0					DPW16	3121
405973	11-Jun-12	OFF	0	0	0	0				DPW16	3121

[waqas]

dear june7.

datefrom() and dateto() are custom function which pass the date from and to.

kindly see my query in last mail and let me know what should i do.

[June7]

Might need the # delimiter for date values:

"[sday] Between #" & datefrom() & "# And #" & dateto() & "# And "

The query is calling the custom functions. Show code for the functions.

What values are returned by the functions? Step debug. Review tutorial in link at bottom of my post.

[waqas]

dear june7
in allen procedure query string return as below query.

SELECT [sday] FROM [tblovertime] WHERE [sday] Between 6/1/2012 And 6/12/2012 AND [leavetype] = 'Absent' AND [idnum] = 405973

but when i put this "[sday] Between #" & datefrom() & "# And #" & dateto() & "# And "

it is working fine but when i scroll a query result right or left or up or down then it start work again.

query reslut become very slow.

[June7]

Yes, other users have commented that the code can be slow with large datasets. Yes, the function reruns when scrolling because it is refreshing the display. There are other examples of VBA to concatenate records but I suspect they will have the same issue.

What happens if you build a report with the query instead of viewing the query directly?