here's the query:
SELECT *
FROM (SELECT [Occurrence Number], [Occurrence Date], [1 0 Preanalytical (Before Testing)], [Cup Type], NULL as [2 0 Analytical (Testing Phase)], [2 0 Area], NULL as [3 0 Postanalytical ( After Testing)],NULL as [4 0 Other], [Practice Code], [Specimen ID #] FROM [Lab Occurrence Form]
WHERE NOT ([1 0 Preanalytical (Before Testing)] IS NULL)
UNION
SELECT [Occurrence Number], [Occurrence Date],NULL, [Cup Type],[2 0 Analytical (Testing Phase)], [2 0 Area], NULL,NULL, [Practice Code], [Specimen ID #] FROM [Lab Occurrence Form] WHERE NOT ([2 0 Analytical (Testing Phase)] IS NULL)
UNION
SELECT [Occurrence Number], [Occurrence Date],NULL, [Cup Type],NULL, [2 0 Area], [3 0 Postanalytical ( After Testing)],NULL, [Practice Code], [Specimen ID #] FROM [Lab Occurrence Form] WHERE NOT ([3 0 Postanalytical ( After Testing)] IS NULL)
UNION
SELECT [Occurrence Number], [Occurrence Date],NULL, [Cup Type],NULL, [2 0 Area], NULL, [4 0 Other] FROM [Lab Occurrence Form], [Practice Code], [Specimen ID #] WHERE NOT ([4 0 Other] IS NULL)
) AS mySubQuery
ORDER BY mySubQuery.[Occurrence Number];
for some reason it doesnt like [Practice Code]. it's definitely a column in the table so i dont understand the problem.
the error is
the microsoft office access database engine cannot find the input table or query 'Practice Code'........
解决方案
Well, the error says it all. It is not a table. In your question, you state that it is a column in a table.
You have [Lab Occurrence Form], [Practice Code], [Specimen ID #] right after a from, so it assumes all those listed there are tables.