How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date
View RepliesWhat expression do i use to count the number of patientID when there are duplicates (ie patient has come back for more than one visit but I just want to know the number of patients who visited (not how many times))
Count([patientID]) doesn't exclude duplicates
I have a table with the following fields:
Customer
Month
Qty
Unit Price
Extended Price
I want to know, by month, what the total extended price is for all customers. But some customers appear more than once in a month. So I also want to know the total extended price for the month, but I only want to include the extended price for each customer once, using only the record with the highest unit price (or extended price, qty for each customer is consistent), and ignoring additional records with the same customer in that month.
I am an excel user, and I could quickly do this with pivot tables... but I have too many records for Excel to handle this efficiently (it just keeps crashing!), so I thought I'd move to access... but I don't know how to use access! So I may need a little hand-holding here.
I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...
View 3 Replies View RelatedI'm trying to make a calculated field in query that will number duplicates, not count them.
For example, I have a table with the following:
Joe
Joe
Joe
Mary
Mary
Henry
Dave
Dave
Dave
Dave
I want the query to display:
Joe #1
Joe #2
Joe #3
Mary #1
Mary #2
Henry #1
Dave #1
Dave #2
Dave #3
Dave #4
I'm fairly new to to Access and I've been trying to get that to work for awhile now.
Hello,
I have 2 tables.
1) Table A, which is a table of all 100,000 users
2) Table B, which is a table of all other users
I have a query qryA, which looks for everyone in table A with a certain type of job title.
I want to exclude anyone who works for a company that occurs in the table B from showing up in the qryA query.
Any ideas?
Hi All,
Say you've got 2 tables and you want to extract data from table 1 but exclude all records from table 1 which have a field value existing in table 2?
Table 1 fields:
Test_Code
Test_Desc
Table 2 fields:
Test_Code
Therefore, I would like to query out from Table 1 all Test_Code values which do not exist in Table 2. Any ideas please?
Could someone please help me? I am trying to run a query in which I would like to exclude a particular person's name from the query. For example, I am entering productivity information for each employee and manager each day, however, I only want the employee information to appear in the reports each day. Is there anyway of excluding a name from a query?
Thanks:)
I have designed an invoice for a project that shows the amount of money needed to be paid, some of this has already been paid and some hasnt, how am i able to make it so that the values that have been paid and therefore have been checked are not include in the query. please help.
View 1 Replies View RelatedI have created a query showing locations of a company's facilities, and product groups associated with that facility. Unfortunately the the products groups are located in one column and, if the facility produces more than one product, the product group is divided by a |, as shown below:
CABLE TRAY SECTION [05CT]|STEEL CONDUIT AND ELECTRICAL METALLIC TUBING SECTION [05RN]
In my query, I only want to show the product code, which I resolved by creating another table and associating the code with the product group. However, this query will exclude those plants that produce multiple products. How can I show just the product codes for all the facilities, including those with multiple product groups?
In otherwords, I would like the above example to look like this: 05CT|05RN (or something similar to this).
I have a table with RandomID, EmpID, CompanyID. Some Companies will have great than 25 employees. I need to setup a query where I can randomly select all employees from all companies whos employee count is less than 25.
I have tried the following:
SELECT top 10 percent Count(*), empID
FROM tlbCompany
Group By empID
HAVING Count(*)<25
ORDER BY rnd(RandomID);
This counts the total number of employees, so if a company has 10 employees, it will only display one of the them.
Can this query be done?
And, what am I doing wrong?
Thanks to all that help....
Enviva..
Trying to build a CRM system for the office but am getting stuck with the below...
Each company (tblCompany) in the database has at least 1 enquiry (tblEnquiries) linked to it and normally at least 1 (sometimes 0) people (tblPeople).
I have a form which loads company specific information e.g. notes/quotes/orders/people/enquiries. All data is loaded based on the company unique id (c_id).
The attached image shows Company 1 (c_id = 1). It has 6 enquiries. However the subform only displays 5 of these. It does not display any which do not have a person (or p_id) linked to it - this is consistent throughout the database. I have included the table relationships and the enquiry as well in case they are needed!
Searching the net seems to suggest 2 possibilities:
1 - the relationship join type needs to be set to include all from tblEnquiries and only those from tblPeople where joined fields are equal, however changing the join properties does not appear to have an effect.
2 - table field types do not match (all _ID fields are set to number, unique numbers only).
[edit]: am using Access 2010!
I have re-designed the query to pull through tblEnquiries and tblPeople data based on the c_id field on the open form, which is now showing all records, including those not assigned to a person.
However, in the form I can select one from the query datasheet and open to see additional info. Those without a blank p_id number do not open, I receive the error:
"Run-time error '2113': The value you entered isn't valid for this field.
All _id fields are set to numeric so am not sure how to correct this?
Table1 gives Case Nu.
Table2 gives Case Nu. and EventType.
Table3 gives EventType and Cleared (yes/no field)
I want to run a query that gives Case Nu and Event where if there is even one event marked Cleared for a Case, then that case number and its events (even those not marked cleared) don't show up at all.
Any ideas? I know there must be an obvious solution, but my mind is stuck right now!
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
I am trying to limit the results of a SQL query by date. I would like to take the current date from the system clock, move back one day, check to make sure it's not a weekend or holiday, then use the result date to limit the results in my Query. If the current date minus one day happens to land on a weekend, the date picked should be the Friday before the weekend. If the current date minus one day lands on a holiday, it should be the date before the holiday as long as it is not a Saturday or Sunday (on another holiday) .
Here is my SQL query right now.
SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
ORDER BY (Mid(ClientDiv.Client_Division,1,3));
It limits what it selects by using the current Date minus one day.
I need to skip over Saturdays, Sundays and Holidays.
Hi 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.
Hello all,
I am sure this must be simple, but I can't get my head around it.
I have a query that looks at client bank account numbers, and finds duplicates.
There are a LOT of duplicates, because we have several accounts for the same person.
What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.
I am trying to find any records where the same account number is being used on more than one account.
All the fields are in a single table.
This is what I have so far, which just finds duplicate sort codes and account numbers;
SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;
Thank you!
Is it possible to make a calculated field in a table not allow duplicates?
There is obviously no option to select No Duplicates in the field settings?
In access database we register the following fields:
Company name - Address - Artickle - Number of copies
We then want access to print a report with these informations, except for the number of copies, which should be the number of duplicates that is to be printed.
I have a query which gets information from 2x tables where the I'd on one table is the reference number on the second table.I would like to know how I can remove the duplicates on my reference number field?
View 3 Replies View RelatedI have a client database that has recently had multiple duplicate entries. I need to reduce or negate this erroneous activity. I have a client table where I record amongst others, the following;
key
[christian_name]
[family_name]
[dob]
......
I believe that to prevent duplicate entrie via form I have created an additional field called "unique" given it as a unique index which I want to have populated with the joined fields first_name & last_name & dob (IE johndoe01/01/90), and then as user enters a new client it wont allow a duplicate.
However I need to fill all the existing customers (3600+) with the relevant joined existing data. If I create an expression I can cajoin the fields in a select query but when I try to make an update query the same syntax comes up with empty fields.
select query sql that worked to show field ...
SELECT divers.christian_name, divers.family_name, divers.dob, [christian_name] & [family_name] & [dob] AS Expr1
FROM divers;
update query that was empty ..
UPDATE divers SET divers.[unique] = [christian_name] & [family_name] & [dob];
I'm trying to create a combo box that takes its values from a field in a table, the thing is that this field has duplicates because:
* The table is a qualification look up table
* It has 3 fields: QualificationID (autonumber), Qualification(e.g. Bachelor), Programme (e.g. Science)
* E.g of duplicate values (this is not a problem as it is necessary) Bachelor of Science, Diploma of Science, Certificate of Science
What I'm trying to do is create 2 single-columned combo boxes that are a parameter for a query and it's working except the combo boxes show duplicated valuesIs there a way to get the query that is the row source for the combo box to eliminate duplicates?
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 table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:
SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));
which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.
Please help. I am having difficulty understanding why a subform is showing duplicates despite using SELECT DISTINCT for its query. I have looked all over the forum but am still quite confused.
I think my database is normalised but this may be the problem and can attach it if necessary.
My query is as follows
SELECT DISTINCT StudentAndCourse.ClassID, StudentAndCourse.LevelID, StudentAndCourse.StudentID
FROM [Level] INNER JOIN (Class INNER JOIN StudentAndCourse ON Class.ClassID=StudentAndCourse.ClassID) ON Level.LevelID=StudentAndCourse.LevelID;
Any help would be most appreciated as I cannot understand what is going wrong.
Hello,
I am just wondering whether there is any way I could run a simple query that would pull all the data I need and inform me of the duplicates if there is any.
Thanks!