By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,652 Members | 1,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,652 IT Pros & Developers. It's quick & easy.

Append query without duplicate rows; restrict in query

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [EI&Fees] ( FacilityID, [Permit#], EmissionInvYear, FeeYear )
  2. 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
  3. FROM [Records to Append to EI&Fees, Active-Pending Query];
Jun 15 '15 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 10K+
P: 12,430
Please use code tags when posting code or formatted data.

Put DISTINCT after the select.
Jun 15 '15 #2

P: 5
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!
Jun 18 '15 #3

Expert Mod 10K+
P: 12,430
Use the WHERE clause to filter out the blanks.
Jun 18 '15 #4

P: 5
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!
Jun 18 '15 #5

Expert Mod 10K+
P: 12,430
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.
Jun 19 '15 #6

P: 5
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?
Attached Files
File Type: pdf EI&Fees.pdf (108.0 KB, 132 views)
Jun 19 '15 #7

Expert Mod 10K+
P: 12,430
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.
Jun 19 '15 #8

P: 5
I'll check all this on Monday--thanks!
Jun 19 '15 #9

Post your reply

Sign in to post your reply or Sign up for a free account.