Access SQL Question

[mscurto]

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

 

[Christian Hoffmann]

Am 16.02.2007, (e-mail address removed) vermutete :

What is the syntax for an sql command to get the following.

I want to pull in a handful of fields from a table but one of the
fields needs to be unique.

For example, if I have a customer table and I want to get each unique
customer number but I also want to get the customer's name, address,
email address, etc. What is the syntax?

I am trying to use DISTINCTROW but to no avail. If I use DISTINCT,
how do I pull in the other fields?

TIA.

Hi TIA

Do you have only one table for all these informations?
If you have, you don't need a Distinctrow, only use: select
tablename.fieldname1, tablename.fieldname2 ...from tablename where
yourfilter ;

br
Chris

 

[Daniel]

Hi,

If I understand you correctly, then you want to pull up one or more
records based on some criteria (which goes after the WHERE below).

If all of the information is in the same table, Customers, then this
would choose a single record based on customer number:

SELECT CT.custNo, CT.firstName, CT.lastName, CT.address FROM Customers
AS CT WHERE CT.custNo = 12345

You might also try experimenting with the query design view tool,
which seems reasonably intuitive.

HTH,
Daniel

 

[mscurto]

Hi,

If I understand you correctly, then you want to pull up one or more
records based on some criteria (which goes after the WHERE below).

If all of the information is in the same table, Customers, then this
would choose a single record based on customer number:

SELECT CT.custNo, CT.firstName, CT.lastName, CT.address FROM Customers
AS CT WHERE CT.custNo = 12345

You might also try experimenting with the query design view tool,
which seems reasonably intuitive.

HTH,
Daniel







- Show quoted text -

Hi,

To clarify (hopefully), I want to list a few fields from one table
based on one of the fields being unique.

For example, if I have an 'Orders' table and each customer has
multiple orders, I want to pull in the Customer only once. So if the
Orders table has fields (Order No., Order Date, Order Type, Customer
Name, Customer Number etc.) what would I use for the SQL syntax so
that I display all these fields but only based on each unique Customer
Number).

Thanks for your responses.