Using Allen Browne's Concat module - how do I order by a field not in the query?

[bigalpha]

I have three tables:
1. A table of generator information (back up power generators). This is the 'main' table.

2. A look-up table of maintenance lists that encompass the generators. Every year or two, a new maintenance list is generated by a 3rd party. This is a listing of every generator that needs to be serviced during the length of the maintenance list period. For example, if there are 100 generators in 2011 the maintenance list will have 100 lines. If we add 23 generators and a new list is issued in 2013, that list will now be 123 lines long.

3. A link table that combines #1 and #2; since a generator will be on multiple lists.

I want to concatenate the Maintenance List name together but I want to sort that concatenation based on the dates they were issued. I can concat the values together with no problems. I can order them ascending or descending based on the field I'm concatenating no problem. Other than that, I'm stuck.

edit: add to clarify

 

[JHB]

Can't you sort them only on the "MaintListDate", or maybe I misunderstand your problem.

 

[bigalpha]

Can't you sort them only on the "MaintListDate", or maybe I misunderstand your problem.

I cannot sort the concatenated result by maintlistdate.

This is my initial code

date Concat: concatrelated("tedate","qryFullInventoryTE","qryFullInventoryTE.GeneratorInfoIDPK= " & [tblgeneratorinfo].[generatorinfoidpk])

and it produces this result:

February 2013 TE, March 2014 TE, October 2011 TE

Adding an 'order by' part

date Concat: concatrelated("tedate","qryFullInventoryTE","qryFullInventoryTE.GeneratorInfoIDPK= " & [tblgeneratorinfo].[generatorinfoidpk],"tedate desc")

Produces the following

October 2011 TE, March 2014 TE, February 2013 TE

which is correct.

However, changing the 'order by' part to "maintlistdate"

date Concat: concatrelated("tedate","qryFullInventoryTE","qryFullInventoryTE.GeneratorInfoIDPK= " & [tblgeneratorinfo].[generatorinfoidpk],"maintlistdate")

Produces

"Error 3093: ORDER BY clause (maintlistdate) conflicts with DISTINCT"

 

[JHB]

What if you do the sorting in a second query based on the query you've?

 

[bigalpha]

What if you do the sorting in a second query based on the query you've?

How would I sort

October 2011 TE, March 2014 TE, February 2013 TE

To

March 2014, February 2013, October 2011

If I sort in the query, it doesn't sort the items in each result, it just orders all the results in alphabetical order.

 

[JHB]

Okay I see it, do you have some sample data + the query, in a database we could play with?

 

[bigalpha]

Okay I see it, do you have some sample data + the query, in a database we could play with?

Sure, give me a minute to sanitize some data. I'll zip it as a 2003 format (it's 2013) now.

 

[JHB]

Fine with the 2003 format, because I have not 2013.

 

[bigalpha]

Ok, got the database all set up and it's attached.

qryFullInventoryTE is the base query that pulls out the data.

qryTEConcat is the query that concatenates the data from qryFullInventoryTE.

 

[JHB]

Try it now, report back if it is correct or has some failure.

If the query qryFullInventoryTE return DISTINCT values, then you don't need it in the Allen Browne's Concat module, and then you are able to sort on the MaintListDate.

 

[bigalpha]

Try it now, report back if it is correct or has some failure.

If the query qryFullInventoryTE return DISTINCT values, then you don't need it in the Allen Browne's Concat module, and then you are able to sort on the MaintListDate.

Thanks, that works a charm. So it was as simple as removing the DISTINCT from the code and/or the query so there was only one DISTINCT? I saw that you commented the line out of the code.

Wow, I feel like a dummy.

Kind of similarly, will this code always limit the result of the concatenation to 255 characters? I ask because when trying to concatenate some longer stuff, it always cuts off.

edit: Reason I ask is because a generator can have multiple comments so when I concatenate it, I get the same number on concatenated results as there are comments. When I try to limit the number of records, that's when it cuts off the concatenated result.

 

[bigalpha]

Thanks, that works a charm. So it was as simple as removing the DISTINCT from the code and/or the query so there was only one DISTINCT? I saw that you commented the line out of the code.

Wow, I feel like a dummy.

Kind of similarly, will this code always limit the result of the concatenation to 255 characters? I ask because when trying to concatenate some longer stuff, it always cuts off.

edit: Reason I ask is because a generator can have multiple comments so when I concatenate it, I get the same number on concatenated results as there are comments. When I try to limit the number of records, that's when it cuts off the concatenated result.

Nevermind, I'm just bad at this I suppose. I was including the query that I was pulling the data from and that was causing multiple records. I forgot I don't need to incorporate that query and that I can just call the concat function without it.

 

[JHB]

Thanks, that works a charm. So it was as simple as removing the DISTINCT from the code and/or the query so there was only one DISTINCT? I saw that you commented the line out of the code.

Wow, I feel like a dummy.

Good it works.
If you remove the DISTINCT from the code, then you need to be sure the query/table only has one entry for each combination, therefore I add DISTINCT to the qryFullInventoryTE and removed it from the code.
Don't feel like a dummy, it was just a different way of thinking.
To your other questions I think you found it out.