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
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!
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
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
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?
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)
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.
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
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?
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?
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
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 RelatedI 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
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
Hello,
I am building a query that pulls from one databases but I am running into duplicates.
Here is the skinny:
From the [tbl_databases], I am setting criteria that restricts a column containing “Visio”, more specifically, “Not Like “*Visio*”. I am also setting criteria that restricts a column containing “OLD” (it is a Yes/No field).
.
The problem is that there are duplicate rows that contain both “Visio” and “Old. So, simply suing my criteria restriction are pulling out the Visio and Visio/Old orders as well as Old and Old/Visio order.
Can I create a subquery that contain the combination of “Visio” and “Old” to restrict the subset from being extracted twice?
I think I have the logic but not sure not to set it up.
Thanks
Tuktuk
OK, first time posting so I'll try to be clear here. I work for a special education agency and have created a database to track student/therapist information. Now, I have reports showing which kids are in which schools and who their therapists are. What I can't figure out is how to print a report, grouped by school, that will display which therapists are active in that school.... any ideas?
View 1 Replies View Relatedwhat 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]....
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
I have produced a label printing query which eliminates duplicate addresses from a mail list. It works beautifully.
However, when I try to replicate with a different field criteria the new query fails to produce any output at all.
The SQL code I am using is as follows:
SELECT a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List]
FROM [Mail List] AS a
GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1]
HAVING (((a.[e-Mail List]) Is Null) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1]
From [Mail List] as b
Where b.[Address 1] = a.[Address 1]
Group By [Address 1])));
The working version has 'HAVING' e-Mail News =False instead of e-Mail List is Null. The former searches for an empty check box and the latter for an empty field.
I use several fields to capture a name: LName, FName, MI and Suffix.
In a report I combine the fields.
=[LName] & " " & [Suffix] & ", " & [FName] & " " & [MI]
This works fine if there is a suffix; however, without a suffix I have an extra space.
What is the "work around" to eliminate the space.
With a particular client, they want to display a subform in datasheet mode. They do not want an alternative like continuous forms. If I am displaying the subform in datasheet mode, is there any way to eliminate the header row which contains the names of the columns in the subform? I know how to remove the captions themselves but the header row is still there. I don't think it can be done but I wanted to check here with those more familiar with this.
View 6 Replies View RelatedI have a query based on 2 tables, joined on Memberid, the result showing :
Table1 Table1 Table1 Table2 Table 2 Table2
Category Association Memberid CustomerName E-mailAddress MemberID
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44
Board Member ADHTY 44 Smith smith@abc.co.za 44
Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I am trying to get the records on start and end date, still showing error no records found.
My code is like this:
Private Sub Command90_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strReport As String
Const strcJetDate = "#dd/MM/yyyy#"
[code]...