Compare Sum Of Values In Rows With Same ID To Another Value

Feb 6, 2013

I need to compare the sum of a values in a column of rows with the same id. For example:

Code:
ID Value1 Value2

122222 20 40 (these are okay, the sum of Value1 in rows with same ID equal value 2)
122222 20 40
333333 100 100 (ok)
111111 50 100
111111 50 100 (not ok, the values with same id add to 150, so the first row containing this id should be deleted so the the total of Value1 equals Value2)
111111 50 100

View Replies


ADVERTISEMENT

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Compare Rows In A Table

Nov 14, 2005

Hi,

I have a table with four columns - A, B, C, D
I want to compare the values in columns A, B and C in row 1, with the values in row 2 and if they are the same, I want to copy the value that is in column D in row 1 into column D in row 2. I then want to move to the next row in the table and compare the values in row 2 with the values in row 3 in the same way. This will continue for all rows in the table.

Any help would be greatly appreciated as my VB is not up to much.


Thanks in advance

View 2 Replies View Related

How To Compare Time Values?

Nov 15, 2007

Hi.
I need create a query that can compare different time values.
My time field format something looks like 12:59 PM.
And compare other fields that has +- 10 minutes from my input.
It means when I input 12:00 PM to the query inputt then it lists records that have times 11:50 AM and 12:10 PM.
But I don't know how to compare these..they are not natural numbers..but they aren't text niether..
I tried to do it
[Arrive_Time] + 10
[Arrive_Time] - 10
in the criteria..but..doesn't work.

View 4 Replies View Related

Compare Values Between Two Subforms

Jul 16, 2007

Any idea if there's a way to compare values between two subforms on one main form?

I have a Required Inventory and and an Actual Inventory subform. I want the Actual Inventory field to be conditionally formatted to turn red if it is less than the corresponding required field and green if it is more. I know how to use Conditional Formatting, but I don't know how to reference the values in the next form.

Note: I have no problem using conditional formatting compared to values in the parent form. The syntax I used for that was:

[Forms]![frmMain]![txtControl]

But still no luck referencing subform controls.

Thanks.

View 1 Replies View Related

Compare 2 Values From Fields On A Table

Mar 6, 2006

Hi there

This is probably quite easy but I cannot make it work so would appreciate some help. I have a table containing vehicle information. I have 2 fields, Current Mileage and Max Mileage. I want to do a comparison of the 2 fields and should the Current Mileage be greater than the Max Mileage, the query returns the Vehicle Registration. Test data has been entered in the table I am querying and I was expecting 1 value to be returned. However the query as I wrote returned either all the vehicle Registrations or none of them. Can someone help with this please.

Thanks in advance for the assistance
Craig:)

View 2 Replies View Related

Compare Negative Values With Positive

Jun 5, 2013

I am trying to to find a way to show all the positive values with nagative, in order words, if the value is 10,000 is should be followed by corresponding -10,000 so that the user could investigate if the entry is a reversible GL.

View 2 Replies View Related

Compare Values Of Columns Within The Same Query Table

Nov 19, 2006

Hi,
I am trying to compare two columns' values within the same QUERY table, but I kept on getting the "Data type mismatch in criteria expression" error. What am I doing wrong?

Here is my Select Query Statement:
SELECT qryDedparmDedetail.EMP_ID, qryDedparmDedetail.[Employer Amt], qryDedparmDedetail.[Employer Actl], qryDedparmDedetail.[Admin Amt], qryDedparmDedetail.[Admin Actl], qryDedparmDedetail.[Employee Actl], qryDedparmDedetail.[Employee Amt], qryDedparmDedetail.FirstOfSTATUS, qryDedparmDedetail.FirstOfAGENCY, qryDedparmDedetail.FirstOfTITLE, qryDedparmDedetail.FirstOfFORMAT_NM, qryDedparmDedetail.RepUnit, qryDedparmDedetail.FirstOfDEDTYPE_CD1 AS Expr1, qryDedparmDedetail.SumOfNBR, RepUnit.REPUNITDESC, qryDedparmDedetail.LeftType
FROM qryDedparmDedetail LEFT JOIN RepUnit ON qryDedparmDedetail.RepUnit = RepUnit.REPUNIT
WHERE (((qryDedparmDedetail.[Employer Amt])<>"Employer Actl") AND ((qryDedparmDedetail.LeftType)="01"));

View 2 Replies View Related

Modules & VBA :: Compare Values Of Several Columns Of A Record

Jan 23, 2014

I would like to compare the values of several columns in the same record and remove any duplicates. So...

ID | Col1 | Col2 | Col3 | Col4 | Col5
-------------------------------------------
1 | A | B | C | D | C
2 | C | C | C | D | D
3 | A | A | B | A | D

In the above example I would remove one of the Cs in row 1.

Two Cs and one D in row 2 and two As in row 3.

At the moment I have the value of each column stored in a variable and each one is compared against each other using LOTS of If statements.

View 5 Replies View Related

Compare Data In Different Tables And List Non Matching Values

Nov 2, 2007

Hi

I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below

Table 1

xyz - A1 - Do not use
ABC - A4 - Use

Table 2

123 - A1

The result of the query should show me

