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