GETTING DISTINCT ROWS

  • Thread starter bifteki via AccessMonster.com
  • Start date
B

bifteki via AccessMonster.com

I have two tables, a Companies table (tbl_Companies) and a Places table
(tbl_Places).
These two are connected through a relationship. Each company may have many
Places.
I want to make a query through which to keep only one line for each company,
regardless of which address it represents.
Does anyone know how to do this?
(Maybe something like DISTINCT? - however this didn't work out)
 
A

Allen Browne

1. Create a query using tbl_Places.

2. In query design, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. In the Total row under CompanyID, choose Group By.

4. In the Total row under the address the primary key, choose First. (Since
you don't care which address it is, the first one will be fine.)

5. Save the query with a name such as qryCompanyPlace. Close.

6. Create another query, using tbl_Companies and qryCompanyPlace as input
tables. Join on CompanyID. This gives you just one address per company.

7. Add tblPlaces to the query, joining tblPlaces.PlaceID to
qryCompanyPlace.FirstOfPlaceID. This gives you the details of that address.
 
B

bifteki via AccessMonster.com

Thank you for your answer Alan.
However, I can't find the Total button you mention.
Note that I'm using Access 2007.
I tried to find it in More Commands... to show it in the toolbar but I only
found a Totals command, which is inactive. On the tooltip text it says
"Show/Hide column totals in the query results". Maybe I can't use it because
I've created a view instead of a query? (I'm connected to a DB on SQL Server
2005).


Allen said:
1. Create a query using tbl_Places.

2. In query design, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.

3. In the Total row under CompanyID, choose Group By.

4. In the Total row under the address the primary key, choose First. (Since
you don't care which address it is, the first one will be fine.)

5. Save the query with a name such as qryCompanyPlace. Close.

6. Create another query, using tbl_Companies and qryCompanyPlace as input
tables. Join on CompanyID. This gives you just one address per company.

7. Add tblPlaces to the query, joining tblPlaces.PlaceID to
qryCompanyPlace.FirstOfPlaceID. This gives you the details of that address.
I have two tables, a Companies table (tbl_Companies) and a Places table
(tbl_Places).
Click to expand...
[quoted text clipped - 5 lines]
Does anyone know how to do this?
(Maybe something like DISTINCT? - however this didn't work out)
Click to expand...
Click to expand...
 
B

bifteki via AccessMonster.com

I really don't have these options in the ribbon.
I think it's because I'm connected to an SQL Server. I can't create a query,
I can either create an in-line function, a view or a stored procedure.
Is there some other way I can do this?
 
Top