Disallow duplicates in combination of fields

I have based a form on a query.  I don't want to allow duplicates of certain 
field combinations.  For example:

Qstnaire ID               PPtID                 ResponseNo               
Other fields
1                                6                           1               
         
1                                5                            2
1                                6                            3

I have created a field in the query that concatenates the QstnaireID and the 
PptID and am trying to add something in the criteria that won't allow a 
duplicate of this combination to be entered.  

I know I can use a Find Duplicates query after the fact, but wish to avoid 
duplicates in the first place, as they will be the result of incorrect data 
entry and should not appear at all.  The entire record won't be a duplicate, 
just the combination of the two fields noted above, so I can't use the unique 
values in the query properties.

I may be approaching this incorrectly.  Perhaps I should be modifying 
controls on my form...  

Any assistance will, as always, be greatly appreciated!
-- 
Thanks!

Dee
0
Utf
1/11/2008 3:34:47 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
685 Views

Similar Articles

[PageSpeed] 17

Create a unique index on the combination of the 2 fields.

1. Open the table in design view.

2. Open the Indexes box (Toolbar.)

3. On the first blank line of the Indexes box, enter a name for the index, 
and the first field.

4. In the lower pane if the dialog, set Unique to Yes.

5. On the next row of the dialog, leave the index name blank (indicating 
this row is part of the same index), and enter the 2nd field name.

It should now look something like this:
    Index Name        Field Name        Sorting
    =========       =========       =====
    QstnairePPt        Qstnaire ID        Ascending
                              PPtID               Ascending

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"dee" <dee@discussions.microsoft.com> wrote in message
news:357E64F0-1013-445A-B750-5D17DA09BCC5@microsoft.com...
>I have based a form on a query.  I don't want to allow duplicates of 
>certain
> field combinations.  For example:
>
> Qstnaire ID               PPtID                 ResponseNo
> Other fields
> 1                                6                           1
>
> 1                                5                            2
> 1                                6                            3
>
> I have created a field in the query that concatenates the QstnaireID and 
> the
> PptID and am trying to add something in the criteria that won't allow a
> duplicate of this combination to be entered.
>
> I know I can use a Find Duplicates query after the fact, but wish to avoid
> duplicates in the first place, as they will be the result of incorrect 
> data
> entry and should not appear at all.  The entire record won't be a 
> duplicate,
> just the combination of the two fields noted above, so I can't use the 
> unique
> values in the query properties.
>
> I may be approaching this incorrectly.  Perhaps I should be modifying
> controls on my form...
>
> Any assistance will, as always, be greatly appreciated!
> -- 
> Thanks!
>
> Dee 

0
Allen
1/11/2008 4:34:00 AM
You certainly work hard in these newsgroups!  Thanks so much - it worked 
perfectly!
-- 
Thanks!

Dee


"Allen Browne" wrote:

> Create a unique index on the combination of the 2 fields.
> 
> 1. Open the table in design view.
> 
> 2. Open the Indexes box (Toolbar.)
> 
> 3. On the first blank line of the Indexes box, enter a name for the index, 
> and the first field.
> 
> 4. In the lower pane if the dialog, set Unique to Yes.
> 
> 5. On the next row of the dialog, leave the index name blank (indicating 
> this row is part of the same index), and enter the 2nd field name.
> 
> It should now look something like this:
>     Index Name        Field Name        Sorting
>     =========       =========       =====
>     QstnairePPt        Qstnaire ID        Ascending
>                               PPtID               Ascending
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "dee" <dee@discussions.microsoft.com> wrote in message
> news:357E64F0-1013-445A-B750-5D17DA09BCC5@microsoft.com...
> >I have based a form on a query.  I don't want to allow duplicates of 
> >certain
> > field combinations.  For example:
> >
> > Qstnaire ID               PPtID                 ResponseNo
> > Other fields
> > 1                                6                           1
> >
> > 1                                5                            2
> > 1                                6                            3
> >
> > I have created a field in the query that concatenates the QstnaireID and 
> > the
> > PptID and am trying to add something in the criteria that won't allow a
> > duplicate of this combination to be entered.
> >
> > I know I can use a Find Duplicates query after the fact, but wish to avoid
> > duplicates in the first place, as they will be the result of incorrect 
> > data
> > entry and should not appear at all.  The entire record won't be a 
> > duplicate,
> > just the combination of the two fields noted above, so I can't use the 
> > unique
> > values in the query properties.
> >
> > I may be approaching this incorrectly.  Perhaps I should be modifying
> > controls on my form...
> >
> > Any assistance will, as always, be greatly appreciated!
> > -- 
> > Thanks!
> >
> > Dee 
> 
> 
0
Utf
1/11/2008 5:06:26 AM
Reply:

