Split String? (1 Viewer)

[Big Al]

Hi, not sure if this should be in the table or report form.

I have a table that lists a large number of equipment. One line of equipment can be anywhere from 1 item to 300 items. Of those multiple items, I will have some that require serial numbers individually, thus the requirement to depict them as 25 single line items. But 300 wastebaskets can remain 300 wastebaskets on a single line.

If given the choice between the two (if only one rule exists) I would much rather have 300 individual lines for the wastebaskets, than have one single line for 25 serial numbered items.

I looked through the split string function, seems like a lot of code. I've used code once for a comparative report against two tables. Not sure I could do that again.

Bottom line is that I need to be able to produce a report that shows the eaches on a single line and another report that has a pivot table look that shows 300 wastebaskets and 25 Defibrillators on two lines.

If something similar has been brought up in the past, I can use the link, if not, any assistance would be greatly appreciated.

 

[plog]

I fear this isn't a Report issue, but a table issue. From your description of your table, it sounds like you are not storing discrete pieces of information in different fields/records, but have them all jammed into one field of one record.

But, I'm not entirely certain. Could you post some sample data demonstrating what you have and what you want?

 

[Big Al]

I don't have permissions yet to post data, but you are correct.

I've taken the list of equipment quantities and stretched them out before importing them into the table.... But there are always instances where I find it necessary to convert yet another record from one to many.

Is there a way to get the table to pay attention to the quantity and provide that number of individual records?

 

[plog]

You can just type sample data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 14, 2/3/2009
Sally, 71, 4/18/2007

What you need to do is structure your data properly, never build forms and reports off an improper structure. Without data though, I can't provide specific help.

 

[Big Al]

Gotcha, here it is... although I think if I understand you correctly, I will have to list the items individually before loading them into a table.

Nomenclature, Qty Req'd, Priority
Lensometer, 3, blank
Corneal Topography Unit, 4, Long Lead
Stool Self Adjusting, 60, blank

 

[plog]

You've confused me. I asked for two sets of data, what you have and what you want. I don't know which you posted. I do know that you shouldn't have non-alphanumeric characters in your field names (no apostrophes nor spaces).

What you posted is the way your data should be structured, so I have no idea what your initial post was concerning. It should be simple to get that data into the report you want.

 

[Big Al]

Sorry...

This is what I have:

Nomenclature, Qty Req'd, Priority
Lensometer, 3, blank
Corneal Topography Unit, 4, Long Lead
Stool Self Adjusting, 60, blank

This is what I want:

Nomenclature, Qty Req'd, Priority
Lensometer, 1, blank
Lensometer, 1, blank
Lensometer, 1, blank
Corneal Topography Unit, 1, Long Lead
Corneal Topography Unit, 1, Long Lead
Corneal Topography Unit, 1, Long Lead
Corneal Topography Unit, 1, Long Lead
Stool Self Adjusting, 1, blank
Stool Self Adjusting, 1, blank
Stool Self Adjusting, 1, blank
Stool Self Adjusting, 57 more times and so forth.



I have the list of equipment required in bulk, I can directly upload that into Access and begin working with the data... however much of the equipment will require a serial number, and because of that I would like Access to be able to list the one line item several times based upon the quantity in the record.

If this isn't possible, than how about the inverse? Can I, after much work in excel to split each quantity into individual records, depict the data in a report where the Cornea Topo machines are shown on a single line with a quantity of four?

 

[plog]

Ok, yes you can do that, but its going to require some VBA code. This is a problem that's been solved before on this forum (I don't have a link) and code exists. In general here's what you need to do:

Create a new table where your resulting data will end up in. You can probably just make a copy the structure (but not data) of your existing table. Then you build code to read in the source table, loop through every record and then run a loop from 1 - the value of that record's [Qty Rec'd] field that inserts 1 record into that table.

That code isn't horrible complicated to write--10 lines maybe--give it a shot and if you get stuck post back here.

 

[Big Al]

Huge thanks. I'll give her a run and see if I can get it done. Thanks much.

 

[Big Al]

I'm fixing to call it a night. I wasn't able to come up with the code although I think I found the right bit of code that needs to be modded for my approach. But I wasn't able to discern where this code would go. Right now I have two exact tables, one is devoid of any data, the other is the compacted, or condensed, table.

I tried putting the code in modules and in class... the only location in which I found to add code. But I'm missing something. Will try again in the morning. This is the code that I found.

Private Sub Sourcing_Equipment_Condenced()
With Me.QTY_Required.Table.RecordsetClone
If .RecordCount <> 0 Then
.MoveFirst
Do Until .EOF
![Qty_Requirement] = ![Quantity]
.MoveNext
Loop
End If
End With

 

[olek_w1]

Please advice,

Do you have solved above problem?

Because if not i have some idea