I have just started using Microsoft Access 2007 and having some issues when comes down to join my tables together.
I have three tables: site details, history and equipment
History and equipment tables have the same foreign key ([site details].ID).
So I have created a Main Form (site details) and two subforms (history and equipment) in it but in different tabs.
Now this is the SQL statement i'm using
SELECT [Site Details].*,
History.ID AS ID_History,
Equipment.[Store ID] AS [Store ID_Equipment]
FROM ([Site Details] LEFT JOIN History ON [Site Details].[ID]=History.[Store ID]) LEFT JOIN Equipment ON [Site Details].[ID]=Equipment.[Store ID];
The problems i'm having are two:
1) The equipment subform duplicates the site details.
When there is more than one equipment item linked to the same site details, the site details record is shown an amount of times related to the number of equipment records linked to it.
I.E. I have 3 equipment records linked to 1 site details. In the form, the site details is shown 3 times and in the equipment tab of each site details record replication, there are the 3 equipment records
I would like to have 1 site details record and the equipment tab showing the 3 items linked to it
2) The notes don't seem to be linked to the site details.
In the notes (history table) tab the notes are independent from the site details table. So I can skim through them as if it was an independent table
I would like to have the notes shown on the history tab for each of the site details record
Thanks for reading this long post... I have tried to explain the as accurate as it could be