Similar Artilces:

New Email Message in Outlook 2007 starts in Body of email not in To: field
I have a number of users who have been upgraded to Outlook 2007 and now when click on New - Email the Email window opens with the cursor in the body of the email first. In previous versions the focus was always in the To: field. I can't find anywhere to change this. Anyone know? It should open in the to field fn new mail, in the body for replies.. Try Help, Office Diagnostics -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outloo...

Outlook 2003 does not export all fields to any of the selected file formats #2
I just came across the message below while trying to answer the same question. No completely satisfying answer was posted at the time. I can add a solution but it raises another problem. You can link to or import a message folder from Access. That brings in the date fields but you lose the From address. It shows the name but not the underlying address. For example, in the case below, the sender would be shown as "Great Eyes" but the actual hotmail address would not be shown. Does anyone have a solution for getting messages into some sort of workable format (flat file, Excel, A...

Replace a field with value from the next row
I have a table listed with columns Product Unit Of Measure Cost % Markup I have products number repeated twice one for unit of Measure "CS" and another for "EA". In some products the % Markup is stored only for "CS" and not for "EA". How do I copy the % Markup from "CS" and paste to "EA" using a formula. ...

Problem inserting fields into subreport...
I have a report that has a subreport in it. The subreport has two fields that store a value that tracks the status of map changes. We have digital maps and paper maps and need to track the status of a change as to whether or not it has been done on the paper maps and/or the digital maps. I want the report to show the verbose meaning of these values (stored in a separate table). Let me set this up a bit... Table: MapChange Field: ChangeID Field: Description Table: ChangeDetail Field: DetailID Field: ChangeID (links this detail to MapChange.ChangID - the MapChange can have ma...

Product Name Field 100 chrs
hey al The product Name field is limited to 100 characters, is it possible to increase that? ive tried deployment manager -> schema manager -> but the length is grayed out I could change the field size from sql enterprise manager but not sure if it'll bust crm. any suggestions will be appriciate Thanks If you are upto your limit then I think you will need to create a custom attribute in the Product entity. I would check to see if there is another attribute already created that you could use for this purpose first. would be nice is ms could increase the length of the name field ...

Age Field in Form but Not Query
Hello All: I am currently in the process of creating a database based on retiree information. I inputted all of my data into one main table and then created queries from this table. I then created a form based on each query. I needed Access to calculate the age of retirees so I followed the directions from Access which explain that a text box needs to be created in the form and then a formula is inserted in the ControlSource. This turned out fantastic and I got the results that I wanted. I realized that one of the reports that I need to create is based on people being below the age of 65...

Field on Form gives Error
I designed a form with record source a table containing among them the numeric (long integer) field ContactID. I added the field to the form using the field List (drag and drop). The field gives me the value #Name? when shown in the form. All other fields in the form are displayed correctly. I checked and double checked if the name of the field is not misspelled but as I used drag and drop from the field list, what can go wrong here? When I invoke the Query Builder and look at ContactID, it is shown normally and has normal values. The form is used as a sub form but does not have Linked C...

Number of dropdown fields in Excel is limited. I need more. How?
I need some thousand dropdown fields (via validation function) in Excel. But if I input too many fields I can't save the file. Seems the number of dropdown fields is limited. How can I enlarge the number of allowed dropdown fields? Can I change the setup anyway? How can a user view/use/comprehend anything more than a dozen or so dropdowns ?? If the second dropdown depends on the user's selection in the first dropdown, then see the advice offered here http://www.pcmag.com/article2/0,1759,1585456,00.asp "UweVahrson" wrote: > I need some thousand dropdown fields (...

Auto Date 4 changing fields in a line
Hi, Is there a way to update a field / column with the date I change a record in that row? I wish to have only specific cells linked to that, to include range of cells. Also prefer data type date for easy view/understanding/sorting: i.e. yymmdd. Thanks in advance. - Subsequently, anyone interested in Investment Spreadsheets / math please feel free to email me: nasgentech at yahoo; have / looking for math equations, resources/solutions. -Nastech J.E. McGimpsey shows how at: http://www.mcgimpsey.com/excel/timestamp.html And with a couple of minor modifications to check multiple...

How calculate percent difference between fields in same group in a
i have a report sectioned by year with number fields. I would like to calculate percent difference of the number fields for each year. If you just want to compare the value for one year with the value in the previous record, and you are comfortable with VBA code, you could do it like this: a) Declare a variable in the General Declarations section of the report's module (at the top, with the Option lines), e.g.: Dim varAmount As Variant b) Add some code to the Print event of the (Detail?) section to remember the value, e.g.: varAmount = Me.[Text22] c) Add some code to the F...

Command button to replace current record's field
I have a continuous form with a command button in detailed band. On click event, I want to replace 'checkin' field of current record with =now(). Click the command button the 2nd time, replace same field with blank. (toggle). Thanks. To assign the current time to the checkin field, use this code: Me.checkin = Now() (or use the SetValue action in a macro if you prefer.) It would be easier to use another button to blank the field. Code: Me.checkin = Null Otherwise you'll have to keep track of when the button was clicked first (module level variable in the f...

The offline address book does not download all fields, especially any custom
The offline address book does not download all fields, especially any custom fields that might have been setup. How can we define what fields are downloaded to the offline address book? Regards Jacques | The offline address book does not download all fields, especially any custom | fields that might have been setup. How can we define what fields are | downloaded to the offline address book? | | Regards | Jacques | Hi Jacques, The attributes within the OAB file are fixed and cannot be customised or changed. You can move data from your custom fields into non-used defined fields and al...

Sum field shows sum of all records, not just related records
My apologies if this answer is already out there ... I looked, but couldn't find what I needed: I have a form which is based on a query of tblCamperInformation and tblCurrentRegistration. I have three tabs, one for the tblCamperInformation, on for tblCurrentRegistration and a third which has a subform for tblPayments. tblPayments is comprised of payment information for each camper. I have primary keys in each table, CamperID, RegistrationID and PaymentID. What I would like is to have the balance of camp due to show on my tab of tblCurrentRegistration, however, when I create a field to...

In a report, how do I unlink a data field with its label?
I am trying to separately adjust the width of a data field and its associated label. I could do this in Access 2003 by clicking a number of times on the data field and it would allow me to widen the data field without changing the width of its label. How can I do this in Access 2007? When I open a form in design view, click on a textbox (with an attached label), then drag the handle in the middle of the box, the textbox control changes size, while the label control remains the same. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received p...

Change NVARCHAR fields
I would like to be able to change NVARCHAR fields ie Job Title anyone aware if you can change to a pick list? Regards Lisa Lisa I think you best bet is just create a new attribute and take the original one off the form -- Alistair 2B.net http://www.crmdeployment.co.uk "LisaNic" wrote: > I would like to be able to change NVARCHAR fields ie Job Title anyone aware > if you can change to a pick list? > > Regards > Lisa Thanks for the response I have gone down this route but then the more I became aware of 3.0 this is not ideal. As, when you use the mail mer...

Possible to link fields in a form to narrow selections?
I'm working on making a form more user friendly - What I'd like to do is to link multiple fields and to narrow selections in field2 and field3 based off of what a user selects in field1. Example: I have field1 Principal with 10 options. I have field2 PrimaryCategory with 20 options, however, there are only 2 valid options per Principal. I have field3 Brand with 50 options, however, there are only 2-5 valid options per Principal. Right now, all options for field2 and field3 show up in the combo box regardless of what is chosen in field1. What I'd like is once user...

Smartlist Duplicate Records
Hello, I am having a recurring problem with duplicate records returning on some of my SmartLists. I have tried building the SmartLists using just tables within Builder and have also tried creating SQL views instead to see if I could eliminate this problem but to no avail. Does anyone have any suggestions/solutions for this problem? I appreciate the help. Julia Hunt Florida Keys Aqueduct Authority Julia: Most likely your table joins aren't configured properly. Can you provide an example of one of your queries that is returning the duplicates? The tables, fields, and joins you...

combining tabs in one master tab
How can I combine two tabs in one master tab. I need to combine two columns? I'm assuming you mean you have a column on sheet1 and a column on sheet2 that you want to total on sheet3. If that's the case to add cell A2 from Sheet1 and A2 from Sheet2 enter the following formula on Sheet3: =Sheet1!A2+Sheet2!A2 Substituting proper sheet names, of course. Good luck! "TeachMeExcel" wrote: > How can I combine two tabs in one master tab. I need to combine two columns? ...

Users of Returns Management, Depot Management, Field Services
I would like to put together a list of users who are using these modules to use as a resource when questions arise concerning these modules. If you are interested, you can either post a reply here, or email me at djohnson@pshinc. com. We are a user that recently purchased the Returns Management and Depot Management modules. I believe we are our VAR's only customer who has these modules, and as a result, is not very familiar with the subtleties of these modules. I would hope to find another company in a similar business, (we are an indutrial repair firm) who is using these modules t...

field as number
I have a make table query. One of the fields has the following expression: “Total : IIf(Nz([Sum Defects Current Month].[SumOfDefects])=" ","0",([Sum Defects Current Month].[SumOfDefects]))”. I set up the field parameter in the query to a general number. However after making the table I get the field in the new table as text. Please advise on how to get the field in the table as number, not text. Thank you, Ra Use 0, not "0". You need to understand the difference numbers and text. "0" is a text expression while 0 is a numeric expression. I t...

Customize Linked Field in a Form
Is it possible to customize the links on a form to open different windows than default? For example, on PurchasingTransaction Entry, the link for VendorID goes to the Vendor Maintenance Card. Can it be made to go to the Vendor Inquiry window instead? Not using native functionality. I think you'd have to use Dexterity to create a new window. -- Charles Allen, MVP "Joel" wrote: > Is it possible to customize the links on a form to open different windows > than default? For example, on PurchasingTransaction Entry, the link for > VendorID goes to the Vendor Ma...

Too Many Fields Defined Error
I have a database containing ten queries with many calculations in each query. In each query I have a "total" field to total the calculations in each query. I then have a central query which I use as my source for the report. This central query includes the "total" fields from each of the ten queries. When I run the report, I get "Too Many Fields Defined". When I run my central query, everything works fine with no errors. I tried creating a new report based on all of the fields in my central query. My central query consists of 23 fields, 10 of whic...

What is DetailID field in TransactionEntry table?
Hi all, I am importing our old sales history into RMS and have a question on the DetailID field in the TransactionEntry table. In the sample database, most items are 0, some are 13, some 115. What does this field mean? Am I safe to import using 0 for all of my items? Thanks in advance! The DetailID is the SerialID of a voucher. To be thorough, once you've imported items into the Serial table (gift cards/vouchers that have already been sold) you can transfer the Serial ID into the Detail ID of the actual transaction entry of that Gift Certificate (voucher) purchase. Hope this helps...

HELP: Access table linked to Excel
The problem is as follows: I linked an Access table to a complex Excel spreadsheet (.xls). I also built a custom Access form to browse and modify the data in that table. Works fine, however the worksheet also has numerous calculated cells (with formulas), and these formulas have no effect while I am using the form. The only way to correctly update these cells is to close my Access application, open the spreadsheet in Excel and save it, which is a hardly acceptable process. Is there a way for those formulas to update the corresponding cells automatically, without having to close Access and o...

Datetime field in E-mail template
Hello, I have created an email template, in that template I added some fields from an Account. In the Account I have an datetime field, with only show date. When I create the email with the template, the datetime field shows "01-05-2007 00:00" How can I remove 00:00? Manuly is not an option, The e-mail is triggerd from an workflow. ...