0

I am not sure if this is possible to do in Access; I have been googling forums and have not found a solution yet that works...

Essentially there are a list of patients who provide multiple blood samples at different times. Each sample is collected and recorded with a rack barcode. Each rack barcode (sample) has multiple tube barcodes (parts of sample)

I have a database that has three tables.

ID info: Patient information (Patient ID, demographic information)

barcodes: A list of all samples collected for any patient (rackbarcode; tube barcode a; tubebarcode b)

BarCode Assignment: Rack Barcodes from barcodes table as they are assigned to each patient. (Patient ID, rackbarcode 1, rackbarcode2) Rack barcode 1 is sample collected at time point 1 and rack barcode 2 is a sample collected for the same patient at time point 2. Rack barcode 1 and Rack barcode 2 can both be fond as "rackbarcode" in the barcodes table.

I have an innerjoin between ID info and Barcode Assignments with the patient ID. I have 2 outerjoins between barcodes and Barcode Assignments. rackbarcode from barcodes table is linked to both RackBarcode 1 and RackBarcode 2 in the Barcode assignment table.

I want to connect patient ID info to the appropriate barcodes, for which i use BarCode Assignment table. There may be a better way to do that, and I am open to suggestions.

My end goal is that I want to run a query that shows me all sample information by patient:

Patient ID; Rack BarCode 1; Tube Barcode A1 Tube Barcode B1; Barcode 2; Tube Barcode A2; Tube Barcode B2

1 Answer 1

0

From your description of your database structure, it sounds like you need to join to two references of the Barcodes table, one reference for each rackbarcode.

For example:

select 
    ba.[patient id], 
    ba.[rackbarcode 1],
    b1.[tube barcode a],
    b1.[tube barcode b],
    ba.[rackbarcode 2],
    b2.[tube barcode a],
    b2.[tube barcode b]
from
    (
        [barcode assignment] ba inner join barcodes b1 on
        ba.[rackbarcode 1] = b1.rackbarcode
    ) 
    inner join barcodes b2 on
    ba.[rackbarcode 2] = b2.rackbarcode

The spaces in the fields names in your question were not consistent, and you referenced the same fields using different names at various points in your question, therefore I can't guarantee that I've used the correct field names in the above example.

Note that ba, b1 & b2 are merely aliases and do not need to be changed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .