I have a problem that i don't know how to explain properly. I have a union query that pulls data from 8 other queries/tables to form a history of any given animal.
I want to create a form with tick boxes to select which queries to include in the union query so that i can customise the output of the report.
My union query is :
This is the HistoryAI query which the union query calls:
The HistoryPurchased:
I think you'll get the idea...
I have a listbox that opens the report when double clicked. The reports record source is qryUKHistory which is this:
Sorry if i have not explained the problem very well, i can upload the db if required.
Any suggestions would be great.
I want to create a form with tick boxes to select which queries to include in the union query so that i can customise the output of the report.
My union query is :
Code:
SELECT [DOB]AS[DATE],[DAM]AS[ID],"Calved" AS[Expr1003],[String] AS[DETAIL]
FROM [HistoryCalving]
UNION ALL SELECT [AIDate],[TAG],"Bulling",[String]
FROM [HistoryBulling]
UNION ALL SELECT [DOB],[TAG],"Born",[String]
FROM [HistoryDOB]
UNION ALL SELECT [Purchase Date],[TAG],"Purchased",[String]
FROM [HistoryPurchased]
UNION ALL SELECT [Movement Date],[TAG],"Moved Off",[String]
FROM [HistoryMovement]
UNION ALL SELECT [AIDate],[TAG],"AI",[String]
FROM [HistoryAI]
UNION ALL SELECT [Footcare Date],[TAG],"Footcare",[String]
FROM [HistoryFootcare]
UNION ALL SELECT [AdminDate],[AnimalIdentification],"Medicine",[String]
FROM [HistoryMedicine]
ORDER BY [DATE];
Code:
SELECT qryAIRegister.AIDate, qryAIRegister.TAG, (IIf(Left([Action],9)="PD Test +",[Action] & " " & Left([Notes],2) & " Weeks Approx. " & [Notes],[Action] & " (" & [AIBull] & " " & [AIBullBreed] & ") " & [Notes])) AS [String], qryAIRegister.AI_ID, qryAIRegister.AIorBull AS Expr1, qryAIRegister.Notes
FROM qryAIRegister
WHERE (((qryAIRegister.AIorBull)="AI"))
ORDER BY qryAIRegister.AIDate, qryAIRegister.AI_ID;
Code:
SELECT AnimalRegister.[Purchase Date], AnimalRegister.TAG, AnimalRegister.[Purchased From], AnimalRegister.PurchasePrice, [Purchased From] & (IIf(IsNull([PurchasePrice]),""," - " & "£" & [PurchasePrice])) AS [String]
FROM AnimalRegister
WHERE (((AnimalRegister.[Purchase Date]) Is Not Null));
I think you'll get the idea...
I have a listbox that opens the report when double clicked. The reports record source is qryUKHistory which is this:
Code:
SELECT History.ID, History.DATE, History.DETAIL, History.Expr1003, AnimalRegister.Brand, AnimalRegister.Sex, AnimalRegister.Breed, AnimalRegister.DOB, AnimalRegister.DAM, AnimalRegister.[On Farm], AnimalRegister.Homebred, AnimalRegister.[Purchase Date], AnimalRegister.[Movement Date], AnimalRegister.[Moved To], AnimalRegister.PurchasePrice, AnimalRegister.SoldPrice, AnimalRegister.InCalf, AnimalRegister.NextDue
FROM AnimalRegister INNER JOIN History ON AnimalRegister.TAG = History.ID
WHERE (((History.ID)=[forms]![frmSearchHistory]![txtSelect]))
ORDER BY History.ID, History.DATE;
Sorry if i have not explained the problem very well, i can upload the db if required.
Any suggestions would be great.