report on one line

[ryetee]

I have a database that basically reports on jobs processed.
A job can have 1 to may failures
A job can have 1 to many parts replaced
The parts and failures are not necessarily related

I want to be able to be able to produce a report and excel spreadsheet that has everything on one line rather than multiple lines.
If for example I have replaced 4 parts and the job has 5 errors I get 20 lines on my report. I want to produce 1 line
eg
I have
job1 part1 failure1
job1 part1 failure2
job1 part1 failure3
job1 part1 failure4
job1 part1 failure5
job1 part2 failure1
job1 part2 failure2
job1 part2 failure3
job1 part2 failure4
job1 part3 failure5
job1 part3 failure1
job1 part3 failure2
job1 part3 failure3
job1 part3 failure4
job1 part3 failure5
job1 part4 failure1
job1 part4 failure2
job1 part4 failure3
job1 part4 failure4
job1 part4 failure5
I want
job1 part1,part2,part3,part4 failure1,failure2,failure3,failure4,failure5

Any ideas how to accomplish this?

I'm using Access 2010 and the report is basde on a query that produces 20 lines as well

 

[vbaInet]

1. Create a query to return unique jobs
2. Add two concatenated fields of the parts and failures to this query using this code:
http://allenbrowne.com/func-concat.html
3. Use this query as the base of your report

 

[ryetee]

1. Create a query to return unique jobs
2. Add two concatenated fields of the parts and failures to this query using this code:
http://allenbrowne.com/func-concat.html
3. Use this query as the base of your report

OK I'll give it a go if I can work out what goes where!

 

[ryetee]

1. Create a query to return unique jobs
2. Add two concatenated fields of the parts and failures to this query using this code:
http://allenbrowne.com/func-concat.html
3. Use this query as the base of your report

OK I'm getting there. I have a query that returns unique jobs now and have put this into the query
Concatfield: ConcatRelated("RepairSymptomID","[Jobs Fails]","[Job ID] = " & [jobs].[job id])

This returns the ID (1 to many) of the fail (repair symptom). What I want however is the code of the repair symptom which is held in another table.
So I the following tables
Jobs Table
-----------
JOb ID
other fields

JOb Fails Table
-----------------
ID
Job ID
RepairSymptomID

ReapirSymptom Table
-------------------------
ID
Repair Symptom Code

Not sure how to do this....

 

[jdraw]

I'm a little confused with your requirement.
You didn't mention repair in post 1. Has something changed?

What I want however is the code of the repair symptom which is held in another table.

Perhaps you could post a copy of your database showing the issue and describing how to get to that issue. Remove anything confidential/private before
zipping and posting.

 

[vbaInet]

I understand your requirement.

Whichever query/table you listed in your first post, is the query/table you should be calling/using in the ConcatRelated() function.

 

[ryetee]

I'm a little confused with your requirement.
You didn't mention repair in post 1. Has something changed?

Perhaps you could post a copy of your database showing the issue and describing how to get to that issue. Remove anything confidential/private before
zipping and posting.

Sorry I'm doing this for various things and have inadvertently put the wrong field in - for repair code read failure code. I'm getting the IDs of the failures (eg 32,45,67,78 etc) and not the code (RC1, TX2, ZZ3, CV9 etc) which are in another table again.

 

[vbaInet]

jdraw to the rescue

It's pretty simple ryetee. Look at the table listing in your first post and create a query that includes the following (equivalent) fields: JobID, Part Description and Symptom Description. Use this query as the basis for the ConcatRelated() function.

Just ensure that the query with the unique jobs also includes the JobID in it.

 

[ryetee]

I understand your requirement.

Whichever query/table you listed in your first post, is the query/table you should be calling/using in the ConcatRelated() function.

Not entirely sure what you mean.
Are you saying I leave original query as is, and in the second query I return unique jobs which in itself has the ConcatRelated function in and refers to the first query viz

ConcatRelated("Failure COde","FirstQuery","ID = " & QUERYID)

 

[vbaInet]

First query as described in post #8

Second query is the unique jobs which will include the JobID and Job Description. You will then use the JobID and First query in the ConcatRelated() function.

Is this clearer?

 

[ryetee]

Not entirely sure what you mean.
Are you saying I leave original query as is, and in the second query I return unique jobs which in itself has the ConcatRelated function in and refers to the first query viz

ConcatRelated("Failure COde","FirstQuery","ID = " & QUERYID)

You're a star vbaInet - got it!!

May have a couple more problems once I've tweaked all this but it's looking good! Thanks gain.

 

[vbaInet]

Glad we could help ryetee. Let us know how it goes.

 

[ryetee]

OK spoke slightly to early!!
I'm getting what I want almost!!
In my first post I was getting 20 rows back where I wanted one.
I'm now getting 1 row returned but I'm still getting the repeating entries more than once.
So instead of getting
job1 "part1,part2,part3,part4" "failure1,failure2,failure3,failure4,failure5"
I'm getting
job1 "part1,part2,part3,part4,part1,part2,part3,part4,part1,part2,part3,part4,part1,part2,part3,part4,part1,part2,part3,part4" "failure1,failure2,failure3,failure4,failure5,failure1,failure2,failure3,failure4,failure5,failure1,failure2,failure3,failure4,failure5,failure1,failure2,failure3,failure4,failure5"

Sure it just needs a wee tweak but not sure how!

 

[vbaInet]

Looks like a problem with the query you're using in the function. Go back to that query and you should find that it's repeating itself. You will need to get rid of those redundancies before using it in the function.

 

[ryetee]

Looks like a problem with the query you're using in the function. Go back to that query and you should find that it's repeating itself. You will need to get rid of those redundancies before using it in the function.

Think I need 2 "feeder" queries, 1 for the failures and 1 for the parts.

 

[ryetee]

OK - this is all working well. I'm trying to output 33 columns to an excel spreadsheet. Only the first 14 are coming out.
Also my spreadsheet appears to have the date and time in cells A1 and B1 respectively. Can I stop this happening?

 

[ryetee]

OK everything sorted.
Big thanks again to vbaInet for pointing me in the right direction.

The spreadsheet problem was overcome by using docmd.transferspreadsheet instead of docmd.outputto

SOLVED!

 

[vbaInet]

The spreadsheet problem was overcome by using docmd.transferspreadsheet instead of docmd.outputto

Good job! Lesson learnt from this is that one should use specific tools for specific jobs.