Finding And Removing Records That Sum To Zero Based On Two Other Columns?

Apr 3, 2013

I have a dataset of about 70 columns but would like to remove any two rows that sum to zero (based on two columns) if the column "Units" are the same for the same "Bar code" thus to say "for the same 'Units' (for any two lines), check to see if their respective 'Bar Codes’ are the same and if so remove if their amounts sums to zero".

I have the following code which seems to be working but for relatively large data ( about 5k), it does not select all the data that meets the criteria:

Code:
SELECT *
FROM datatable
WHERE ((([datatable].[Unit] & [datatable].[Bar Code] & Abs([datatable].[Amount]))
In (SELECT datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
FROM datatable
GROUP BY datatable.Unit&datatable.[Bar Code]&abs(datatable.Amount)
HAVING count(*)>= 2 and sum(datatable.Amount)=0)))
ORDER BY [datatable].Unit & [datatable].[Bar Code] & Abs([datatable].Amount);

identifying the bug in this code that makes it not pull all the data meeting the criteria.

See sample data below:

# Unit bar Code Amount
1. AAB Mac1 2.75
2. AAB Mac1 -2.75
3. AAB Mac1 1.24
4. AAC Mac3 35.00
5. AAC Mac3 -20.00
6. AAC Mac3 20.00
7. AAD Mac3 16.11
8. AAC Mac2 11.00
9. AAC Mac2 -11.00
10 AAC Mac3 12.05
11 AAF Mac3 -12.05
12 AAD Mac3 -16.11
13 AAC Mac4 35.00

I want to select records # 1, 2, 5, 6,7,8,9 and 12 since they are same units having the same bar code and their sums total zero.

View Replies


ADVERTISEMENT

Finding Records Based On Text Inputs

Sep 13, 2005

Hello

I am trying to set up a user friendly front end to my database. I would like to have a form which allows the user to enter information into text boxes and then press a button which finds the relevant record(s).

I would like to use it to pin point an employee's record based on at least one piece of information, but possibly combined with up to four more by entering into these boxes. For example personnel number, surname, first name etc.

Is this possible and how do I go about setting it up?

I am aware of the filter function and queries but I can't seem to get either to operate in the way I have envisaged. They don't appear to offer the most user friendly solution, at least not in the format I have tried.


Apologies, but I am still getting to grips with access to be honest.

View 1 Replies View Related

Forms :: Finding Records Based On Todays Date

Aug 9, 2013

I'm wondering what would be the best way to update a list of records based on today's date. Right now I have it to open the form based on the LAST record and then navigate to the record they would like to update, I would only like to show what was entered that day.

View 1 Replies View Related

Finding Duplicate Data From Two Columns?

Jul 21, 2014

I am trying to find duplicate data from two columns and I want to query the all the duplicates. I tried the query wizard but it only finds duplicates in the column itself. I am trying to compare the columns together and see what is found in column A that is also found in column B and also Vice Versa.

View 3 Replies View Related

Removing Rows From A Query Based On Date?

May 11, 2015

I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:

DATE ID
01/01/2015 1
02/01/2015 1

What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.

I'm unsure what the easiest way to do this would be (or how to do it at all).

View 2 Replies View Related

Archive, Removing Records

Jul 5, 2005

hi

i have a form with various fields, on this form i have an archive button that places certain fields in an archive form. however this does not remove the information from the main form. pls see attached

any ideas, as some will know i'm a newbie

View 1 Replies View Related

Removing Outdated Records.

Sep 27, 2006

I have:

Database A with fax numbers:
Example:
- 1. John. W
- 2. Mark. Y
- 3. Sean .S

I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software)

I can export the error report and save as dbf.

Is there a way to make use of this dbf to update and remove Mark's record from Database A?

View 1 Replies View Related

Removing Outdated Records.

Sep 27, 2006

I have:

Database A with fax numbers:
Example:
- 1. John. W
- 2. Mark. Y
- 3. Sean .S

I exported to a dbf file to do some mass faxing and realised that Mark's fax number is no long in use. (error report by the fax software)

I can export the error report and save as dbf.

Is there a way to make use of this dbf to update and remove Mark's record from Database A?

View 1 Replies View Related

Removing Duplicate Records... Help Please

Jan 3, 2008

