How to create a string from a query (1 Viewer)

[pbutler]

Hi all,

I am having some trouble retrieving data from a query in the format I want it. I have a query where for each record there are 52 fields. Only three or four of those 52 fields actually have some data in them, the rest are empty. I need to set up a calculated field that will join the data from the columns that are not empty, but I cannot work out how to place a separation space between the data.
I think an example may be in order here:

Record 1: MU-103 EN-205 LE-303

When I join these three columns in my calculated field I get:
MU-103EN-205LE-303

But what I would like to get is: MU-103 EN-205 LE-303
or even better: MU-103/EN-205/LE-303

Any suggestions would be very much appreciated.

Thanks,
Pino

 

[bob fitz]

May be you could use something like:
Expr1:[Field1Name] & "/" & [Field2Name] & [Field3Name]
You would need to substitue Field1Name etc with the names of your fields.

BTW. Have you Normalized you tables? 50 fields seems like a lot of fields.

 

[pbutler]

May be you could use something like:
Expr1:[Field1Name] & "/" & [Field2Name] & [Field3Name]
You would need to substitue Field1Name etc with the names of your fields.

BTW. Have you Normalized you tables? 50 fields seems like a lot of fields.

Thanks Bob. I have tried that but I end up with lots of //// joining the empty fields. I am doing a timetable program and the 52 fields is the number of subjects on offer, so each year group may end up doing more than one subject on each period (splitting the year group, of course).

 

[bob fitz]

..I am doing a timetable program and the 52 fields is the number of subjects on offer...

IMHO Sounds to me like "Subjects" should be another table. As I said before, the tables need to be Normalised. You need to get your table structure right before you continue with making queries and forms. Without Normalised tables the queries will be more difficult if not impossible to construct.

 

[bob fitz]

You may find these links of some help:
http://allenbrowne.com/binary/Access_Basics_Crystal_080220_Chapter_03.pdf
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
http://www.databaseanswers.org/tutorial4_db_schema/index.htm
http://www.databaseanswers.org/data_models/index.htm

 

[Imyls]

I'm not any good with access but I would have used a rather long iif function.

But a question to the people who suggested creating additional tables

I'm creating a simple database that counts how many times a unique client visited a store and when.

Wondering which option is better

Option A
I have a table (Registry) with Clients ID#s and personal information and at the end of the 12-52 open columns (Visit 1, Visit 2, Visit 3, etc. There is a limited number of visits, either once a week or once a month, depending on store).

Option B
Since there are multiple stores, create a table for the store with 2 columns, ID and Visiting Date.

And then on the client form use a lookup formula to pick out vist dates?

 

[pbutler]

..I am doing a timetable program and the 52 fields is the number of subjects on offer...

IMHO Sounds to me like "Subjects" should be another table. As I said before, the tables need to be Normalised. You need to get your table structure right before you continue with making queries and forms. Without Normalised tables the queries will be more difficult if not impossible to construct.

Thanks, Bob.
The 52 fields are not on a table but on a query. Basically I am working on a timetable programme. I have a table with subjects, three fields. A table with periods, another one with teachers and another with classes. I then have a timetable table with a field for each of the above and each entry is a lesson (with the class, the sibject, the period and the teacher).
My problem comes when doing a report to show the lessons each class has throughout the week. Sometimes a class is split in two or three and they do different subjects. I have created a crosstab to compile all the subjects a given class is doing at a given period (hence the 52 columns). I have based a selec query on the crosstab and I have created a string similar to theone I gave in the example to show all subjects. My problem is I cannot introduce a space between the lessons, so it reads a bit strange. I have tried includding the separator but I end up with lots of slashes in my string. I also tried an Iif function but the query says the expression is too long to parse. So I am a bit stuck.

Thanks for your interest,
Pino

 

[bob fitz]

I'm not any good with access but I would have used a rather long iif function.

But a question to the people who suggested creating additional tables

I'm creating a simple database that counts how many times a unique client visited a store and when.

Wondering which option is better

Option A
I have a table (Registry) with Clients ID#s and personal information and at the end of the 12-52 open columns (Visit 1, Visit 2, Visit 3, etc. There is a limited number of visits, either once a week or once a month, depending on store).

Option B
Since there are multiple stores, create a table for the store with 2 columns, ID and Visiting Date.

And then on the client form use a lookup formula to pick out vist dates?

See the attached db for a very simplified idea of the tables that I would use.

 

[bob fitz]

Thanks, Bob.
The 52 fields are not on a table but on a query. Basically I am working on a timetable programme. I have a table with subjects, three fields. A table with periods, another one with teachers and another with classes. I then have a timetable table with a field for each of the above and each entry is a lesson (with the class, the sibject, the period and the teacher).
My problem comes when doing a report to show the lessons each class has throughout the week. Sometimes a class is split in two or three and they do different subjects. I have created a crosstab to compile all the subjects a given class is doing at a given period (hence the 52 columns). I have based a selec query on the crosstab and I have created a string similar to theone I gave in the example to show all subjects. My problem is I cannot introduce a space between the lessons, so it reads a bit strange. I have tried includding the separator but I end up with lots of slashes in my string. I also tried an Iif function but the query says the expression is too long to parse. So I am a bit stuck.

Thanks for your interest,
Pino

Is it possible to attach a copy of the db in A2003 mdb format.

 

[pbutler]

I only have it in access 2010, I am not sure if it can be converted back to 2003. I'll have a look tomorrow and will let you know. Anyway, I am thinking of trying to create a function.
Thanks,
Pino

 

[JANR]

Thanks Bob. I have tried that but I end up with lots of //// joining the empty fields.

You can try and use Null propagation when concationate your string.
Assuming that [Field1] ALWAYS have data:

Expr1: ([Field1]) & ("/" + [Field2]) & .... & ("/" + [Field52])

Note the "+" inside the perens, if the field is null the whole expression inside the perens is null and is omitted from your finished string.

If [Field1] CAN be a NULL then:

Expr1: Replace(Trim(([Field1]) & (" " + [Field2]) & .... & (" " + [Field52])), " ", "/")

It will be a LONG expression.

JR

 

[pbutler]

You can try and use Null propagation when concationate your string.
Assuming that [Field1] ALWAYS have data:

Expr1: ([Field1]) & ("/" + [Field2]) & .... & ("/" + [Field52])

Note the "+" inside the perens, if the field is null the whole expression inside the perens is null and is omitted from your finished string.

If [Field1] CAN be a NULL then:

Expr1: Replace(Trim(([Field1]) & (" " + [Field2]) & .... & (" " + [Field52])), " ", "/")

It will be a LONG expression.

JR

Well, that worked a treat! Thanks so much, one more trick for the bag

 

[JANR]

No problem, happy to help

JR

 

[bob fitz]

I like the solution proposed by JANR.

Pino,
In an earlier post you spoke about looking at using a function. I case it still of interest I have attached a simple db which uses a function. The expression would not be as long and iy will handle any number of fields.