Unique records

[Guest]

Hi

I have a table that shows material numbers, vendor numbers, e-mails ect.
Some of the material numbers are shown twice in the the table. This is
because a vendor can have 2 e-mail adr. For different reasons I need a query
to always return the first record it finds for each unique material number,
so I get a list of materials with only one vendor, one e-mail and so on. How
can this be done?

regards
Ticotion

 

[Guest]

Use the Querys property UniqueRecords.
Then this property is set to yes, there is only a set of uniqe displayed.
What happends is that all fields in the query is checked for duplicates.
If you want to limit this check to only one or a few fields, you should use
use the UniqueValues Property instead.

For more information in this subject look at:
http://office.microsoft.com/en-us/access/HA012328001033.aspx?pid=CH100728931033

Borris

 

[Guest]

Ticotion,

It sounds like your table structure should be relooked. You should have a
Vendors table which contains a VendorID, name, ... and a VendorEmail table
that contains VendorID, Email, EmailType, Priority or something like this.
Then you can write a query to give you a single phone number for each vendor,
something like:

SELECT tblVendors.VendorID, tblVendors.LastName,tblVendorEmail.Email
FROM tblVendors INNER JOIN tblVendorEmail
ON tblVendors.VendorID = tblVendorEmail.VendorID
WHERE tblVendorEmail.Priority = 1

Not sure exactly how your other table should be setup because I'm not sure
what the Material numbers are for. My guess is that it should contain the
Material numbers and a single VendorID.

Having said all that, you should be able to do something like:

SELECT MateriaNumber, VendorID, FIRST(Email) as EmailAdd
FROM yourTable
GROUP BY MaterialNumber, VendorID,

HTH
Dale

 

[Guest]

Hi

Thank you for your answers. As for changing the table structure this isn't
an option as the table struckture is set by our ERP system.

As for the unique proberty setting i'm not sure this solves my problem. As I
understand it this is just like using distinct in an SQL statement?!

I will try to give you an exsamble of what I want it to do.

Material No Material text Vendor PGr Telephone
AAK169Z015 SKRUE 3,5X6,5 208310 L27 4207104 *
AAK169Z015 SKRUE 3,5X6,5 208310 L27 4207523
AAK170Z015 mønt 3,5X6,5 208450 L27 4207523 *
AAK170Z015 mønt 3,5X6,5 208450 L27 4207523

as you can see I have the same material but with two different telephone
numbers. But I only want it to choose the first row of each unique material
No (marked by a star above). How should I do this?

regards
Ticotion

 

[Guest]

Hi again

Sorry I found the solution. I use the First function on the values I want to
make distinct. Thnak you for all your help.

Regards
Ticotion