CODEWITHSUNDEEP
sqldatabasems-access
Bob
Bob

Reputation: 21

Access report from two tables using selected rows from second table

I am trying to create Access report from two tables with already created relationship. My problem is that the second table has more than one row for each entry in the connected field name. I need the value only from the first row of the second table for each entry. Number 1 in the column titled criterion in the second table corresponds to the first row which I want to keep. Consider the following example:

First table
name value 
100  2
101  0
107  20

Second table
name test criterion
100  9    1
100  7    2
101  6    1

The two tables are connected using "name". I need to see the following in my report:

Report
name vale test
100  2    9
101  0    6

So, the second row of the second table is skipped since it has a duplicate name entry- i.e., 100. Can anyone please guide me how it can be done?

Upvotes: 1

Views: 72

Answers (1)

Nic3500
Nic3500

Reputation: 8621

Assuming these tables:

CREATE TABLE `first_table` (
  `idfirst_table` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`idfirst_table`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

and

CREATE TABLE `second_table` (
  `idsecond_table` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  `test` int(11) NOT NULL,
  `criterion` int(11) NOT NULL,
  PRIMARY KEY (`idsecond_table`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Using the same data you provided as sample in your question, the query would be:

SELECT first_table.name, first_table.value, second_table.test
FROM first_table
JOIN second_table ON first_table.name = second_table.name
WHERE second_table.criterion = 1

And the result is: enter image description here

This sample was tested on MySQL, but any database can be used for this, as it is pretty basic. The CREATE TABLE statements might vary, but the query should be fine on other DB.

Upvotes: 1

Related Questions