Append query without duplicate rows; restrict in query

[prose01]

I was able to create a multiple field unique index for the EI&Fees table, restricting to unique values for FacilityId, EmissionInvYear, and FeeYear; however, can you show me how to apply the same restriction in the query? Here is the SQL for the query:

INSERT INTO [EI&Fees] ( FacilityID, [Permit#], EmissionInvYear, FeeYear )
SELECT [Records to Append to EI&Fees, Active-Pending Query].FacilityID, [Records to Append to EI&Fees, Active-Pending Query].[Permit #], [Records to Append to EI&Fees, Active-Pending Query].EmissionInvYear, [Records to Append to EI&Fees, Active-Pending Query].FeeYear
FROM [Records to Append to EI&Fees, Active-Pending Query];

[Rabbit]

Please use code tags when posting code or formatted data.

Put DISTINCT after the select.

[prose01]

This works; but I need to receive data for all fields in records added. If only one of the above four fields has unique data (compared to records already in the table being appended) then the record is appended, but the other three fields have blank values for the record(s) appended--I need to append the values for all four fields. Can you understand what I'm asking, and perhaps help? Thanks!

[Rabbit]

Use the WHERE clause to filter out the blanks.

[prose01]

Thanks for your QUICK response. I don't know how to create the WHERE clause. When I'm in design view and open the datasheet view of the SELECT DISTINCT query, I get 56 records having four columns none of which have blank data. But when I RUN this query it allows only one record to be appended (as appropriate)--that record has different data in the Permit# field but same data in FacilityID, EmissInvYear, and FeeYear fields compared with existing records in the table they were appended to. How can I get this query to show the values (not blanks) in the table they are appended to? Will a WHERE clause work? Can you give me an example? Sorry if I'm not clear here--and thanks for any help you can give!

[Rabbit]

I'm sorry. Can you try explaining again with sample data? I'm completely confused by your explanations.

You said there was blank data and you wanted to exclude them. But now you're saying there is no blank data. Also, you said there are 56 unique records, but only 1 gets appended. Which doesn't make sense, because if they're unique, that means they don't violate the index, and all of them should be appended.

[prose01]

Please see attached pdf file.

Thanks for trying to help, and sorry for confusion.
If can't get SQL select DISTINCT query to work, will add index to the table to which records will be appended (unless you have another suggestion).

Maybe I should try bracketing all four fields--not only Field2--in the first line of the query?

[Rabbit]

The values should be there assuming everything is spelled correctly and is syntactically correct. the DISTINCT clause doesn't blank out values. This is the first thing you should check and fix.

The second thing is that it seems like you're not (only) looking for uniqueness in the record set that you're going to insert. But also looking for uniqueness in the table you are inserting into. To accomplish that, you will need to outer join the query to the main table on the key fields and only return the ones where the key fields in the main table are null, i.e. that they don't exist in the main table.

[prose01]

I'll check all this on Monday--thanks!