Ok well I thought I was doing this right but it appears I have not. I am drawing data from two tables. Both of them related by the person's name. One table I am pulling the unique ID number, the other table contains the name. I have a PO (Purchase Order) field being pulled from the table that holds unique ID. Or lack there of, so I am pulling all the records that contain no PO number. However duplicate unique ID's are being pulled. The way my query is set up now is:

Unique ID Number
Name
Count (Number of Procedures)
Charges
PO Number - Criteria: "Is Null"

I have also right click in the table view area, in the properties I selected Unique values. I have to assume that this would have held back any Dupes from the output. However this is not the case. When I looked in the help files the only thing I could find was:

http://office.microsoft.com/en-us/a...0840151033.aspx
Quote: "If you want to see only unique values in a result set, you can specify that you want to exclude duplicates from the result set.

In the Database window, click Queries under Objects, click the query you want to open, and then click Design on the database window toolbar.
Right-click the background of the Diagram pane, then choose Properties from the shortcut menu.
Select DISTINCT values.
The Query Designer inserts the keyword DISTINCT in front of the list of display columns in the SQL statement.

Note If you use the DISTINCT keyword in Microsoft SQL Server, you cannot modify the data in datasheet view."

The problem with this is my properties box does not offer a Distinct Values option. So my question is. Is there an expression I can place in the Unique ID field that will exclude any duplicate records.

Sorry for the book
Sean

View 3 Replies View Related

Queries :: Removing Duplicate Records

Jul 23, 2015

I'm having a problem removing duplicate records from a table. It should be easy but I can't suss it.This is an example of the data in my table:

Code:

RefDateStatus
113007111/06/2015Do Not Pay
114454306/07/2015Do Not Pay
115760714/05/2015Do Not Pay
116520705/05/2015Do Not Pay
117670108/05/2015Do Not Pay
118036218/05/2015Do Not Pay
118517015/05/2015Do Not Pay
178734020/07/2015Do Not Pay
182809915/07/2015Do Not Pay
184226010/07/2015Payment Due
184226022/07/2015Payment Query

As you can see, there are 11 records here but the last two records have the same reference number. I need my query to show the first 9 records + the record from the bottom two with the latest date (22/07/2015).

View 2 Replies View Related

Removing Duplicate Records With Macros

May 8, 2013

I currentlyt have a search form that searches criteria based on a textbox. After entering data a macro runs, and opens up a query with the results. After that query opens i have macro that runs a duplicate query off of the query initally opened based on the search results. What i would like to have is a macro that removes the duplicate records from the duplicate query and display the remaining records.

View 1 Replies View Related

Queries :: Removing Duplicate Records In Query

Apr 24, 2014

Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.

A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left

There are other tables but these are the 3 that are used in the query.

View 8 Replies View Related

Tables :: Removing Duplicate Records From One Table

Feb 18, 2014

I have a table with at least 13.000.000 records. There are many duplicates records... For example

ID Name Family mobile car number chassis Register_Year
1 Roy Jalbout 9999 123456/G ASF4546 2005
2 Roy Jalbout 9999 854658/G GRK554JFJD 2009
3 Tony Elishah 1234 854658/G GRK554JFJD 2012
4 Sam Markos 5478 854658/G GRK554JFJD 2014
5 Roy Jalbout 9999 123456/G ASF4546 2005

As you can see ID Number 2,3,4 have the same car but every one bought from another so it's not duplicates

The duplicates here is the ID's number 1,5. So how to remove the duplicates. I remember you i have at least 13.000.000 records. I try to make a query to find duplicates then i make a copy of the original table and than i should make a primary key then append the data from the original table to the copy table, but here i have more than one criteria

When I made a query to find duplicates the result was 680.000 records and every one have a minimum 2 duplicates an maximum 4 duplicates so it's about 2.500.000 duplicates records at least....

View 5 Replies View Related

Queries :: Finding Max Record Based On Primary Key In Query

Jul 25, 2015

How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.

View 2 Replies View Related

Queries :: Timesheet Database - Query Is Removing Records When Nothing To Sum

Jan 28, 2015

I'm creating a job/timesheet database and have a problem...

I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.

There are then various tables that support the main table, which provide actual employee names etc.

I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.

The way I was going about this was

- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.

- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.

Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.

View 4 Replies View Related

Listbox/Search Isnt Removing Still Displays Deleted Records

Jun 21, 2005

