Using Prompt to select a column

[Steindawg]

I have a fairly simple table which is composed of a location on a row, and then has details from a quesionaire on the columns. Each question has a mean and a n column. So say for the cancer center I have a quesion tagged a1...so I will have an a1m and an a1n column. This is repeated for about ten questions. What I am trying to accomplish is using an append query which prompts you for the question you are looking at and then uses the prompt to bring back the n and m values for that question. So final results look like this....

Question mean number
Cancer A1 81.4 29
Cancer A2 79.3 26


So in the above example 81.4 would come from the A1m column and 79.3 comes from the A2m column. I have tried using the question field like

Questionnbr:[Question]---My prompt

mean: (Quesionnbr)m
number: (Questionnbr)n

I am sure my syntax is incorrect but I feel like it is easily fixed...thanks in advance...

 

[Fornatian]

I assume that your data consistents of 30 fields each grouped into 10 question areas with the mean and number like:

Q1 - Q1n - Q1m - Q2 - Q2n - Q2m - Q3 - Q3n - Q3m -etc...


1.If you are entering the data via Access then your data structure is incorrect, you should have 3 fields only, Question, Mean and Number.

2. If you data is coming from outside Access then you have two options(so far), either...

a. Build ten queries each referencing your ten questions pulling the appropriate fields as answers

b. Use a UNION query to forge the Ten sets of questions and answers into a three field table as described above, this way you are simply filtering on one field instead of ten.

Ian

 

[Steindawg]

Ian,

The data is coming from an external source, but I am getting it as a CSV file and just set up the macro in excel to import it as my predecessor did. Instead of doing the Union query, which is unfamiliar to me, I am just going to go back in excel and edit the macro to bring the file in as Center and question with a value for mean and number. Thanks for the assistance...

 

[Fornatian]

A Union Query is Not So Difficult

Given that you pass the same number of columns into each 'slice' of the Union query there is no problem, it is simply a way of 'stacking' data sources.

An example from A97 help:

SELECT [CompanyName], [City]
FROM [Suppliers]

UNION

SELECT [CompanyName], [City]
FROM [Customers]
ORDER BY [City];

An example for you would be

SELECT [Q1], [Q1n],[Q1m]
FROM [YourTable]

UNION

SELECT [Q2], [Q2n],[Q2m]
FROM [YourTable]

UNION
SELECT [Q3], [Q3n],[Q3m]
FROM [YourTable]

UNION
SELECT etc...

Then by using an append query you can add the results to a static table in Access. But whichever works for you is the best.

 

[Steindawg]

OK, I looked at the help but it wasn't quite clear, but using my example makes it seem pretty simple...I should be able to pull that off...thanks for the help...