Displaying un-matched records in query/crosstab query

[lizo.consulting]

I'm working on a project that will facilitate comparing a large amount
of historical data stored in "horizontal" format to new data that is
being collected in "vertical" format. I need to turn the "vertical"
data "horizontal" in order to do data analysis and comparison.

The new data consists of two tables:

lst_antigens: list of possible agents that could be used in a
laboratory test

antigenName
Antigen1
Antigen2
Antigen3
Antigen4
Antigen5
Antigen6
Antigen7
etc....

tbl_TestResults: contains a variable set of test results by antigen,
depending on which the scientist has chosen to use for the particular
test.

trID TestID Antigen TestResult

1 57 antigen1 2.7
2 57 antigen3 0
3 57 Antigen4 27
4 57 Antigen7 35
5 58 Antigen1 15
6 58 Antigen2 2
7 58 Antigen4 3
8 58 Antigen 7 8


I need to turn data in tbl_TestResults from vertical to horizontal (by
TestID) in order to analyze the results against some historical data
(may even need to write additional rows to the table containing the
historical data). I've got a working crosstab query that successfully
turns the data in tbl_TestResults from vertical to horizontal by test
ID:

testID Antigen1 Antigen2 Antigen3 Antigen4 Antigen7
57 2.7 0 27 35
58 15 2 3
8

*However*, I need for the crosstab query to include one column for
each of the antigens located in the lst_Antigens, *even if* there are
no corresponding records in the tbl_testResults. For example:

testID Antigen1 Angigen2 Antigen3 Antigen4 Antigen 5
Antigen6 Antigen7
57 2.7 0
27 35
58 15 2
3 8

Note: even though antigen 5 and Antigen 6 have no results, I would
like to have them show in the crosstab.

The crosstab is based on a select query that joins lst_antigens to
tbl_TestResults (includes all antigens and the results in
tbl_TestResults that match), but I cannot get this select query to
include antigens with no test results (e.g. Antigen5 and Antigen6 are
eliminated).

Any suggestions on building a query that would return a one full list
of test results by Antigen for each Test ID, such as:

trD TestID Antigen TestResult

1 57 antigen1 2.7
57 antigen2
2 57 antigen3 0
3 57 antigen4 27
57 antigen5
57 antigen6
4 57 antigen7 35
5 58 antigen1 15
6 58 antigen2 2
58 antigen3
7 58 antigen4 3
58 antigen5
58 antigen6
8 58 antigen7 8


Thanks for your assistance,

Liz O'Donoghue
University of Washington
(e-mail address removed)

 

[lizo.consulting]

Noticed my table formatting for some of my posting got altered by my
browser.

The new data consists of two tables:

lst_antigens: list of possible agents that could be used in a
laboratory test

antigenName
Antigen1
Antigen2
Antigen3
Antigen4
Antigen5
Antigen6
Antigen7
etc....

tbl_TestResults: contains a variable set of test results by antigen,
depending on which the scientist has chosen to use for the particular
test.

trID TestID Antigen TestResult

1 57 antigen1 2.7
2 57 antigen3 0
3 57 Antigen4 27
4 57 Antigen7 35
5 58 Antigen1 15
6 58 Antigen2 2
7 58 Antigen4 3
8 58 Antigen 7 8

J need to create a select query that joins lst_antigens to
tbl_TestResults and includes antigens with no test results (e.g.
Antigen5 and Antigen6 are
eliminated).

Any suggestions on building a query that would return a one full list
of test results by Antigen for each Test ID, such as:

trD TestID Antigen TestResult

1 57 antigen1 2.7
57 antigen2
2 57 antigen3 0
3 57 antigen4 27
57 antigen5
57 antigen6
4 57 antigen7 35
5 58 antigen1 15
6 58 antigen2 2
58 antigen3
7 58 antigen4 3
58 antigen5
58 antigen6
8 58 antigen7 8

Thanks for your assistance,

Liz

 

[Jeff Boyce]

Not sure what kind of comparison you intend to do, but have you looked into
exporting the data as is into Excel, then using the Excel Transpose function
to swap vertical for horizontal? Spreadsheets generally offer more analytic
capabilities...

Regards

Jeff Boyce
Microsoft Office/Access MVP