Multiple Values on Same Line

[jkfeagle]

I have a query that generates grouped data following the general outlineignore the hypens - they are just to space out the headings so it looks representative of the structure)


(Column Headings)-----Line1------------Line2--------Line3
Product
---Quality
--------Type
------------- Code------xx--------------xx-----------xx
-------------------------yy--------------zz

The catch is that the code field can have several values for each prodution line and I want to simply list them one after another like xx,yy,zz on that line of the report. I can't come up with any way to list them together except in a column. What I want it to end up looking like is:


---------------------------Line1------------------Line2----------------Line3
Product
----Quality
--------Type
------------Code----------xx, yy------------------xx, zz------------------xx


Can anyone think of a way to do this?

 

[Dave Eyley]

Join the fields together for the report by using a bit of code -

Depending on the datatype of the fields -

If they are string fields -

Result=[Field1] & "," & [Field2]

where Result is an unbound field on the report.
the 2 fields.visible=False, so you only see the combined Result.

If they are numerical fields -

Result=Str$([Field1]) & "," & Str$([Field2])


This changes the fields into string data before combining them.

The =Str$([Field1]) & "," & Str$([Field2]) bit could go straight into the ControlSource of the Unbound Field - Result

This process is called concatenation. A big word meaning 'linking or joining together'.

Hope this is what you are looking for?

Dave E

 

[jkfeagle]

Dave,

Thanks for the info. I think we're headed in the right direction except the numbers I'm joining together are not different fields. They are the same field with multiple values under those categories. Hope this makes it a little clearer. Thanks. Any other ideas?

 

[Dave Eyley]

No, sorry. It's not clear to me at all.

Can you show me what a typical Code field contents would look like in the table, then perhaps I can grasp the concept.

It's only me being thick, I'm sure.

Dave E

 

[jkfeagle]

OK. I'm going to try to explain myself a little better. The data in the table will be grouped according to similar values in three levels as seen in the above post. All the values in the fourth field are listed in the report in a column under each other. An example would be that on the report under the field 'vegetables' are listed

carrot
lettuce
pepper

They way I want the report to list the related data is

carrot, lettuce, pepper

So you can see there are different values within the same field. The report will have a number of value groups like above within the same field based on the other levels of grouping. Does that make it any clearer or make it worse?

 

[Dave Eyley]

I think so...

Somehow you've got to get Access to do something it's not designed to do, i.e. look at many values of the same field at the same time!

The only way I can see to do this is to pre-process the data before printing the report, but that's not easy because you may not know how many categories or codes are going to be sourced by the query.

I will think about this one for a while....

Maybe someone else will have a better solution?

Dave E

 

[jkfeagle]

I think I know how I'm going to do this, I just need to sort through the code. If someone has something similar to send me and save me some time it would greatly be appreciated (I have a lot of coding experience unfortunately little experience using code to manipulate recordsets). I am first going to write the different groupings to another table without the data I want to gather onto one line (already done). In other words, the four fields that group the specific field we are concatenating data for. Then I am going to try to code a module to look at the new table, find the records in the original table that match those field values and loop through the values in the fifth field, concatenating them to a single variable which then will be written to the appropriate field in the new table.

 

[Dave Eyley]

That was what I had in mind when I said about using code to pre-process the data before printing the form.
But the more you think about it, the harder it seems to get!

Restructuring the tables may help, although you've still the problem of predicting how many categories and codes under each category.

Good Luck...

Dave E