Combine Query??

[jereece]

I run queries against an SQL database where I have read only access. This database has the following fields

EventNumber
BriefDescription
DetailDescription
EventCode
CauseCode
SignificanceCode
Resolution

When users enter one of the 3 "Codes", they can enter as many as are appropriate. So an event may have 3 EventCodes, 2 CauseCodes and 2 SignificanceCodes for example.

When I run my query, I get a different record for each. So for the above scenario, I may get 12 records. Problem is I only want one record for this event. Using a query, how can I combine all the EventCodes together and the CauseCodes together and the SignificanceCodes together...maybe separated by a space or comma? If I have to copy the data down locally, that's okay. I am wondering if an Update query could be used somehow, but I am not sure how to do this.

Any help is appreciated.

Jim

 

[ajetrumpet]

When users enter one of the 3 "Codes", they can enter as many as are appropriate. So an event may have 3 EventCodes, 2 CauseCodes and 2 SignificanceCodes for example.

When I run my query, I get a different record for each. So for the above scenario, I may get 12 records.

That must mean that there is a record entered for each code, using the same Event for each record.

Problem is I only want one record for this event.

Then use a criteria method of querying; either by using criteria input boxes or the QBF (query by form) method.

Using a query, how can I combine all the EventCodes together and the CauseCodes together and the SignificanceCodes together...maybe separated by a space or comma?

What for?? Are you wanting to view only ONE record, but having that record list all of the possible codes associated with it??

 

[jereece]

Are you wanting to view only ONE record, but having that record list all of the possible codes associated with it??

Yes...that the whole point of my post. I want to see one record (because it's one event) but I want to see all the codes.

 

[neileg]

You can't do this in a simple query, you would need to use VBA.

Why do you want to see this in a query? Wouldn't a form/subform set up be much easier?

 

[jereece]

You can't do this in a simple query, you would need to use VBA.

Why do you want to see this in a query? Wouldn't a form/subform set up be much easier?

I was thinking I had seen it done in a query. I ultimately want the results in a report.

 

[jereece]

I am just reposting becasue I still don't have an answer about how to do this. Just to clarify, I want to view only ONE record, but having that record list all of the possible codes associated with it.

Thanks,
Jim

 

[twoplustwo]

Sounds like you might want a case statement or some SQL in VBA with a few IF/WHERE statements in?

If I'm understanding - you don't want all permutations of your query criteria right?

Could you give an example?

 

[neileg]

Sorting and gouping like this is easilly done in a form/subform or in a report. It's hard work in a query.

 

[jereece]

I want to end up with the information in a report but I thought I would have to build a query as the engine to support the report. If it can be done in a report without a query, that's fine. I may need a little help understanding how to do this.

An example would be as follows

Record 1
EventNumber 0001
BriefDescription Some text
DetailDescription Some more text
EventCode A1
CauseCode Z1
SignificanceCode H1
Resolution Some text

Record 2
EventNumber 0001
BriefDescription Some text
DetailDescription Some more text
EventCode A5
CauseCode Z2
SignificanceCode H4
Resolution Some text

Record 3
EventNumber 0001
BriefDescription Some text
DetailDescription Some more text
EventCode A2
CauseCode Z4
SignificanceCode H6
Resolution Some text

and what I want in a report would be like this,

EventNumber 0001
BriefDescription Some text
DetailDescription Some more text
EventCode A1, A2, A5
CauseCode Z1, Z2, Z4
SignificanceCode H1, H4, H6
Resolution Some text

 

[jereece]

I have made a subform before but not a subreport. How do I create a subreport and how would I configure it to show the combined data (e.g. EventCode A1, A2, A5)?

Thanks,
Jim