A4 (as this does not appear in table 2

I am not too familiar with Null values as there will be many null values that i want to ignore.

Any help will be appreciated.

View 4 Replies View Related

Modules & VBA :: How To Compare Values Between Input Form And Table

Nov 12, 2013

I am making a dp for end of day sales, and i want to compare data in a table to the numbers inputted in a form and see whether this value is higher or lower. the table displays the target sales figures for each day, and the form allows the area mangers to enter in the current day figures.

View 3 Replies View Related

Modules & VBA :: Query To Compare Values To Show All Records Where Form Combo Value Is Selected

May 1, 2014

I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.

For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.

View 4 Replies View Related

Column Values To Rows?

Aug 25, 2006

I am trying to devise a way to display query results in a row instead of a column. The table on which the query is built has this structure:

Column1: Index (pkf)
Column2: Group (int)
Column3: Position (int)
Column3: ItemCode (txt)

When I do a select query, the data comes back like this:

Group Position ItemCode
11A
12B
13C
14D
15E
16F
Etc.

I want to arrange the data this way:

1ABCDE (Etc)

I keep thinking that a cross-tab query is the answer, but all I seem to be able to do is to get counts of Position 1, 2, 3 Etc.

I’m baffled. Does anyone have a suggestion?

Thanks,

Rick

View 4 Replies View Related

Modules & VBA :: Populating Rows With Set Values?

Sep 11, 2013

I have a Machine Running Conditions database that records running conditions for 2 machines. The table is set up like this

Code:
LotNumber Parameter Condition
130506m11 Screwspeed 100
130506m11 StockTemp 50
130506m11 KniveSpeed 75

In reality there are many more machine parameters (about 30). We have two machines that usually are set with 2 different set combinations of parameters. The combinations vary at times but are usually one or the other

Currently the user is going in and selecting the parameters from a combobox manually for each lot number.

To save him time and work, I want those fields auto populated with the parameters for a machine. So the user will hit a button for machine 1 or 2 and the parameter rows will fill with that machine's correct parameters.

View 6 Replies View Related

Reports :: How To Get Sum Of Values For Specific Rows

Nov 5, 2013

I have a report being generated that pulls in a bunch of data. The processed column is a boolean value.

I want to be able to set the text of a label to be equal to the "material cost" for all the rows where is processed is set to false.

Picture of the report: [URL] ....

View 3 Replies View Related

Combining Rows With Duplicate Values

Jun 17, 2013

I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.

I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.

Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?

Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.

View 8 Replies View Related

Totaling The Values In Multiple Rows In A Table

Oct 16, 2006

HELP! I’m working for a charity in a small town Tanzania and there is not a soul for miles who knows anything about access.

I have created a database to track requisitions of Office Supplies. I have table full of the requisitions which contains multiple rows for each item we have in the store and the quantity requisition on each occasion.

What I want is a query that will calculate the total quantity of each item requisitioned in a month. Is there an easy way to do this??

View 1 Replies View Related

Queries :: Comparing Two Values From Rows In Same Field

Jul 10, 2013

I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].

The idea of the table is that you can input and update the value of each product on a given day.

I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..

A picture of what the table looks like is attached for reference.

View 9 Replies View Related

Compare VBA

May 9, 2007

Is there some free software somewhere to allow me to compare the vba in two differnat access dbs highlighting the differances??


THanks

View 2 Replies View Related

Compare

Dec 21, 2006

Hi All:

I need a mechanism that takes the excel spreadsheet or table and determine if there are any new deal groups. (In this spreadsheet I allready have a deal group field) I will be using Book and DealGroup as combination primary key to compare against the table "8 -- YTD Principal Investments" which will hose the YTD numbers and serve as a historical table we will update throghout the year.If there is a new deal group the append into table "8 - YTD Principal Investments"


Thank you

View 1 Replies View Related

How To Compare Before And After

Apr 26, 2007

Please help with query/report. I have the following problem.

One student evaluates course before and after. There is several questions related to this evaluation (all are same before and after) and for one question there is 5 possible answer (stored in option group)

I would like to build a report or a query where I can compare data from each student (before and after) for all questions. Is this possible to do in access.

So, StudentA: questionOn: answer: one columen: before, second column: after…. Next question.

View 1 Replies View Related

Compare SQL String

Jan 4, 2007

Hi, all.

I have string: strSQL = SELECT SUM column FROM table

I need compare it with constant number, for Example:

If strSQL > 50000 Then ....

its not working, any Ideas?

Thank You in advance

View 4 Replies View Related

Compare Digits

Mar 8, 2007

Hi there!
I'm bulding this database where the user will have to insert a full phone number. What I would like to know is? if is there any way where Access can pick the first 5 digits of the number to compare with information already imputed in. by example if the user input the number 01942789789, access pick the number 01942 to look for a match? I know I could do with two diferents fields one for the code number and other for the number, but my boss only want one field. Is there anyone that could help me

Thanks

www.joaobeca.co.uk

View 1 Replies View Related

Compare Query

Aug 19, 2005

I have a table that shows Part Numbers. Each part number can be listed multiple times depending on how man Build ID's it is associated to.
Example:
Part Nubmer 123456 could be associated with:
Build Id's F187, A910, F001, C810, E010, C809
or it might only be associated to 1, 2, 3, 4,or 5 of those Build Id's.
What I want my query to do is find any Part Number that is only associated with anything less than all six of the Build Id's.
Example:
Part Number 123456 is only associated with:
Build Id's F187 & A910 or only F001 & C809 or only E010.
If this was the case I would like for that Part Number to show up in the Query along with whatever Build Id's it is associated with as long as it is not associated with all six Build Id's. The Build Id Field is also part of the table.

Any help on how to do this.

Hope all of this makes sense.

Thanks!

View 1 Replies View Related

Compare 2 Tabels

Feb 8, 2006

Hi,

I've made 2 tabels tblIn and tblOut,
Now I want to make a query which get the differences between the number of product A in tblIn and the number of product A in tblOut the same for product B,.... It's possible that there is nothing in tblOut from product X so the query just has to show the number of tblIn of product X, I've tried a few things but i can't make the query can anybody help me???

thx

View 4 Replies View Related

Compare Two Lists

Mar 5, 2007

I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id?
For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?

View 4 Replies View Related







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