I have have 2 feilds in my database, there can be the same value in one feild but not both.....for example say NAME and ADDRESS i can have the same NAME for more then one entery and i can have the same ADDRESS for more then one entery but i cant have the same NAME And the same ADDRESS....anyone know how to do this? :confused:
View RepliesI've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
Quick run of data that would be in this table:
Code:
1 ABC 11/21/2014
2 ABC 11/27/2014
3 ABC 12/07/2014
4 DEF 11/21/2014
5 DEF 11/27/2014
6 DEF 12/07/2014
7 ABC 11/27/2014
in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.
Is their a way to highlight a feild. I am using the duplicate function but after the record is duplicated their are certain feilds that have to be changed, how can i highlight them somehow. ANy ideas??? The feild is Price???
Thanks Very good forum
Pete
The situation I am in at the moment is that I have a database for a company who does work at different building sites. I have created a database so that it has the date the work was completed. With this company in particular the last possible dates of payment are 30 or 60 days after the work was completed, depending on the work. I would like to know how I would be able to create a calculated field that would display the first day that the payment is late.
I have a form and subform which have the total value of subscriptions made for the month on them.
The subform has the individual values, and the mainform has the total value for that month.
how can i then assign the value of a feild on the mainform, to a value in one of my tables?
whats the best way to go about it?
thanks
BB
Im having a problem with controling duplicate records in a table
i want to allow duplicates in all feilds individually, but not if 3 feilds are the same in more than one record. (ei. if the entries in a1,b1,c1 are equal to entries in a2,b2,c2; prevent the second record from being entered)
so far, i've writen a macro to identify duplicates in an existing table, but i dont know how to delete records, or access values being entered in a form
any help would be greatly appretiated, thanks
I have a table (tblStudent) containing a field named fldTeacher1. Looking at the properties of fldTeacher1 I see that it has a lookup:
Bound Column: 1
Column Count: 2
Column Widths: 0";1" (id number; teacher's Name)
I am trying to run a DoCmd.RunSQL command in code and My question is how do I write a sql statement in code that updates that field with a new value?
I have tried:
DoCmd.RunSQL ("UPDATE tblStudent SET fldTeacher1 = " & Val(txtID) & " WHERE fldID = " & gintStudentID & ";")
All it does is place the id # in the field and not display the teacher's name in fldTeacher1 after the command was run.
I have a feild in my table that I need to increase by 1 each year.
eg.
2004- needs to change to 2005- on 1/1/2005 and keep changeing each year after that.
I am sure this is simple but all my attempts at makeing a counter have failed thus far.
Hi,
I'm trying to create a table which needs some feilds to be automatically filled up when the control goes to it. Say my table has feilds, "size" and "result". In each row, the result cell should be filled up corresponding to the size value of that row. This needs to be looked up from an existing table.
Would appreciate any solutions!
I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!
View 2 Replies View RelatedHi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
I have a form based on a select query (query does calculations as well). The query pulls from 2 linked tables, and it's not allowing me to add new records. On the form properties, I have "allow additions, allow deletions, allow edits, and data entry" set to yes. I've tried adding code to go to a new record when the form opens and this doesn't work, either.
Any suggestions?
Thanks in advance.
I'm new to Access and am trying to work out the best way to achieve the following.
I need a way for users to be able to filter the data which is displayed by forms or output in reports. For example, suppose that a user wants a list of all customers in either of two specific towns with a specific date of birth.
Obviously this could be achieved using a query and setting the criteria of the towns column to be:
"Anytown 1" or "Anytown 2"
and setting the criteria of the Date of Birth column to be the specific date required, e.g.
"08/08/2006".
However, so far everything about my database has been very user friendly with forms for data entry and presentation, so I don't really want to force my users to start creating their own queries.
The alternative I'm thinking of is as follows. Provide a query to pull together the required information (from various tables) but without any specific criteria. Then create a form with a field for each of the columns in the query, and allow the user to type their critieria into these. Then, place If statements as the criteria of the query to check if there is anything typed into each box on the form and, where there is, set it as the critieria for the query, then run the query.
With the above in mind, I have two questions.
1) Is there a better, more "standard" way to achieve a system of allowing users to create customised filtered data without requiring them to write queries?
2) In some cases, e.g. like with my specification of a town above, it would be good if the user could select the town they want to filter for from a drop-down combo box, rather than typing it in and risking entering details incorrectly. However, this would obviously allow them to select only one town, and not two as was required in my example. Is there an equivalent to a combo box which would allow multiple selections?
Thanks in advance if anyone can help me - and please remember that I am new to Access so whilst I want the best possible solution, I need a plain English explanation!
Gary
Hi,
i'm not sure if this should be posted under queries or forms...
i have a series of combo boxes in a from based on a query.
each combo box needs to display options for characteristics of each section of a motor (ie. combo box for 'Corroded' = 'slightly', 'very', 'not at all', combo box for 'dents' = 'none', 'top', etc)
my table structure works like this: i have a parent defintion tbl that defines the list of combo boxes (i.e. 'corroded', 'dent', 'rub marks') and assigns then and ParentID number that links to the tbl:InfoObjectDefinitions that defines ALL the possible characteristics ('slightly', 'very', 'not at all','none', 'top') and assigns them an ObectID number.
So in a nutshell, i can pull the possible numeric options (OID) and text values('slightly') for 'corroded' from the table InfoObjectDefinitions according to 'corroded's PID.
I then store the OID in the column for 'corroded' in tbl:Repairs.
so i need to create a form that lets people add/edit entries in the repair tbl using the text values and not the id numbers.
i can create a query for the form, that joins the repairs table and the InfoObjectDefinitions table, and substitutes the numeric values from the repairs table with the text values in InfoObjectDefinitions but then the comboboxes arent editable???? (the combobox expands, and i can see all the text values but cant select a new value)
The SQL that created my query 'repairFormTest' is:
SELECT Repairs.FENumber, Repairs.Corroded, (select otext from InfoObjectDefinitions where pid = 17 and oid = Int(Corroded)) AS CorrodedTxt
FROM Repairs;
and the form places the field CorrodedTxt as the Control Source of the combo box with 'select otext,oid from infoobjectdefinitions where pid = 17' as the Row Source.
And as i mentioned, the box lokks right, and expands correctly but i can select another value.
Any help appreciated, i really stuck.....
Thanks,
Vauneen
I have a very simple database used by 3 users at a time. It is split into BE and FE. One user accesses the DB with queries only so I have created a DB with the master file linked - and literaly nothing else, She will then create and save all of her queries here. But - I want to keep her from accidently updating the file from here. I can't seem to find a simple solution to simply not allowing her to update. Any ideas? Thanks - Dika
View 3 Replies View RelatedHow can i have multiple people entering data into a database, also there is an excel file that imports the info every few min, if i have that open i cannot enter data into the database. theres going to about 4-5 users plus the excel file.
View 1 Replies View RelatedHi Folks,
I have a table called tblSchemeCodes with the following field names:
Field Name Data Type Field Size Required Indexed
strCompany Text 1 Yes No
strSchemeCode Text 3 Yes Yes(No Duplicates)
strSchemeName Text 100 Yes No
strOrigNo Text 9 Yes No
strCategory Text 9 Yes No
dtmDateAdded Date/Time No No
Here is an example of some of the scheme codes I have:
HG
HGA
HGB
HGD
HGF
HGG
HGJ
HGP etc etc
however when I try to add HGE, it won't allow it, even though it doesn't exist within the table, it keeps changing it from HGE to HE, and HE already exists, so a can't add messgage pops up, which is should, because duplicates are not permiited.
I've tried turning off all of the required aspects and changed the "Yes (no Duplicates)" to "No", and the problem persists!!
Has anyone any idea why access would behave in this way and how I might resolve it.
Any assistance would be appreciated
John
I have a database that allows multiple entrys of the same information. I also have a report based on a query that pulls out said data.
I just need my query to pull out every record regardless if it is indentical to a previous one. I found under queries > design > properties there are two options unique values and unique records. According to a source online, these are what control allowing duplicates through, but I have swapped them around with no luck. Currently they are both set to "no".
Is there some other setting I have to switch up in order to allow the duplicates to come through?
Thanks
I have 2 related tables. The form to enter new data into the tables is set up with a subform for the second table. I want to allow the user to query for a specific record, have it display in a form that looks exactly like the data entry form, and be able to update the tables from that. I first tried an exact copy of the data entry form, using a subform for the second table. It allows me to update the 2nd table from the query results displayed, but won't allow updates to the main form. So then I tried just doing one form with all the results displayed, no subform, but then it won't allow me to update ANYTHING.
What am I doing wrong? Any help is appreciated.
Thanks
Stacey
I have a main form with 2 sub forms built in. my propblem is that i can't enter data into either of the sub forms
pls help
I have a filter (via textbox) on a form that only allows 1 character at a time to be typed (!!?) ie: after typing in "a" it highlights it, which means the next letter I type in, replaces the "a".When I use the mouse to re-click after the letter entered, it does allow further characters.What am I missing here?The code for the filter:Private Sub txtClientSearch_Change() On Error GoTo ErrorHandler Forms!frmClient.Filter = "Name Like " & Chr$(34) & "*" & Forms!frmClient!txtClientSearch.Text & "*" & Chr$(34) Forms!frmClient.FilterOn = TrueExitProcedure: Exit Sub ErrorHandler: DisplayError "txtClientSearch_Change", Me.Name Resume ExitProcedureEnd SubMany thanks!..Is it anything to do with Focus?
View 3 Replies View RelatedThe first relates to the fact that in the crosstab is a field that is set to Yes/No. It appears as a textbox with a value (0 or 1) in it. I want to replace this with a tickbox but when run neither the textbox nor the tickbox will allow any changes. Is it possible to set this to be editable? I cannot see how this would relate back to the actual data table but if it is possible then this should become clearer.
The second issue relates to the fact that the crosstab obviously creates the number of columns in relationship to the maximum number of values of the relevant field. So, I have a field that can have values from 1 to 15 but in most cases the actual value runs no further than 9 and 10. So for those records where the maximum number is not being used I want the unused tickboxes NOT to appear. Again, is this possible.
In connection with the second, if I have (as I do) this group of 15, which is in fact made up of 3 groups ranging from 1-8, 1-4 and 1-3. So number 8 may not be required in the first group and numbers 3 and 4 in the second. I would prefer there not to be a strange gap between the datasets when run. Is it possible to set up a continuous form so that all the potential boxes are in place, that I run code on load that puts the values in from the query, hiding the end tickboxes and so collecting the groups together - then allowing the changes to be detected through code and re-assigned to the table?
I just created a form using the form wizard. I have added records from both tables and queries and now the form will not allow me to add any new records. Is this because I've used both tables & queries?
View 5 Replies View RelatedI'm currently working on a database that includes several tables with information about current and former employees. One of these tables has the training history for each employee, where the columns are the type of training (currently 37 types), and the data entered for each is either null or the date they completed the training.
I have a form bound to this table that allows the user to edit and add records, but additional types of training will surely be added in the future. I would like for the user to be able to click a button on the form that allows them to add a new training field to the form and underlying table. I understand that users should typically not be allowed to add new fields, but I can't think of a better way to account for the fact that new training will surely will be added in the future.
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)