perform group by on form

[luzz]

Hi all, i would like to ask if it is possible to group all the similar data as one on the form?

For example:
Current data on listbox
Line 1 Blue 300gm
Line 2 Blue 200gm

End result on the form after retrieving these two data:

Blue 500 gm

 

[CJ_London]

only by using a group by query as a recordsource to the form. Note the form will not be editable

 

[luzz]

only by using a group by query as a recordsource to the form. Note the form will not be editable

Does this apply if i am using an unbound form?

 

[CJ_London]

if the form is unbound then you can edit the form - but you can't update a group by query

 

[luzz]

if the form is unbound then you can edit the form - but you can't update a group by query

Oh, so the only way to group data is through query?
Because I thought that after I selected the data on the list box and retrieve it on the form the same data will be group and shown as one.

I attached an image of the sample data for your reference.

For instance PO 100000958 has 6 lines of data and each line have a color, qty and yards.

And as you can see from the image, PO 100000958 line 2-5 have the same colour but in different qty and yards. It is hard for me to use query to group them together as they belong to different Lot NO. Hence, i am thinking if i am able to retrieve them to the form and it will be able to group the same colour and sum the qry and yards?

 

[CJ_London]

suggest you experiment. in your example, to get the result you want you would group by colour and sum by yards and lbs. Based on the data shown, You can include all the other columns except lot number and line number.

But it will not be editable. you would have one row, but that row is made up of a number of different records - the db does not know which record you are editing.

 

[luzz]

suggest you experiment. in your example, to get the result you want you would group by colour and sum by yards and lbs. Based on the data shown, You can include all the other columns except lot number and line number.

But it will not be editable. you would have one row, but that row is made up of a number of different records - the db does not know which record you are editing.

Okay, Thank you!