Eliminating Keypunching Errors Through Redundant Data Entry?
Oct 15, 2007
I work with an Access database with about 20 tables that requires considerable manual data entry. Although it is time consuming, the bigger problem is the keypunching errors that inevitably occur. A colleague of mine said that some databases can be set up so that two (or more) individuals can enter the same data into the database (basically creating two separate, temporary databases). At the conclusion of this double data entry, the two databases can be compared to see where they differ so that corrections can be made...the logic being that it's extremely unlikely two people entering the same data independently will make the same keypunching error in the same field.
Just wondering if Access has this capability, or if a reasonable facsimile of this approach exists.
Thanks!
View Replies
ADVERTISEMENT
May 21, 2007
Dear All,
I have a table with several fields.
Concentrating only on few fields I'd like to sort out redundant information (unnecessary records).
(It is similar to using Ecxel: Data>Filter>Advanced filter>Unique records only)
So, I'd like to ask for your help how can I reduce my table to unique records.
thx
Csaba
View 5 Replies
View Related
Jan 2, 2007
Hello,
I am using the access program to create a mailer. The fields are really basic... Name and address....
Many of the fields are going to be identical. City, state, zip code for instance.
The way we had it set up on our first try was to create the data base with all of the fields empty and to input everything over and over. I imagine there has to be a way to set it up so you only have to enter "San Diego" or "CA" once and then simply do manual entry of the names and street address.
Any help getting me started would be well eppreciated.
Thanks
View 1 Replies
View Related
Sep 14, 2004
Each pay period, I must enter data for each employee that worked an extra shift. The table is simple:
Name
Period Starting
Period Ending
Number of Extra Shifts Worked
Is there a way to bulid a form that will keep the dates I type in the period starting and ending fields until I change them?
To clarify, I have 17 employees who worked extra shifts, and I don't want to have to type in the Period starting and ending dates for EVERY record. Thanks in advance for any help. I really appreciate it.
KellyJo
View 2 Replies
View Related
Jun 30, 2006
I wasn't sure where to post this, so here goes:
Very basically, I have two related tables that I'm working with.
TABLE #1:
tbl_MAIN
FIELDS:
REC_ID (Autonumber) Primary Key [PK]
EMP_NAME
EMP_ADDRESS
DEPT_ID (Foreigh Key [FK] from the tbl_DEPT table...)
ETC...
TABLE #2:
tbl_DEPARTMENTS
FIELDS:
REC_ID (Autonumber) Primary Key [PK]
DEPT_NAME(Text)
ECT...
Now, I'd like to create a form that can enter information on the employee, including the department that they're a part of. I'd like to avoid having duplicate departments in tbl_DEPARTMENTS, but I want new department entries to be created if an employee from a new department is entered into the form. I'd like employees from a previously existing department to get associated with the department entry already in tbl_DEPARTMENTS. Is this possible? What's the best way to do this? I've tried every way I can think of, but I can't get this to work.
Thanks for any advice you can give me.
- Corey
View 2 Replies
View Related
Jun 28, 2005
I have a field in a database that needs the data entered without any spaces and I'm trying to figure out how to set either the validation rules or the masks so that the system will either automatically delete any spaces or warn the user not to enter them.
My preference would be for them to automatically be deleted.
Any ideas, suggestions?
I can figure out how to make either all numbers or all letters, but the data is both so that doesn't work.
Thanks, in advance, for your help.
Charis
View 4 Replies
View Related
Jul 2, 2014
what I am trying to do with that table is get a sum of all assets, a sum of all Customers, and a sum of all Employees only counting each Comp ID once. Each company that we deal with carries multiple of our products. So as you can see I currently have it in the table where it shows the different data for each product over 2011, 2012, and 2013. Obviously for this I cannot just sum the entire column, as I would have the same company summed multiple times. The thing that is making this challenging for me is that I need it summed for each individual year (2011, 2012, 2013), with the end result being a total number of employees, assets, and customers.
I was thinking that to do this I would need a Sumif formula with several other formulas inside of it, but cannot figure out what exactly I would need to do. I know that if I took it into excel I would be able to do it, but we have over 7000 different customers, each having anywhere from 3-20 different products, and for each of the years 2011-2013... so the data is just too massive to try to convert and manipulate in excel.
The reason that I am hoping to get a count of their customers, assets, and employees is so that we could then take the info from individual customers and find out that particular customer's portion of the total assets, customers, or employees. I guess the mock table that I made doesn't keep the formatting when I add spaces, so imagine that each pair of l l is a column and match that with the columns above.
I also need another column that has the 2012 and 2013 results
l Comp ID l Year l Prod Fam l Prod Group l Prod Code l Assets l Customers l Employees l Assets for 2011 l Customers for 2011 l Employees for 2011 l
l 1101232 l 2011 l Family A l Group A l Prod A l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group A l Prod A l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group A l Prod A l 51000 l 39 l 25 l
l 1101232 l 2011 l Family A l Group C l Prod B l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group C l Prod B l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group C l Prod B l 51000 l 39 l 25 l
l 1101232 l 2011 l Family B l Group B l Prod C l 59000 l 33 l 28 l
[code]....
View 3 Replies
View Related
Oct 12, 2005
hi
I have a query I'm creating that is using a joined view. I'm joining two tables and whereas the first contains unique data to each field the second contans class information so for example while all users are unique many may be assigned to the same class. When i use the join it therefore dupliactes all the class information in the query.
how can i set up the query so that the class information is only output once and not duplicated along with a user every time ?
cheers
chris
View 3 Replies
View Related
Aug 22, 2005
Access 2002/2003. I have inherited an old Access 2000 database where one of the fields in the table had a Number data type with TEXT in it. I noticed the wrong data type and I change it to text. All appeared OK until I attached a combo box to this field. When clicking in or opening the combo box in any row in this table with the old text, I get an error message: "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits." This field has about 3,000 records in it, and it appears to be corrupted and a permanent numeric field, I have tried everything to fix it: Compact and Repair; Import, append, and update this field into another table/field and I cannot get rid of the error message. How do I get that Number field finally and completely converted into a TEXT field? Anyone got any ideas? Thank-You! :o
View 1 Replies
View Related
Sep 9, 2005
I have a table that needs to be refreshed with new data monthly. The table has extensive relationship links to other tables. The new data comes to me in a spreadsheet.
I am tying to import the data from the spreadsheet into this existing access table, but errors halt the import. The fields in the spreadsheet are the exact same as in the access table. I am trying to accomplish this task with the Get External Data, Import wizard.
The strange thing is I can import the excel data into a new table without any problems. I then override the old table with the newly created one. The problem with is I have to severe all the relationships before doing so which is a real pain and a cumbersome process
If you can offer any advice, it would really help out
View 10 Replies
View Related
Jul 8, 2004
Picking up on some previous conversations with people, I have a form (Interconnect Register) within a database that I am trying to add a "cancel" button to which would close the form without saving. I can do this now, but the problem is there is a subform (Sheetsubfrom) within the main form. I have struggled to get the subform data to clear (was trying to use the DIRTY property but it wouldn't work - believe me, spent ages on it!) but have now got the following in place which I thought would work.
Private Sub Command41_Click()
Dim retval As Integer
'Check main form
On Error GoTo Sub_Form:
Forms![Interconnect Register].SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
Sub_Form:
On Error GoTo Error_Handle:
Forms![Interconnect Register]![SheetSubform].SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.Close
Error_Handle:
DoCmd.Close
End Sub
The problem has come full circle, in that when nothing has been changed ie nothing to cancel, an error occurs saying "nothing to undo". I believe this is something to do with having an error handling procedure nested within another error handling procedure, as from what I can make out from the help files Access backtracks to the level above. So when I have an error in the Sub_Form part, the whole thing crashes.
Any ideas guys and gals? I could try posting then DB on here if that would help, providing I can zip it up ok
Thanks
Ian
View 11 Replies
View Related
Jun 15, 2004
I have come across this problem several times. I have sub reports on a report and calculations are done using the data in the sub report. In many instances the subreport is empty. The calculated fields read #error. (otherwise the report runs well and records with data calculate fine) I would like to hide these errors and associated labels when the subreport is empty. Everything I have tried either does nothing (error still appears in text box - but runs ok otherwise) or I get a runtime error since it is trying to access "nothing" in the sub report.
How can I catch and therefore react on this empty sub report. (or subform too)
Thanks
Lisa
View 13 Replies
View Related
May 1, 2014
I have been consistently getting an error almost every month when I try to load some data that has been input into access, then I have it linked to an excel sheet so that whenever I refresh, the access data imports into excel and updates my pivots, charts, etc.
When my data entry ppl enter data, sometime they forget to enter a code, or something, and when they forget to enter that, it creates some kind of error in the query. The query still runs in access, but shows something similar to " #ERROR#" in the field IF I ever do find it in the access query. The issue is that I cant filter to find that error. I literally have to scan and scroll through thousands of lines of data to try and find this error. When I try to refresh the data in excel, the following error message pops up;
"Data could not be retrieved from the database. Check the database server or contact your database administrator. Make Sure the external database is available, and then try the operation again."
In past months I can usually find the #ERROR# by scrolling through access and finding it. Some months I have EXTREME trouble finding the error. It can take hours out of my work day. Is there any way to more easily identify which line these errors are in rather than scrolling through thousands of lines of data? Is there a way to still export the data to excel with the errors still in them?
View 3 Replies
View Related
Sep 11, 2013
Custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.
Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.
The code I have at the moment is as follows:
'------------------------------------------------------------
' cmdApplyFilter_Click
'
'------------------------------------------------------------
Private Sub cmdApplyFilter_Click()
Me.Filter = "[Asset Group] = " & Me.cmbFilter1 & "" And "[Location] = " & Me.cmbFilter4 & ""
Me.FilterOn = True
End SubThe current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.
View 9 Replies
View Related
Jul 8, 2005
More on the lot materials management program.
I have a series of related table:
Builder
Subdivision
Lot Number
Lot Option
Lot Materials Details
The [Lot Materials Details] table is populated by appending data from the [House Type Details] table tree as follows:
Builder
House Type
Elevation
Options Per Elevation
House Type Materials Details
The filtered Option Table in both branches determine which [House Type Materials Details] records get appended to the [Lot Materials Details] table.
The question is:
Is there any advantage, other than simpler queries later on, to adding the primary key from the [House Type Materials Details] table to the [Lot Materials Details] table.
It’s redundant because the [Lot Options Table] contains the [Options Per Elevation] primary key.
Any thoughts?
View 2 Replies
View Related
Jun 13, 2007
Hi All
Can anyone help me with this problem?
I have an 11 million row table that lists relationships between pairs of people. There are several columns, but the relevant ones for my problem are
personA, personB, a-b_strength, b-a_strength.
because of the size of the table i'm having all sorts of problems, and i could actually cut out half of the rows, because for every row listing eg
dave, steve, 4, 5
there's another row listing the same information, but flipped round, ie
steve, dave, 5, 4
I'm sure there must be an efficient way to delete, using a query or otherwise, one of each of these pairs (it doesn't matter which). I have another column (id) that simply has a unique number for every row, ie 1,2,3->11 million, so i was thinking of something like
delete
from table t1
where exists
(select *
from table t2
where t1.personA = t2.personB
and t1.personB = t2.personA
and t1.id < t2.id)
So, this should delete every row that has a mirror version earlier in the list.(i think)
What i'm worried about though, is performance: is this going to make a new temporary table for the nested select for each new row in the outer statement? I already created a table that listed the count of friends for each unique person with a make table query, and that took 12 hours to complete. I don't mind another 12 hour query, but not a 144 hour query!
Any advice welcome - thanks! (Sorry for the longwinded post)
View 4 Replies
View Related
May 13, 2013
I'm doing up a database of hazardous chemicals, based on publicly available information from various agencies. Each chemical has a name and a number, and has different hazards associated to it. The hazards are grouped into classes, and for each class the hazard is further broken down into categories. Hence for an explosive hazard (the class) I could have categories 1, 2 and 3 based on severity. For each iteration of the hazard class and category (for example explosive hazard category 1), there is a non-unique hazard statement. Another complication is that there is a qualitative element in the classification system, and hence agencies from different countries could classify the same chemical differently. This should be captured into the database as well. Thus for every chemical, I will have to enter the Japanese classification, the EU classification, etc.
I've managed to set up what I think is a correct relationship, based on my understanding of databases. However when I generate the report, fields such as the chemical name and ID are repeated for each hazard. I would like to merge these redundant entries, so the chemical name, I.D., and any other repeated fields appear only once. In essence, I'm trying to make it such that there is only one entry for each chemical+I.D/country, with multiple hazard subentries, rather than having multiple, seemingly duplicate entries.
View 3 Replies
View Related
Feb 29, 2008
I am have trouble eliminating the $0.00 balances in a query. From what I can figure out is that even though the Balance appears as $0.00 in reality it could be anywhere from $0.0012 to $0.009 or something like that.
What I am trying to do re reconcile the Balance field. If the balance reads $0.00 I want it out of the query. Then I would like to find all the balances that appear to be $0.01 to a negative $0.01.
It seem that no matter what is use as critiera I still can't what I want.
Any ideas?
View 3 Replies
View Related
Jul 28, 2014
I'm working on a scheduling system for my fathers company that automates some redundant tasks. Most importantly the system manages the order of processes on different parts (ie, part "tubes" may be cut, then drilled, then welded, then shipped).
I'm trying to create a form that allows for the editing and viewing of all the parts and the processes for each part for a specific work Order. Ideally the information that applies to all the parts (Work order Number, due date etc) would appear at the top of the form with all of the processes to appear beneath (Separated into groups - all the parts to be welded shown together). My problem with using sub forms for this was that I need all the records to be shown without scrolling. Is there a way to dynamically change the size of each Subform to match the required size, or is there a better feature to use?
View 7 Replies
View Related
Mar 9, 2006
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
View 2 Replies
View Related
Mar 5, 2015
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
View 1 Replies
View Related
Feb 9, 2015
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
View 3 Replies
View Related
Dec 18, 2014
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
View 5 Replies
View Related
Sep 6, 2005
I have a query for clients who come to our office looking for services.
I only need to report 1 visit per client during the month, in other words if the client comes several times during the month, we only count 1.
When I run the query, it gives all the times the client came to the office, but I only need to show once.
Clients are being sorted by ClientID. What do I need to write in the Criteria field to filter clients from showing again ?
Thanks
View 5 Replies
View Related
Jan 3, 2007
I have many queries that all do the same thing. The problem is that they're connected to specific controls on a form, so I have to make a new query everytime I make a new form to perform the same task as the others. This would be eliminated if there was a way to use the criteria the same way you would in code, like Me!controlname. Is there anyway possible to make these queries more universal so that they can be used by many forms?
View 1 Replies
View Related
Jan 23, 2007
I have created a query which gives me a COUNT for a field type GROUP:
SELECT AllPairAssignmentsQ.COID, Count(AllPairAssignmentsQ.COID) AS CountOfCOID
FROM AllPairAssignmentsQ
GROUP BY AllPairAssignmentsQ.COID;
AllPairAssignmentsQ is based on a union query of 5 tables. The problem I am having is that I am getting some duplicates which I need to eliminate. Each listing contains several fields. However their are two fields in the query which can be used to determine that entries are duplicated. If the combination of MAINCOUNT and COID are identical in an entry then only one of the listings needs to be counted.
The following is an example of my problem:
MAINCOUNT COID
1201 HNVL
1202 HNVL
1203 HNVL
1203 HNVL
1203 COAC
1204 COAC
My existing query will return the following:
COID COUNT
HNVL 4
COAC 2
Since 1203 HNVL is duplicated I would like for the query to count only one entry so that the query returns the following:
COID COUNT
HNVL 3
COAC 2
Thanks for any help that can be provided in solving this problem
View 8 Replies
View Related
