Concatenate field into additional query rows

[jetersauce]

I have a frustrating problem that I could really use some help on (I'm not sure if the solution would be in the query or report)

Our company have labels that we print on samples for testing. We have to have a separate label for every dilution in a test.

What I need is a way so that when the field Department= "Micro" that the query (or more importantly report) will concatenate the PrepMethod field into additional rows in the query.

I need this

Test PrepMethod
APC 10, 100, 1000

to change into this

Test PrepMethod
APC 10
APC 100
APC 1000

The PrepMethod field will vary with the number of dilutions, but they are always seperated by a comma.

Examples
10, 100
100
1000, 1000, 1000, 1000
1, 10, 100, 1000

This LIMS database has the forms and tables locked by the company who sold it to us (so I can only edit queries or the reports). Thanks, I could really use some help on this,

 

[vbaInet]

From an Access (or relational database perspective), this is a design flaw.

In a properly normalized database, you would normally go from this:

Test PrepMethod
APC 10
APC 100
APC 1000

to this:

Test PrepMethod
APC 10, 100, 1000

What you're asking for is the process that people would go through when they're migrating from Excel to Access.

You can't do what you need without saving those values in a table and I think it's worth normalising.

 

[jetersauce]

Since it is a proprietary database, I cannot edit any existing tables. I can however add new ones.

Is there a way I could add a table that could allow the data to be displayed correctly in the query?

 

[vbaInet]

Here's some aircode that will transpose that table into a new table:

dim rs as dao.recordset
dim strSplit as variant
dim i as integer

set rs = currentdb.openrecordset("SELECT Test, PrepMethod FROM TableName WHERE Not PrepMethod Is Null;", dbopenforwardonly)

do while not rs.eof
    strsplit = split(rs!PrepMethod, ", ")

    for i = 0 to ubound(strsplit)
        ... code to Insert rs!Test and strsplit(i) into a new Table goes here ...        
    next

    rs.movenext

    ... code to Insert all records From your original table Into the new table Where PrepMethod Is Null goes here ...
loop

rs.close
set rs = nothing