Unique Records

[magmike]

In my table, there may be multiple listings for one company, however,
each company only has one EIN.

How can I get my query to just report 1 record per EIN and show other
fields? I thought this would be easy to figure out, but I can't get
this one!

Thanks in advance!

magmike

 

[Arvin Meyer [MVP]]

You can use SQL with the DISTINCT keyword. You can also use an Aggregate
(Totals) query which will Group By unique values. The Sigma button in the
query design screen will add aggregate attributes to your query.

 

[John W. Vinson/MVP]

In my table, there may be multiple listings for one company, however,
each company only has one EIN.

How can I get my query to just report 1 record per EIN and show other
fields? I thought this would be easy to figure out, but I can't get
this one!

Ok, let's think this through.

You have 11 records for an EIN. Each record has various other fields.

Which record's fields do you want to see? How can Access tell? Do you
want to see all 11, an arbitrary record, a specific record, or what?

Perhaps you could post some more information about the structure and
relationships of your tables.

 

[magmike]

Ok, let's think this through.

You have 11 records for an EIN. Each record has various other fields.

Which record's fields do you want to see? How can Access tell? Do you
want to see all 11, an arbitrary record, a specific record, or what?

Perhaps you could post some more information about the structure and
relationships of your tables.

Well, if this was something I designed from scratch, I would have a
table for the company, then seperate tables for the other data.
However, this is data that was provided to me in Comma Delimmited
format, where there are multiple lines per employer. There are 159
fields of data in each record. I don't need to see all of it, but a
lot of it. But I don't need to see every record.

I tried using the Unique properties of the query, but that didn't
accomplish it. I tried creating a query with just the EIN so that I
could isolate one result per EIN, but then I can't add any other data,
or I start seeing the other records.

I was hoping to make this easy, but now wondering if I am going to
have to split up the table some how and relate them?

 

[magmike]

Ok, let's think this through.

You have 11 records for an EIN. Each record has various other fields.

Which record's fields do you want to see? How can Access tell? Do you
want to see all 11, an arbitrary record, a specific record, or what?

Perhaps you could post some more information about the structure and
relationships of your tables.

In answer to your final question, each listing is differentiated by
5500 filing year. However, there may be multiple records per year for
different plan filings. For example, they may have only filed one 5500
in 2006, but the Schedule C may have mulitple line items - one for the
health plan, one for the dental plan and another for their life
insurance. So then my data, would have three records for 2006 for that
EIN.

If I specify, however, only show me EIN's in 2006 for their health
listing - there may be an employer who only has dental listed, and no
health, but then I wont see him listed, and I'd like to.

 

[John W. Vinson/MVP]

In answer to your final question, each listing is differentiated by
5500 filing year. However, there may be multiple records per year for
different plan filings. For example, they may have only filed one 5500
in 2006, but the Schedule C may have mulitple line items - one for the
health plan, one for the dental plan and another for their life
insurance. So then my data, would have three records for 2006 for that
EIN.

If I specify, however, only show me EIN's in 2006 for their health
listing - there may be an employer who only has dental listed, and no
health, but then I wont see him listed, and I'd like to.

Sounds like you'll need to create an Employer table, and run an append
query from this mess into it. Select only those fields that pertain
just to the employer as an entity - i.e. not the filing year! - and
use the UNIQUE VALUES property of the query to get just one record per
employer. You can then join this table (perhaps after some cleaning to
make sure it's one record per EIN) to the other table(s).

 

[magmike]

Sounds like you'll need to create an Employer table, and run an append
query from this mess into it. Select only those fields that pertain
just to the employer as an entity - i.e. not the filing year! - and
use the UNIQUE VALUES property of the query to get just one record per
employer. You can then join this table (perhaps after some cleaning to
make sure it's one record per EIN) to the other table(s).

I know how to do a table to list the top ten records, or the top 25%,
etc. Is there a way to list only the latest year of each EIN (once
I've done the table thing)?

 

[John W. Vinson/MVP]

I know how to do a table to list the top ten records, or the top 25%,
etc. Is there a way to list only the latest year of each EIN (once
I've done the table thing)?

Sure. A Subquery will do this nicely - use a criterion on the year of

=(SELECT Max([yearfield]) FROM transactions WHERE transactions.EIN =
Companies.EIN)

Adapt to your table/fieldnames of course.