Help with select query!! 1

[UHNSTrust]

I have inherited an information system where the table structure does not help reporting. I have a select query which returns the following data structure

Name DataItem AnswerYN AnswerDate
Bloggs 1 True
Bloggs 2
Bloggs 3 01/01/07
Bloggs 4
Bloggs 5
Bloggs 6 True
Bloggs 7
Bloggs 8 01/02/07

This means:
Data item 1 would be question 1 with an answer of Yes.
Data Item 2 would be question 1 with an answer of No.
Data Item 3 would be question 2 with an answer of 01/01/07.
Data Item 4 would be question 3 with an answer of ward 1.
Data Item 5 would be question 3 with an answer of ward 2.
Data Item 6 would be question 3 with an answer of ward 3.
Data Item 7 would be question 3 with an answer of ward 4.
Data item 8 would be question 4 with an answer of 01/02/07.

The logic is that only one of Item 1 and Item 2 can be True and that there will never be a date in Item 1 and Item 2's record and that item 3 will never have a True/False but only a date etc)

What I want to be able to show is this (using the data from above)

Name Question1 Question2 Question3 Question4
Bloggs Yes 01/01/07 Ward 3 01/02/07


Can anybody help me out? Can this be done through queries? Or should I create a table and build up the data by doing lots of appends and updates?

Thanks in Advance

Jonathan

 

[r937]

i would fix the data

r937.com | rudy.ca

 

[UHNSTrust]

Fixing the data is not an option as the web based system was designed by a third party supplier so that new functionality could be developed by the users without the developer (i.e. new questionnaires with radio buttons and date fields).

I only have access to the data through an ODBC connection and have to report as best I can.

Thanks for the response.

Jonathan

 

[r937]

Or should I create a table and build up the data by doing lots of appends and updates?

= fix the data


r937.com | rudy.ca

 

[UHNSTrust]

Hi r937,

Sorry misunderstood what you were suggesting.

I will start having a look at it.

Thanks

Jonathan

 

[dhookom]

How about a crosstab query with SQL view of:

TRANSFORM Max(IIf([AnswerYN]=True,IIf([DataItem] Between 4 And 7,"Ward" & [DataItem]-3,"Yes"),[AnswerDate])) AS Expr2
SELECT tblRWHTrust.Name
FROM tblRWHTrust
GROUP BY tblRWHTrust.Name
PIVOT "Question" & Choose([DataItem],1,1,2,3,3,3,3,4);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[UHNSTrust]

Hi Duane,

Sorry for taking so long to come back to you. This is exactly what I need. Thanks.

I did however over simplify it because the column headings need to be proper headings and not just Question1, Question2 etc. Where you have Question1 I need 'Assessment?', Question2 would be 'Admission Date', Question3 would be 'Ward' and Question4 would be 'Discharge Date'.

I have not worked with crosstab queries much so hope you don't mind helping a bit more. I have tried amending the Column Headings property but this gives me blank column names.

Sorry for the over-simplification. Thanks again.

Jonathan

 

[UHNSTrust]

Re last post. When I tried amending the Column Headings property it did give me the column headings but the data was missing.

Sorry for the confusion.

Jonathan

 

[UHNSTrust]

Hi Duane,

Solved the problem with the column headings. For anybody else who wants to know the code Duane provided changes to;

TRANSFORM Max(IIf([AnswerYN]=True,IIf([DataItem] Between 4 And 7,"Ward" & [DataItem]-3,"Yes"),[AnswerDate])) AS Expr2
SELECT tblRWHTrust.Name
FROM tblRWHTrust
GROUP BY tblRWHTrust.Name
PIVOT Choose([DataItem],"Assessment","Assessment","Admission Date","Ward","Ward","Ward","Ward","Discharge Date");

However;
DataItem1 and DataItem2 are YES and No to Question 1. They are both stored as True (only one record is set to True at a time). It is the DataItem number that denotes whether it is a Yes or No. The code above gives the answer 'Yes' whether DataItem1 or DataItem2 is set. I hope this makes sense.

As always, any help is really appreciated.

Jonathan

 

[UHNSTrust]

Hi Duane,

I Have now sorted this with a lookup table which holds a Column Header description and a value for each Dataitem. I can then use this in the Crosstab Query for the column Header and Value fields.

Thanks for pointing me in the right direction. I am sure I will use this method a lot in the future.

I have given you a star for your efforts.

Jonathan