Specifying Constraints For New Table In SELECT...INTO...

[P3Eddie]

Hello All!

Sorry if this is basic, but I'm having trouble finding a way to specify unique fields (like you would ADD CONSTRAINT in an ALTER TABLE statement) for a new table I'm creating with a SELECT / INTO query.

The table is a list containing, among other fields, names of children (fname and lname), their birthdays and ship addresses. I'd like to pull all the fields from the master table into a new table, retaining only one of any possible duplicate entries (children) in the process in the fname, lname and ShipAddress fields.

Put simply, I'm asking if in a statement like:

SELECT master.fname, master.lname, master.shipaddr1 INTO NewTable FROM master

Can constraints be added/specified for tblNewTable?

Any help is greatly appreciated!

[ADezii]

Hello All!

Sorry if this is basic, but I'm having trouble finding a way to specify unique fields (like you would ADD CONSTRAINT in an ALTER TABLE statement) for a new table I'm creating with a SELECT / INTO query.

The table is a list containing, among other fields, names of children (fname and lname), their birthdays and ship addresses. I'd like to pull all the fields from the master table into a new table, retaining only one of any possible duplicate entries (children) in the process in the fname, lname and ShipAddress fields.

Put simply, I'm asking if in a statement like:

SELECT master.fname, master.lname, master.shipaddr1 INTO NewTable FROM master

Can constraints be added/specified for tblNewTable?

Any help is greatly appreciated!

You can use the UniqueValues property when you want to omit records that contain duplicate data in the fields displayed in Datasheet view. For example, if a query's output includes more than one field, the combination of values from all fields must be unique for a given record to be included in the results. The DISTINCT Predicate corresponds with the Unique Values Property as in:

SELECT DISTINCT master.fname, master.lname, master.shipaddr1 INTO NewTable FROM master