DISTINCT Question

[bdavey]

We are trying to write a query that will give us just one instance of an order,however, the other fields associated with this order and not the same.

Ex. We have multiple sales orders and each of these sales orders has a different engineering order.

How do we show just one instance of the sales order when all of the other fields are different as well.

 

[ToeShot]

DoCmd.RunSQL "SELECT DISTINCTROW FROM {Table Name} WHERE SalesOrder = '{OrderNumber}' AND EngineerNumber = '{Engineer'sNumber}';

if you know the sales order number you can put that straight into this query as the ordernumber otherwise prompt for the order number same with the engineer number. If prompting then the query would look like this

Dim SalesNo As String
Dim EngNo As String

SalesNo = Input("Enter Sales Number",,"Sale number&quot
EngNo = Input("Enter the Engineers ID Number",,"Engineers ID&quot

DoCmd.RunSQL "SELECT DISTINCTROW FROM {Table Name} WHERE SalesOrder = '" & SalesNo & "'AND EngineerNumber = '" & EngNo & "';"

HTH

 

[tlbroadbent]

NOTE: RunSQL runs Action queries (Delete, Update, Insert, Alter, etc.) not select queries. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ToeShot]

Terry is right so you would need to create a table with the same structure as the on you are running the query on the your statement would look like this

DoCmd.RunSQL "INSERT INTO {new table} SELECT DISTINCTROW FROM {Table Name} WHERE SalesOrder = '" & SalesNo & "'AND EngineerNumber = '" & EngNo & "';"

Sorry for any inconvinence