Best Way To Produce Unique Records
Jul 25, 2005
Hello, hoping for some help on the following scenario:
I need to produce a query which results in one record for each part number.
In a sample table of purchasing history (tblPODetails):
Fields:
PartNumber, Description, Cost, DatePurchased
We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.
I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.
The resulting description field would of course be the one in use for the latest received date.
I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...
View Replies
ADVERTISEMENT
Feb 18, 2013
I have a database where we regulary import excel data to generated from a form sent to our clients. The excel data that we import normally holds around 10 to 40 records at a time.
The current process I use to do this is to run a macro that creates a new table (tblImportForm), and then run an append query to append those records to our existing main table (tblJobDetails). I should also mention that the macro first deletes the existing tblImportForm before creating a new one with the same name with the new data.I have used this method rather than appending the data straight into the existing tblJobDetails as I found I came into more problems with generating IDs etc.
What I want to do is have a report come up after the data has been imported/appended, that lists the new data imported with the new record IDs generated in the main table tblJobDetails. It would be easy to do this is if I only needed the data or could use the IDs in the first table I import to, but the idea of the report is to give the user the new IDs (PrimeKeys) from the second table that the data is appended to.I could maybe do a count of records in tblImportForm and then produce a report using a query from tblJobDetails that pulls that number (the count) of data from the last record backwards? But I don't know exactly how to do this.
View 14 Replies
View Related
Sep 2, 2004
I have sort of a complex unique fields question. And I know there are probably quite a few ways to get at the answer, but I'm a novice so the simplest way would be best. I have a table (with over 25M rows, otherwise I'd be doing this in excel) with many columns. One of the columns is employee ID, and another is a date. Each employee ID however can have multiple rows with different dates. As an end product I need to have one row for each employee ID with the most recent date. An idea I had would be to sort by employee ID and then by the date. Then insert a column of if statements that asks if a record has the same employee ID as the record below it, but I don't have any experience with if statements in access (and VERY little SQL experience). But there is probably an easier way. Any help would be appreciated. Thanks!
View 1 Replies
View Related
Sep 8, 2006
I have a database that contains sales order information. A sales order could have multiple records (if that sales order has several different items ordered) or just one record. There is a status column in each record that shows a "C" (for closed). For items that were not delivered in a particular sales order, the status column is left blank.
Here is an example:
Sales Order Item Ordered Status
908111 Coaxial Cable C
908111 Transreceiver C
908111 Connector
908112 Coaxial Cable C
908112 Transreceiver C
908112 Connector C
The above example shows that only 2 items from sales order # 908111 were delivered or "C" but one of them is not. For sales order # 908112, all items were delivered or "C". I want to take all records of sales order # 908111 and show it on a table of partially delivered and all records of sales order # 908112 in delivered table.
I've tried different filtering options and group by but it doesn't seem to be working. Anyone has any ideas?
View 1 Replies
View Related
Apr 12, 2006
Firstly, my thanks to everyone who is going to be able to help with my problem, and also my apologies if this thread isn't in the right forum.
Ok, I am trying to construct a database at the moment for a small business. The database holds a number of customer records, and all of the basic design and construction I am skilled enough to write myself.
However, I'm running into major problems whilst trying to tackle a certain task...
The Required Situation
I have a form called Contacts. This form shows all of the customer details, laid out neatly, and draws its information from a table of the same name. This aspect works fine.
Now what I've been asked to look into is the possibility of adding two buttons to this form. Each button, when clicked, will open a pop-up window where extra details, such as a log of received telephone calls, can be entered and searched if necessary. the two buttons should do the same thing where the code is concerned, but store their details separately (one button is for sales calls logged, the other for support calls).
Now it is essential that these records are tied - uniquely - to whichever record the database user was on when they clicked the button.
My Current Thinking
I'm stumped. Well, half stumped. I think that the solution lies in sub-forms, but I'm not nearly skilled enough to actually impliment them (and don't want to waste my time doing so if there's an easier way).
Currently I have two tables, contacts and calls, the former to store the customer information, the latter to store the calls that are entered on this new form.
The form contacts has a button on it which opens the form calls. Now I can go to any record I like, click the button and open the calls form. I can enter details for such, and the record gets saved to my calls table. The problem is that it is not unique. No matter which record I'm on when I open the calls form, the details are always the same.
Yes, I know the absoute easiest way is to simply have a few fields on the main contacts form to log these details, and that it will uniquely tie to that record, but unfortunately that is not a practical solution. These calls may run into the hundreds, and must be easily navigated by the database users.
If I've not been clear enough, I can attach a copy of my database for you guys to take a look at, but what I'm after in the first instance is (a) whether there is an easier way to solve my problem, and, (b) if not, whether I'm right about needing subforms.
View 14 Replies
View Related
Jun 23, 2006
Hello
New to Access, new to this forum
I hope this is the right place for this question?
I have a table of data, with the following headings:
"group1","group2","groupage","Reference","name","team","pres","CB","CBPR","LA","LA PR","IS","ISPR","N","NPR","Origin"
I am trying to get a count of unique "References" (there are normally over 20,000) for each "group1" or "group2" by "groupage"
I can do this using Excel........but it is a very long winded process getting the data ready to use a pivot table.
Can anyone help me? Or do I need to provide more information?
Any help will be greatfully received. :)
View 10 Replies
View Related
Mar 23, 2006
Feeling stupid today...how can I query a table to find the last five unique records?
Table1:
PrimaryKey
Field1
Field1 may contain duplicates. I need the five latest entered unique records in Field1, and am struggling to write a query for this.
Many thanks to anyone who can help out.
[Still using Access 97]
View 1 Replies
View Related
Sep 15, 2006
Hello, I am new to Access and trying to run a query that will count only unique invoice numbers in my table. I am sure this is an easy command, and thank you for your help!
View 3 Replies
View Related
Mar 14, 2006
:confused:
I have been working on a normalized db for the past few months and it works wonderfully. But now I need to find a way to create a form so that the rest of the company can update their data.
My Table has the 5 following fields. Also listed is an example of the types of data I have there
StoreID_Catagory_Customer Year_Month_MixPercentage
IN00001_Japanese_____2005_____Jul_______.01______
IN00001_Japanese_____2005_____Aug______.02______
IN00001_Japanese_____2005_____Sep______.01______
IN00001_Chinese______2005_____Jul_______.35______
IN00001_Chinese______2005_____Aug______.25______
IN00001_Chinese______2005_____Sep______.26______
IN00001_Japanese_____2006_____Jul_______.01______
IN00001_Japanese_____2006_____Aug______.02______
IN00001_Japanese_____2006_____Sep______.01______
IN00001_Chinese______2006_____Jul_______.35______
IN00001_Chinese______2006_____Aug______.25______
IN00001_Chinese______2006_____Sep______.26______
As you can see I have multiple StoreID's for one store, IN00001. My db goes up to IN00419 so it ends up displaying 29,664 records. There are 4 different categories (Japanese, Chinese, Local, and Other). Until now everything has been taken from a datasheet and put into an Update Query but now that other people have to quickly access the system I need a slick interface.
What I need is a way to have a form that can update the table and add new entries that looks like this in the form:
IN00001____2005________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04
IN00001____2006________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04
I hope this gives you a clearer picture. I need to be able to update 96 entries at a time (12months, 4 customer types, 2 years) but do it in an efficient manner. The table only has 5 fields so I need to figure out how to display multiple entries on the same form. I also need a way to add new entries. In other words for me to add another StoreID such as IN00420 then I would have to enter in a value for every field for 96 records in the table. When in reality I should only need to update the mix percentages since the months and years remain constant. HELP!!
Any help would be much appreciated. Due to the variable nature of the db I cannot establish very many one-to-one relationships. So therein lies another problem.
View 3 Replies
View Related
Sep 10, 2003
Hi there:
I have a Question
In Access 2002 (XP) I have to write a query that displays unique Id's:
For Example:
CustomerID Company Name
1 ABC INC
1 ABC INC
2 XYZ INC
2 XYZ INC
3 PQR INC
The query should be 3 records.
But for some reason when I write the query in sql view it doesnot work.
Thanks,
Ashi
http://www.ringvirginia.com
View 12 Replies
View Related
Oct 24, 2012
I am trying to build a query using two tables. One is a table called sanctuary lakes cleaning data. The other one is called Faulire data. In cleaining table there are four years of cleaning records for each pit. I want to find the performance of these cleaned pits using failure table. Each failure date should have only one correspoding cleaning date. I wrote the following query. This query works. But it gives duplicate data. Some of the records will show two cleaning dates for a failure.
My query is like this.
SELECT DISTINCTROW [Sanctuary Lakes cleaning data].PIT, [Sanctuary Lakes cleaning data].[Cleaned Year], [Sanctuary Lakes cleaning data].[Cleaning Date], [Sanctuary Lakes cleaning data].[SR NO], [Failure data table].[Failure Year], Min([Failure data table].Failure_date) AS MinOfFailure_date, [Failure data table].[Failure SR no]
FROM [Failure data table] LEFT JOIN [Sanctuary Lakes cleaning data] ON [Failure data table].Pit = [Sanctuary Lakes cleaning data].PIT
GROUP BY [Sanctuary Lakes cleaning data].PIT, [Sanctuary Lakes cleaning data].[Cleaned Year], [Sanctuary Lakes cleaning data].[Cleaning Date], [Sanctuary Lakes cleaning data].[SR NO], [Failure data table].[Failure Year], [Failure data table].[Failure SR no]
HAVING (((Min([Failure data table].Failure_date))>Min([Sanctuary Lakes cleaning data]![Cleaning Date])))
ORDER BY [Sanctuary Lakes cleaning data].PIT;
My results are as follows:
PIT Cleaned Year Cleaning Date SR NO Failure Year Failure_date Failure SR no
1-08 2007/08 16-Oct-07 213458 2011/12 13-Aug-11 414984
1-08 2007/08 16-Oct-07 213458 2011/12 25-Jun-12 478589
1-08 2009/10 19-May-10 313497 2011/12 13-Aug-11 414984
1-08 2009/10 19-May-10 313497 2011/12 25-Jun-12 478589
1-08 2010/11 22-Feb-11 379081 2011/12 13-Aug-11 414984
1-08 2010/11 22-Feb-11 379081 2011/12 25-Jun-12 478589
1-08 2011/12 17-Apr-12 458430 2011/12 25-Jun-12 478589
I want the following results.
PIT Cleaned Year Cleaning Date SR NO Failure Year Failure_date Failure SR no
1-08 2010/11 22-Feb-11 379081 2011/12 13-Aug-11 414984
1-08 2011/12 17-Apr-12 458430 2011/12 25-Jun-12 478589
How can I modify my query?
View 2 Replies
View Related
Jan 10, 2006
Hi All,
Just a quick enquiry if I sort a query by a date field in ascending order then select unique records will I get the latest records.
Thank You
View 1 Replies
View Related
Jan 13, 2006
Here's my situation...
I'm making a query that uses information on company members from a database. The most important data field is the MemberID. Many members are listed in the table more than once because different records are put in when they have more than one contact person or health plan, etc.... However, right now I am trying to have the query make a table that produces only one record for each company so I can get a total number of companies. Therefore, I dont want the query to produce records with duplicates of the MemberID. My problem is that by setting Unique Values to 'Yes' doesn't work because if a different field in the record is different, it makes the record unique. I can't figure out a way to make it so only one field is considered for unique values. The funny thing is that it is simple to do this in Excel, you just gotta go to advanced filters. Does anybody know how to do this in Access?? Thanks.
View 14 Replies
View Related
Mar 14, 2006
Hi all,
I thought this would be relatively simple task to complete but its proving tricky.
I have two tables with the same data: E1 and E2
E1 has two columns and is as follows
A 1
B 2
C 3
D 4
E2 is similar except for one record '5'
A 1
B 2
C 5
D 4
Now the task is to show those records that do not match...in this instance
E1.Field1 E1.Field2 E2.Field2
C 3 5
Now I've used the find unmatched wizard and this is the SQL:
SELECT E1.Field1, E1.Field2, E2.Field2
FROM E1 LEFT JOIN E2
ON E1.Field2 = E2.Field2
WHERE (((E2.Field2) Is Null));
this is giving me partially the right answer as in
E1.Field1 E1.Field2 E2.Field2
C 3
Anyone else think of some other way?
View 2 Replies
View Related
May 12, 2006
Dear all,
Many thanks for taking the time to read this.
I have a slight problem which seems like it should be easy to solve but I have no idea how to do it.
I have 2 tables (table1 and table2 for instance) Table2 contains more data than table1 but should contain everything in table1 as well as more.
How is it possible to return just records that are in table2 that are not in table1
Eg
Table1 contains:
IDab
112
222
322
422
511
611
711
Table2 contains:
IDab
112
222
322
422
511
611
711
833
The query should return just the line:
833
as this is unique.
thanks again
View 6 Replies
View Related
Jun 1, 2006
I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.
SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;
qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.
(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)
Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.
Any help is greatly appreciated. Thanks.
View 4 Replies
View Related
Jun 8, 2006
Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.
What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.
I've attached a screenshot and here is the sql:
SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. *
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"
I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.
Thank you,
RCurtin
View 1 Replies
View Related
Aug 4, 2006
I will try to explain this in as detail as possible. I have One table having numerous columns.
Columns
RowNumber <primary key>
A
E
B
C
D
Now what I am trying to do is retrieve all records in this table where the combination of A and E is unique. e.g. if table looks like
RowAEBCD
111blahblahblah
221blahblahblah
322blahblahblah
421blahblahblah
532blahblahblah
633blahblahblah
When I Run a query I should get only the following records
RowAEBCD
111blahblahblah
322blahblahblah
532blahblahblah
633blahblahblah
I want all such records where combination of A and E is unique.
View 1 Replies
View Related
Mar 22, 2008
I am trying to set up a query to select PaymentDate, CustSurname, CustForename, AgentName showing only the last record for each customer if the last payment is over 7 days and also include customers with no payment.
the table structure is:
tblLoans
LoanID (PK)
CustID (FK)
AgentID(FK)
tblPayments
PaymentID (PK)
PaymentDate
LoanID (FK)
tblAgents
AgentID (PK)
AgentName
tblCustomers
CustID (PK)
CustSurname
Custforename
I have tried the code as below: but it now gives me every payment for the defaulters as opposed to just the last defaulted payment. Can you shed any light on what I have done wrong.
select b.PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from tblCustomers a, tblPayments b, tblAgents c, tblLoans d
where d.LoanID = b.LoanID and d.CustID = a.CustID and d.AgentID = c.AgentID
and (DATE()-(SELECT max(PaymentDate) from tblPayments f where f.LoanID=d.LoanID ))>7
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;
View 1 Replies
View Related
Jan 31, 2008
This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?
Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks
View 6 Replies
View Related
Apr 9, 2014
I got a large transaction file with deplicated records!! For unit price checking, I need to copy the last 10 unique records to another file with the same criteria but price is not the same as the current checking record.
For instance, I got 5000 records with around 400 records unit price is not the same as history. For each checking, I needed to copy the same criteria historical records to a tempory file and then delete corresponding duplicated records first, following by another deleting action only to keep the last 10 records according to date field.
View 2 Replies
View Related
Sep 30, 2013
In a report, I have a textbox to show the number of employees in the report.
I use this formula : =Count([last name])
Sometimes the same person has multiple entries, so the count is wrong.
How can I show only the number of different employees and ignore the duplicate names?
View 3 Replies
View Related
Mar 25, 2015
I have one table with 250 fields and hundreds of records. Each field represents a computer we have and the records in that field represent the type of applications we have on that computer. So the table looks like this:
Computer 1..........Computer 2.......... Computer 3........ Computer 250
Microsoft...............Google.................... .Paint.....................Microsoft
Windows Hotfix......Security Updates.......Java ......................Google
Java.....................Mediaplayer.............. Google.....................Java
As you can see, most computers have the same applications, but some have applications that others do not. I just need the list of applications we have from all computers. Meaning each app is listed only once.
I just need each application listed once. So I was trying to come up with a way to delete all the duplicates or use a Totals query to group the apps but since my table has 250 columns I'm not really sure how to go about it. What kind of query would be best to make this work?
View 3 Replies
View Related
Sep 20, 2005
Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..
Here are my fields..
I've assignment_num field, project_num field, project_name field, emplyee_name field.
here are my data..
assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally
What I want is to display the employee name with 2 projects or more...
What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..
pls help.
View 13 Replies
View Related
Sep 23, 2007
I have this basic question (obviously not so basic for me): how to pull out only unique records from two fields.
More details - two fields with names of competitors in a tournament (Winner or Loser) and i need to build a query to have all players names participating in the tournament regardless if they have won or lost in one list.
View 4 Replies
View Related
Oct 27, 2014
The main table for the database I am working on contains the following fields:
ID
Mfr Control Number
Initial or Follow-Up
Follow-up Number
Suspect
Date of Initial Email
Date Received
Date Submitted
Date of Report
Serious
Brief Description
Causality
Notes
With some additional qualifications I wanted to find records that had an intial report but no follow-up. Which translates too I want records that are unique in the Mfr Control Number field (no duplicates).
I am trying to build a query and keep getting hung up on the unique aspect of fields. I started by trying to query only "Mfr Control Number" fields that are unique (no duplicates). As best I can figure for some reason I can not add any additional fields to that query. My current query is set up in the query build table such that I have added "Mfr Control Number" in two columns. The first column in Total I have "Group By". In the next column I have set Total to "count" with a criteria of 1. If I try to add any other fields from my table than I seem to lose those unique results. But I need to further filter to get the exact information I need.
I want criteria on the "Initial or Follow-up" field to only bring "initial"
I want criteria on the "Serious" field to only bring "serious"
I want criteria on the "Date of This Report" field of "<Date()-"15""
Is there some way to take the results of that initial query to then build a another query based just on those records? I could then apply the further criteria and run my report. Or is there a way to do this in one step?
I've tried to make clear my intention but know it can be difficult to get this kind of stuff down in writing in a clear fashion. I have to be careful to keep information confidential also so some of the details are vague.
View 2 Replies
View Related
