Version : MS ACCESS 2007
Database Structure:
I have developed a database which records observations of inspection to analyse and record the data. I have 3 main tables(details below) (total of 14 tables in the DB) that are all connected. However the query concerns only 2 of them (Table 1 and Table 2).
Table 1
QuestionID Autonumber
Question No. Text
Question Memo
Version 6 Yes/No
Version 5 Yes/No
Version 4 Yes/No
CrossLink Number
Table 2
ObservationID Autonumber
InspectionID Number
QuestionID Number
Observation Memo
Table 3
InspectionID Autonumber
Date Date
Location Text
Station Text
• Table 1 is a list of Questions used by the inspectors for the inspection. Each question has a specific Question number. The Question list is presently in its 6th edition (released last month). Editions prior to the 4th edition are not covered in the database. All questions of each edition are listed and the respective Yes/No is selected.
• Table 2 is a record of only the observations pointed out with the QuestionID from Table 1
• Table 3 lists the details of the inspection.
• Table 1 to Table 2 is a one-many relationship
• Table 3 to Table 2 is a one-many relationship
Difficulty:
Each new edition of the Questions are appended to Table 1 and the respective Yes/No is accordingly selected. Many questions are repeated in subsequent editions, however the question number may change. Also some new questions are included and some are deleted. Therefore subsequent inspection’s observations will be under the new question number.
Report Required:
A list of all observations listed under the new questions only.
My problem:
Until now, when we had only 2 editions (4th & 5th), and I had used self-join within Table 1 (CrossLink field) wherein I had entered the number of the question from the previous edition. (e.g. QuestionID 500 which belongs to the 5th edition would have CrossLink number 1 which will be the same question but in the 4th edition). I had then created a self-join and created 2 sets of queries; One that lists observations under the 5th edition and another that lists out observations of the 4th edition that have connected questions in the 5th edition. I would then append these together and create a single list which would make the report. This worked though with some limitations.
Now with the 6th edition, I am not sure if the above system will work. I also have a gut feeling that there might be a simpler method.
Request:
What I finally want is a list of observations listed under the 6th edition questions, that have relevant observations of the 5th and 4th editions.
I would really appreciate if anybody could point me in the right direction on how to solve this. Frankly speaking I am a newbie to Access and I learnt Access to create this database and learnt from the web and your website. I am familiar with VBA with sparse knowledge of SQL.
Database Structure:
I have developed a database which records observations of inspection to analyse and record the data. I have 3 main tables(details below) (total of 14 tables in the DB) that are all connected. However the query concerns only 2 of them (Table 1 and Table 2).
Table 1
QuestionID Autonumber
Question No. Text
Question Memo
Version 6 Yes/No
Version 5 Yes/No
Version 4 Yes/No
CrossLink Number
Table 2
ObservationID Autonumber
InspectionID Number
QuestionID Number
Observation Memo
Table 3
InspectionID Autonumber
Date Date
Location Text
Station Text
• Table 1 is a list of Questions used by the inspectors for the inspection. Each question has a specific Question number. The Question list is presently in its 6th edition (released last month). Editions prior to the 4th edition are not covered in the database. All questions of each edition are listed and the respective Yes/No is selected.
• Table 2 is a record of only the observations pointed out with the QuestionID from Table 1
• Table 3 lists the details of the inspection.
• Table 1 to Table 2 is a one-many relationship
• Table 3 to Table 2 is a one-many relationship
Difficulty:
Each new edition of the Questions are appended to Table 1 and the respective Yes/No is accordingly selected. Many questions are repeated in subsequent editions, however the question number may change. Also some new questions are included and some are deleted. Therefore subsequent inspection’s observations will be under the new question number.
Report Required:
A list of all observations listed under the new questions only.
My problem:
Until now, when we had only 2 editions (4th & 5th), and I had used self-join within Table 1 (CrossLink field) wherein I had entered the number of the question from the previous edition. (e.g. QuestionID 500 which belongs to the 5th edition would have CrossLink number 1 which will be the same question but in the 4th edition). I had then created a self-join and created 2 sets of queries; One that lists observations under the 5th edition and another that lists out observations of the 4th edition that have connected questions in the 5th edition. I would then append these together and create a single list which would make the report. This worked though with some limitations.
Now with the 6th edition, I am not sure if the above system will work. I also have a gut feeling that there might be a simpler method.
Request:
What I finally want is a list of observations listed under the 6th edition questions, that have relevant observations of the 5th and 4th editions.
I would really appreciate if anybody could point me in the right direction on how to solve this. Frankly speaking I am a newbie to Access and I learnt Access to create this database and learnt from the web and your website. I am familiar with VBA with sparse knowledge of SQL.