i have a form porblem. I have a search form embedded onto my main form its really a listbox that when i click the records in it they display the record.

prob;em is that when i delete the record it still shows in the listbox

View 1 Replies View Related

Finding First Records Only

Nov 1, 2006

lots of customers. lots of orders. i need to query for the first order of each customer only. thoughts?

View 1 Replies View Related

Queries :: SELECT DISTINCT Not Removing Duplicate Records In Staging Table

Jan 29, 2015

I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)

I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.

There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.

However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?

Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

[code]....

Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?

View 14 Replies View Related

Finding Duplicate Records

Jun 19, 2006

Hello

I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.

Thanks

Dave, England

View 4 Replies View Related

Finding Records 3 Months Old

Mar 12, 2008

Can't think how I would do this. If I create a query with a certain date then it will need to be changed every time the query is run. How would I write a query that automatically looks for a date 3 months or more old.

Thanks for any help. :D

View 2 Replies View Related

Finding Records By Primary Key Value

Oct 26, 2004

I have a table, and there are many records, each with a unique value in the OrderNumber field

I have a text box on a form, and i want it to work so that the user types in a OrderNumber value, and it loads that record into the form (the form is bound to the table)

I already know how to load a record by record number, but how can i do it by OrderNumber value?


thanks

View 2 Replies View Related

Finding Records Between Dates

Jan 4, 2005

Hello, i need to have my database find records that were entered between two dates. To do this i made a query that included the dates the records needed to be in between....[date1] and [date2]. Then i had the record dates be called [sent date]. i made my query and in the criteria for [sent date] i wrote
(Between [Date1] And [Date2])
when i enter the two dates [date1] and [date2] the query does not show the records that are between that date. why is this happening? is there and easier way? another way?
For example, say that i have records with their [sent dates] equaling 12/30/2004, 1/1/2005, 10/1/2005
i want to make it so that when the user inputs a records with dates [date1] and [date2], that those records will show up if their [sent dates] are between [date1] and [date2]. if [date1] is 1/1/2003 and [date2] is 1/1/2006, all of the records will show up.
if [date1] is 12/31/2004 and [date2] is 1/3/2005, then only the record with the date 1/1/2005 will show up.
how can i do this?

View 5 Replies View Related

Finding Records Within One Table - Msg Box

Feb 17, 2005

I have patients and their details all listed in onetable.
I want a message box to come up if a patient also has a family memeber somewhere else in this table. To do this i need to
match surname and mother's christian name.
Ani ideas?

View 1 Replies View Related

Queries :: Finding Non-Zero Records

Aug 7, 2015

I have a table in Access that looks like:

fiscal_year acct_period loc_rc account afe description amount system
2015 6 01001028 745003 100050652John A Doe -69.72 E
2015 6 01001028 745003 100086599Jane Doe -33.37 E
2015 6 01001028 745003 100086599Jane Doe -587.69 E
2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 1 33.37 L
2015 6 01001028 745003 994709 AP - EXPRPT050815SG - Jane Doe - 2 587.69 L
2015 6 01001028 745003 994709 AP - EXPRPT05
2115PK - John A Doe - 1 69.72 L
2015 6 01001028 745006 100048910KROGER -389.74 E

It's combining data from 2 system Identified by E or L. The data is similar but not exact & I'm comparing it using the description. What I need Access to do is find the record that doesn't "zero-out". In the example above the record it should pull is the last one "Kroger $389.74". How to do this but I do have a query/report that will subtotal based on the LOC-RC field.

View 4 Replies View Related

Finding Lower Case Records

Aug 15, 2007

I supply data to our customer for their system, a single table listing 1.9 millon records. 1 of the 7 fields is unique but the rest in the table can contain duplicated data.
My problem is caplial letters vs lower case.
How can I query my table to find just the mixed and lower case versions of for example "BIRMINGHAM" ie "birmingham" or "Birmingham". My customers system is rejecting the Data due to some lower case entries.

:confused:Any help appricated

Steve

View 4 Replies View Related

Need Help With A Query For Finding Matching Records

Aug 5, 2005

I have two tables Table 1 and Table 2. Table 1 has 6 fields 175 records and Table 2 has 4 fields and 330 records. The first three fields in both the tables are the same. I need to find all records in the first table that have a matching record in the second table based on those first three fields. How can I accomplish this? Any help would be greatly appreciated.

Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved