I created an Access application and I am running into some problems.
I would like to generate an output report based off of multiple tables in the database, using one joined field to locate the records. I have created the report and based it on a query that queries the three tables and returns all the matching records based off of the one joined field, which I have the user inputting via a parameter window (at the run-time of the query)...To do this I had to set the table relationships up so that they are all related based on the joined field I designated. When I run the query it works, but it returns multiples of the same record; probably because they are joined by that one field. The problem I am having is that these duplicate records are also appearing in the output report I have access generating. (i.e.- I am getting multiples of the same record on the report & I do not want that). FYI- the report fields (labels/text boxes) are pulling from fields within the query I set up. I tried to alter the report properties to "hide duplicates" but that does not seem to work. Any assistance would be greatly appreciated, as I am not a DBA by trade, so this is all fairly new to me. So I assume I am just doing something wrong. Below is the SQL statement I used to query the three tables and as the end result output to the custom report.
Here is my SQL statement:
SELECT DWGS.DWGS, Specs.Specs, DWGS.[Date Entered], Specs.[Date Entered], DTs.[Date Entered], DWGS.[DWG Number], Specs.[Specs Number], DWGS.[DWG Rev Number], Specs.[Specs Rev Number], DWGS.Quantity, Specs.Quantity, DWGS.[Trans Number], Specs.[Trans Number], DTs.Subject, DTs.[DT No], DWGS.[DWG Title], Specs.[Specs Title]
FROM (DWGS INNER JOIN Specs ON DWGS.[Trans Number] = Specs.[Trans Number]) INNER JOIN DTs ON (Specs.[Trans Number] = DTs.[DT No]) AND (DWGS.[Trans Number] = DTs.[DT No])
WHERE (((DWGS.[Trans Number])=[Enter Document Trans Num, (ex: DT-001)]));
The three tables are named: “DWGS”, “Specs”, “DTs”
The Query asks the user to input the “Trans Num”, which is the field that the three tables are joined by (this field is named “DT No” in the DTs table)…This field is the one that will locate the associated records. The output report is set up to list the following fields from the database based on the query:
DWGS.[DWG Number], Specs.[Specs Number], DWGS.[DWG Rev Number], Specs.[Specs Rev Number], DWGS.Quantity, Specs.Quantity; using the linked label/text boxes on the report. The query does work and returns the proper records, but it returns duplicates of the same record in the table and on the output report, which is not what I need. Could you possibly take a look at my SQL and see what I am doing wrong? I am fairly new with SQL, and I am probably doing the join incorrectly. Thanks in advance.
I would like to generate an output report based off of multiple tables in the database, using one joined field to locate the records. I have created the report and based it on a query that queries the three tables and returns all the matching records based off of the one joined field, which I have the user inputting via a parameter window (at the run-time of the query)...To do this I had to set the table relationships up so that they are all related based on the joined field I designated. When I run the query it works, but it returns multiples of the same record; probably because they are joined by that one field. The problem I am having is that these duplicate records are also appearing in the output report I have access generating. (i.e.- I am getting multiples of the same record on the report & I do not want that). FYI- the report fields (labels/text boxes) are pulling from fields within the query I set up. I tried to alter the report properties to "hide duplicates" but that does not seem to work. Any assistance would be greatly appreciated, as I am not a DBA by trade, so this is all fairly new to me. So I assume I am just doing something wrong. Below is the SQL statement I used to query the three tables and as the end result output to the custom report.
Here is my SQL statement:
SELECT DWGS.DWGS, Specs.Specs, DWGS.[Date Entered], Specs.[Date Entered], DTs.[Date Entered], DWGS.[DWG Number], Specs.[Specs Number], DWGS.[DWG Rev Number], Specs.[Specs Rev Number], DWGS.Quantity, Specs.Quantity, DWGS.[Trans Number], Specs.[Trans Number], DTs.Subject, DTs.[DT No], DWGS.[DWG Title], Specs.[Specs Title]
FROM (DWGS INNER JOIN Specs ON DWGS.[Trans Number] = Specs.[Trans Number]) INNER JOIN DTs ON (Specs.[Trans Number] = DTs.[DT No]) AND (DWGS.[Trans Number] = DTs.[DT No])
WHERE (((DWGS.[Trans Number])=[Enter Document Trans Num, (ex: DT-001)]));
The three tables are named: “DWGS”, “Specs”, “DTs”
The Query asks the user to input the “Trans Num”, which is the field that the three tables are joined by (this field is named “DT No” in the DTs table)…This field is the one that will locate the associated records. The output report is set up to list the following fields from the database based on the query:
DWGS.[DWG Number], Specs.[Specs Number], DWGS.[DWG Rev Number], Specs.[Specs Rev Number], DWGS.Quantity, Specs.Quantity; using the linked label/text boxes on the report. The query does work and returns the proper records, but it returns duplicates of the same record in the table and on the output report, which is not what I need. Could you possibly take a look at my SQL and see what I am doing wrong? I am fairly new with SQL, and I am probably doing the join incorrectly. Thanks in advance.
