Solved Comma to count Quantity and Separate Insert in the Report

[smtazulislam]

Hello, peace be upon your
I have a textbox [short text] in continuous form where I added data like

FacilityID	Date	Code	Items Received	Quantity	Total Quantity	Items Quality	Items Status
1	01/11/2022	2022110001	Laptop, Mobile Set, TV,	1	?????	Used	Current
2	12/12/2022	2022120024	Sleeping Accessories, Refrigerator Family Size	1	??????	New	Current

I tried to USING, LTrim, REPLACE, SPLIT function but at last not success to enter the new Text in the report.
I would like to insert "Items Received" put in the report page separate textbox after COMMA,

SL	Date	Code	Items Received	Quantity		Items Quality	Items Status
1	01/11/2022	2022110001	Laptop	1		Used	Current
2			Mobile Set	1		Used	Current
3			TV	1		Used	Current
4			Sleeping Accessories	1		New	Current
5			Refrigerator Family Size	1		Used	Current

 

[arnelgp]

i made a function that will split your Items Received field.
i call it it the Union Query.
then i made the Final Query.
from Final Query, i made the report.

 

[XPS35]

The real problem here is that the table is not designed correctly. If you can receive several goods under the same code, you need at least two tables. Your solution can cause all sorts of other problems. For example, what if you receive 1 laptop and 2 TVs? Or did you accidentally enter a full stop instead of a comma?

 

[smtazulislam]

Thank you very much, Appreciate SIR,

 

[smtazulislam]

The real problem here is that the table is not designed correctly. If you can receive several goods under the same code, you need at least two tables. Your solution can cause all sorts of other problems. For example, what if you receive 1 laptop and 2 TVs? Or did you accidentally enter a full stop instead of a comma?

Thanks for your advice.
I guess and understood what you are advice me. but I can't do that because, We need to subform display a line with on a textbox.
Actually it was inserted in pup up form, and also same sales invoice, Texes Invoice, Date is different, for an employee.
All of the thing discussed, we decide to created a textbox for all items in the sub-form.

 

[ebs17]

@XPS35 is mercilessly right, and not all problems have been mentioned.

Note: Assembling is easier and more efficient than separating. Therefore, in a database environment, atomic content will be stored in table fields (1st normal form) and content will only be assembled when required (for human viewing).

The database solution presented has the systematic problem that the UNION query must be derived from the unknown number of individual items. It can easily be that the set hardcoded number of 9 is not enough. So what? If you have to reprogram through some data more, you have a big problem. Furthermore, VBA.Split is not a very slim function, due to the repeated repetition and additionally via the UNION query, which subsequently does not allow index use, a performance problem has been created that then becomes noticeable with corresponding amounts of data.

 

[smtazulislam]

The database solution presented has the systematic problem that the UNION query must be derived from the unknown number of individual items. It can easily be that the set hardcoded number of 9 is not enough. So what? If you have to reprogram through some data more, you have a big problem.

Thanks, I getting your point.
So, this query UNION can't possible more then 10 items data, if I need more then I have to add and fixed how many items want to display.

 

[June7]

This is a situation where a multi-value field would be preferable to a comma separated string. I never use multi-value field but CSV is worse.

The desired report output is more like how your data should be saved - each item is a record.

There is a 50 SELECT lines limitation for UNION. Could just build the UNION with 50 lines and hope there are never more. Otherwise, use temporary table to write records for report. But would really be best to redesign schema. Your reason for not normalizing doesn't make sense. Might provide your db for analysis.

Advise not to use space in naming convention.

 

[arnelgp]

So, this query UNION can't possible more then 10 items data

you can extend the quey to how much items you want to "extract".
remember that the SQL string has limits as to how many characters of length.