Hello,
Thank you for taking the time to read this. I'm trying to create a query where my header breeding group needs to pull data from either the sire table or dam table. Both the sire and dam table have a fishID and breeding group which is common to both tables and my query search for a fishID that corresponds to either the sire or dam ID and pull out the breeding group information.
I've tired to create a union query but I have an error in the breeding field. The error message is "syntax error (missing operator) in query expression 'Breeding Group'. This is the SQL code I am using.
SELECT [MYPed-MS].PITtag, [MYPed-MS].FishID, [sireINFO-MS].[Breeding Group], [MYPed-MS].FamID, [MYPed-MS].Status, [MYPed-MS].[Status Date], [cryo-MS].[Milt sample Code], [cryo-MS].[N cryopack], [cryo-MS].[N cryovials (1,8ml)], [cryo-MS].[Freezing date], [cryo-MS].[N thawed cryopacks]+[N thawed cryovials] AS [N thawed samples], [cryo-MS].[Sample color code], [cryo-MS].[Canister/Rack Number]
FROM ([MYPed-MS] LEFT JOIN [cryo-MS] ON [MYPed-MS].FishID = [cryo-MS].FishID) LEFT JOIN [sireINFO-MS] ON [MYPed-MS].SireID = [sireINFO-MS].FishID
WHERE ((([MYPed-MS].FamID)=[Forms]![searchKeyEntry].[famID]))
union
SELECT [MYPed-MS].PITtag, [MYPed-MS].FishID, [damINFO-MS].[Breeding Group], [MYPed-MS].FamID, [MYPed-MS].Status, [MYPed-MS].[Status Date], [cryo-MS].[Milt sample Code], [cryo-MS].[N cryopack], [cryo-MS].[N cryovials (1,8ml)], [cryo-MS].[Freezing date], [cryo-MS].[N thawed cryopacks]+[N thawed cryovials] AS [N thawed samples], [cryo-MS].[Sample color code], [cryo-MS].[Canister/Rack Number]
FROM ([MYPed-MS] LEFT JOIN [cryo-MS] ON [MYPed-MS].FishID = [cryo-MS].FishID) LEFT JOIN [damINFO-MS] ON [MYPed-MS].DamID = [damINFO-MS].FishID
WHERE ((([MYPed-MS].FamID)=[Forms]![searchKeyEntry].[famID]));