how to select a limited number of duplicates

[Guest]

Hello,

basically I have a table of addresses with a field for a company name and a
field for an individual name. several records may have the same company.

I want to select only the first three records for records that have the same
company.

How can I go about this?

I would also like to have another field which adds a sequence number based
on how many duplicates are in the company field (i.e. If I had 4 records with
the company as "McDonalds", I would like to have another field which
basically numbers them from 1-4)

Thanks then!

 

[Guest]

Oli,

How do you want to order these records by Company, by individual name, or by
address, or some other field? A way to do it by individual name would be:

Select Company, [indName]
From yourTable as T
Where (Select Count(*) FROM yourTable WHERE [Company] = T.Company AND
[indName] <= T.[indName]) <4
Order by Company, [indName]

If you add a Sort_Order column, you could do something like the following to
populate it:

Update yourTable
Set Sort_Order = DCOUNT("ID", "yourTable",
"[Company] = '" & yourTable.Company &
"' AND [indName] <= '" & yourTable.[indName] & "'")

Watch the word wrap in this last line.

HTH
Dale

 

[BruceM]

Hello,

basically I have a table of addresses with a field for a company name and
a
field for an individual name. several records may have the same company.

I want to select only the first three records for records that have the
same
company.

How can I go about this?

I would also like to have another field which adds a sequence number based
on how many duplicates are in the company field (i.e. If I had 4 records
with
the company as "McDonalds", I would like to have another field which
basically numbers them from 1-4)

Thanks then!

You have described a spreadsheet. Storing data redundantly is not the best
way in a relational database such as Access. For one thing, if the company
name changes, you need to change the company name for several records rather
than one.
It sounds as if you are describing contacts for a company. In a handwritten
address book you would probably have a company name, followed by a list of
contacts related to that company. That's how to do it in Access, too. You
need a Company table, and a Contacts table (tblCompany and tblContact).

tblCompany
CompanyID (primary key, or PK)
CompanyName, etc.

tblContact
ContactID (PK)
CompanyID (foreign key, or FK)
FirstName
LastName
Phone
etc.

If CompanyID in tblCompany is autonumber, CompanyID in tblContact must by
Number (Long Integer); otherwise, both fields must be the same Data Type as
defined in table design view.

Click Tools > Relationships. Add both tables. Drag CompanyID from one
table to another. Click Enforce Referential Integrity. Close the
Relationship window.

Create a form (frmCompany) based on tblCompany, and another (fsubContact)
based on tblContact. Set the Default View of tblContact to Continuous. To
do that, open the table in design view, and click View > Properties. This
opens the Property Sheet. (To see the properties for a control such as a
text box, click the text box while the Property Sheet is open.) Back to the
form's Property Sheet, click the Format tab, and set the Default View.

With frmCompany open in design view, drag the fsubContact icon onto
frmCompany. Click the border of fsubContact, and again click View >
Properties if the Property Sheet isn't still open. Verify that you are
looking at the properties for the Subform/Subreport (at the top of the
Property Sheet). Click the Data tab, and verify that the Link Child and
Link Master Fields are set to CompanyID.

Now when you add a contact, the contact is associated with a particular
company.

Back to your original question, if you want to see the first four records,
you need to decide what counts as "first". The first one you entered? The
person with the highest rank? Once you have decided that, create a query
based on the form, and sort by that field. Open the query in design view,
and click View > Properties. Set the Top Values property to 4.

As for a sequence number, it is quite easy in a report, but rather more
complex in a query. In a report, set the control source of a text box to
=1, and set its Running Sum property to Over All (or maybe Over Group, if
you are grouping in the report). For a query, try a groups search. The
matter has been discussed extensively.

 

[Guest]

Hi, it doesnt matter which order they are in as long as they are numbered.

This looks like it should work, will try it and let you know... cheers

Oli,

How do you want to order these records by Company, by individual name, or by
address, or some other field? A way to do it by individual name would be:

Select Company, [indName]
From yourTable as T
Where (Select Count(*) FROM yourTable WHERE [Company] = T.Company AND
[indName] <= T.[indName]) <4
Order by Company, [indName]

If you add a Sort_Order column, you could do something like the following to
populate it:

Update yourTable
Set Sort_Order = DCOUNT("ID", "yourTable",
"[Company] = '" & yourTable.Company &
"' AND [indName] <= '" & yourTable.[indName] & "'")

Watch the word wrap in this last line.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hello,

basically I have a table of addresses with a field for a company name and a
field for an individual name. several records may have the same company.

I want to select only the first three records for records that have the same
company.

How can I go about this?

I would also like to have another field which adds a sequence number based
on how many duplicates are in the company field (i.e. If I had 4 records with
the company as "McDonalds", I would like to have another field which
basically numbers them from 1-4)

Thanks then!

 

[Guest]

Hi,

I appreciate what you are saying, but this is an external list provided to
me which isnt part of any of our (my work's) databases. I need to process
these address lists quickly and repeatedly and then send them back the list
in the same format. I dont have the option of building relational tables each
time i need to do it. cheers tho.

 

[Guest]

Hi again, it works as far as I can see.
fantastic, cheers.

Hi, it doesnt matter which order they are in as long as they are numbered.

This looks like it should work, will try it and let you know... cheers

Oli,

How do you want to order these records by Company, by individual name, or by
address, or some other field? A way to do it by individual name would be:

Select Company, [indName]
From yourTable as T
Where (Select Count(*) FROM yourTable WHERE [Company] = T.Company AND
[indName] <= T.[indName]) <4
Order by Company, [indName]

If you add a Sort_Order column, you could do something like the following to
populate it:

Update yourTable
Set Sort_Order = DCOUNT("ID", "yourTable",
"[Company] = '" & yourTable.Company &
"' AND [indName] <= '" & yourTable.[indName] & "'")

Watch the word wrap in this last line.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hello,

basically I have a table of addresses with a field for a company name and a
field for an individual name. several records may have the same company.

I want to select only the first three records for records that have the same
company.

How can I go about this?

I would also like to have another field which adds a sequence number based
on how many duplicates are in the company field (i.e. If I had 4 records with
the company as "McDonalds", I would like to have another field which
basically numbers them from 1-4)

Thanks then!

 

[BruceM]

That would have been good to know before answering.