Union Query or NOT?? (1 Viewer)

[DeborahP]

HELLO!

I need some help. I have a database that contains records about some telephone training. The user has an entire month to complete the training and can have several entries. I download this information and generate an autokey. I have created a query and found about 400 duplicates. Is there a way to combine the records. For example, joe calls and logs in 5 questions, the next time he calls , he logs in 4 questions and finally by the end of the month - he logs in the last 11 questions. He has a total of 20. I want to take the table and put all of Joe's records together.

Is this possible? Would I generate a union query? Most be complete the project in 2 calls.

Any suggestions would be great!!

Deborah

 

[Jon K]

The best way is to use a report.

But if you want to have a table, you can use a select query and a crosstab query. Assuming your table contains the text fields User and Question, type each query in the SQL View of a new query (using the correct table name):

qry1_UserQuestion:-
SELECT a.User AS User, a.Question AS Question,
"Question" & dcount("Question", "yourTable", "User = '" & [User] & "' and Question <= '" & [Question] & "'") AS QuestionNum
FROM yourTable AS a
ORDER BY User, Question;


qry2_UserQuestion:-
TRANSFORM Max(Question) AS QuestionsAsked
SELECT User
FROM qry1_UserQuestion
GROUP BY User
PIVOT QuestionNum;



Run the second query.

Note these limitations of the queries:-
1. The maximum number of questions per User is 254 as the maximum number of fields in a table is 255.
2. Questions will be displayed in alphabetical order, not in the order they were entered.
3. The table should not contain duplicate questions for a User.