Solved link tables in a query (please urgent)

[drhassan]

Hello everyone

I'm trying to link two tables (table names: add sample + antibiogram data) by a query (query name: Collected antibiogram) in my database (attached) but the showing data is only for one table. I'm not sure how to fix that as I'm still beginner?! Can you help?

 

[SHANEMAC51]

Hello everyone

I'm trying to link two tables (table names: add sample + antibiogram data) by a query (query name: Collected antibiogram) in my database (attached) but the showing data is only for one table. I'm not sure how to fix that as I'm still beginner?! Can you help?

ID		Sample ID	Antibiotic Name	Result	Method	Ïîëå1
1		12221	Norfloxacin	R
2		12221	Garenoxacin	S
3		5555	Ciprofloxacin	R
4		5555	Doxycycline	S
5		5555	Azithromycin	R
6		33511	Minocycline	S
7		33511	Gentamycin	R
8		33511	Chloramphenicol	R
9		1	Doxycycline	S		correct
10		2	Tigecycline	R		correct
11		787777	Levofloxacin	R

Sample ID is not in the main table, there are 1-6

 

[SHANEMAC51]

Sample ID	Sample Code	Sample type	Sampling Date	Species	Pathogen suspected	Antibiotic Name	Result
1	12221	Chicken meat, Cuts up, Chilled	08.02.2022	Broilers	Salmonella	Doxycycline	S
2	33511	Dust	06.02.2022	Broilers	Salmonella	Tigecycline	R
4	787777		08.02.2022	Broilers
5			08.02.2022	Broilers	Salmonella
6	5555	Chicken meat, Cuts up, Frozen	09.02.2022	Broilers	Salmonella

 

[SHANEMAC51]

Sample ID	Sample Code	Sample type	Sampling Date	Species	Pathogen suspected	Antibiotic Name	Result
1	12221	Chicken meat, Cuts up, Chilled	08.02.2022	Broilers	Salmonella	Doxycycline	S
2	33511	Dust	06.02.2022	Broilers	Salmonella	Tigecycline	R
5			08.02.2022	Broilers	Salmonella

There is no Sample ID in the main table, there are 1-6
tables linked by Sample ID, not Sample Code

SELECT [Add samples].[Sample ID], [Add samples].[Sample type],
 [Add samples].[Sample Code], [Add samples].[Sampling Date],
 [Add samples].Species, [Add samples].[Pathogen suspected],
 [Antibiogram data].[Antibiotic Name], [Antibiogram data].Result
FROM [Add samples]
 LEFT JOIN [Antibiogram data]
 ON [Add samples].[Sample ID] = [Antibiogram data].[Sample ID];

 

[arnelgp]

you join the two table not by ID (which is autonumber) but by
[Add Samples].[Sample Code] and [Antibiogram Data].[Sample ID].

 

[drhassan]

You are wonderful guys. I don't know how i missed that. Many thanks