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 Replies
ADVERTISEMENT
May 15, 2013
I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:
initial field:
xxx;yyy;ppp;yyy
targeted result:
xxx;yyy;ppp
How this could be achieved ?
View 5 Replies
View Related
Aug 26, 2006
is it possible to compare values in 2 different rows? for example if the query returns this:
1 | $5.00
2 | $6.00
3 | $20.00
4 | $30.00
i want to find out the cost difference between 1 and 2 which will be $6-$5 and then 2 and 3 which will be $20-$6 and so on. how can i make this happen in SQL? any suggestions?
View 6 Replies
View Related
Aug 27, 2013
I have one table A that has a Name field that I would like to find if it matches any of the cases in table B with 2 other fields: Field 1 or Field 2
There is no way of establishing referential integrity because the Name Field can be found in either Field 1 or Field 2.
How would I go about searching so I can pull in the rest of the data that I need with when the Name is matched either with Field 1 or 2?
View 2 Replies
View Related
Jun 25, 2013
I have 2 tables and 1 query. Table dbo_RepOrderItem includes columns:
RepId
OrderNumber
Item
ShipDate
SerialCardID
Table tbl_LBP Sales Location Num includes columns:
Location ID
Rep Region Code
What I've been doing so far through my code is working through a query 'CalculateTotal' which looks up a structure number (Which I enter through an InputBox) from table dbo_RoicStructure, gets the SerialCardID from the same table and collects the associated RepId,OrderNumber,ShipDate from table dbo_RepOrderItem and checks that the OrderNumber(s) returned exist in column Location ID in table tbl_LBP Sales Location Num. It also checks that Rep Region Code is not equal to 'INT' nor 'inte'.
My code for all of this works just fine. What I am trying to do now is simplify my results. I want to only return rows that do not have the same Item, OrderNumber, and RepID.
Fore example:
OrderNumber: Item: RepID:
11 3 1
12 3 1
11 4 1
11 3 1
14 7 4
16 8 8
It would now count 5 existing RepID(s) rather than 6 because it would have deleted the duplicated row. This should only be deleted in the user's table not the actual table.
And speaking of the user's table. What I have been doing so far is only returning the results (right now it returns 6, which as I explained above is incorrect) but I also want to return a table or query that would should the user the work behind the returned number.
This is my code:
Option Compare Database
Option Explicit
Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant
[Code] ....
View 10 Replies
View Related
Jul 8, 2013
I have a table that has a date field set to text. I use the Mid function and get just the month in a query. I have a textbox on a form that when the user adds a month or removes a month from a selected listbox it adds or removes that months number from the textbox.
I want to us that text box on the form as the criteria for the month (the Mid function getting the month) and I can seem to get it to read what's in the textbox and use it as the criteria. I can manually type in a number and it works fine.
View 6 Replies
View Related
Jun 26, 2014
I can not solve this problem, my Access knoledge is mediocre.
I have the following table from imported data:
RecNr IPaddres DateandTime
1 178.00.000.01 20-11-2013 21:47:21
2 178.00.000.01 20-11-2013 21:47:59
3 178.00.000.01 20-11-2013 21:48:35
4 178.00.000.01 22-11-2013 20:44:59
5 178.00.000.02 22-12-2013 19:47:59
6 178.00.000.02 22-12-2013 20:47:59
7 178.00.000.03 01-02-2014 12:47:59
8 178.00.000.03 01-02-2014 12:48:30
9 178.00.000.03 01-03-2014 11:47:59
etc
I want to make a query that results in displaying records that have the same IP-address where the difference in the date/time stamp is within 1 minute. When the date/time stamp is > 1 minute the record can be deleted from the table.
View 6 Replies
View Related
Oct 28, 2007
i want to know if it is possible to compare values (a string) stored in a field with a field of another table?
for example, i have a table named Courses, it has field Course ID...and Course Requirements.
the field Course requirements can accept only 3 value: A,B and C.
i have used a combo for this.
then, i have Student qualifications table. i have also designed a form to input student qualifications.
my problem is that i have to compare qual. of students with the req of the course, to allocate them a specific course. if both values meet, the db automatically allocates the course to the student.
View 1 Replies
View Related
May 12, 2013
So in my example you have a Weld (tblWelding_WeldingID) and each weld can have more than 1 consumable (tblconsumables_consumableID). have a third table that links the two, trackID,consumableID, weldingID)
What I need to be able to do in a report, is under the heading COnsumable, list the 1 or many consumables.
Also you select the consumable from a combo box..... if that just makes it that much harder...
View 2 Replies
View Related
Dec 7, 2007
Okay I have a query that pulls from a table. It's pretty simple - nothing big.The query will be used to correct information in some sales tax data.I have 3 colums with sales tax info, and they should all be the same. I want to compare each column to show only those which don't match each other.NetTax, TotalTax, and TaxB are the fields.In the query, for the criteria of NetTax, I put in:<> [TotalTax]When I run the query, I get results, but they are all equal.EG:4.35 4.35 4.35I don't want them to show, and not sure if that's the right way to go about it or what.If I run without that criteria, I get 21,600 results.With that in the criteria, I get 304 results. So it is working somewhat...but for whatever reason some don't seem to be seen as being equal? I don't get it. Data type is number and they are formatted the same way.
View 14 Replies
View Related
Dec 17, 2007
Hi...
I will try to keep this succinct.
I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query? I would need this comparison to take place within this query, rather than creating a new one.
Thanks!
Kathryn
View 8 Replies
View Related
Sep 10, 2006
Hello everyone! I've found much use of this forum recently as I am working on a database concerning my work.
I'm not a newbie working with access but this problem seems confusing at the beginning.
I've got 2 tables
1) Information about produced details. Article, Mass etc.
2) Mass constants
Mass min. Mass max. Constant
What I need, is to build a query that compares the mass of the detail with the range mass min. and mass max. and shows the mass constant that the detail belongs to.
Has anyone tried to solve this kind of issue before?
Greets from Estonia,
K
View 1 Replies
View Related
Jan 20, 2014
I'm doing a bank-rec database, so on the one hand I'm comparing positive debits, to negative credits. How do I query to get, say, "Bank1 -$1000, Bank2 $1000"
View 1 Replies
View Related
Mar 2, 2015
I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)
I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".
Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico
[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico
View 4 Replies
View Related
May 9, 2006
Hey all
Im rather new to access but have to use it at work. Ive been asked to see if there is a way to delete duplicate records from a table.
Now, I have 2 tables. The 1st table (tbl_list) contains records of various customer details. This list is old.
The second table (tbl_new_list) contains new customer details.
We have found that we have the same customer details in tbl_new_list that are in the old table.
Im looking for a way to compare these 2 tables so that the values in tbl_list are not in tbl_new_list.
I have tried numerous methods using append queries but nothing seems to work.
Thanks in advance people
P.S the data will be compared through a telephone number.
View 1 Replies
View Related
Jan 1, 2015
There is a table with many columns. I want to add many rows to this table. The easiest way is to write a query to do that. But most of the columns have validation criteria, lookup tables, default values, nulls not accepted condition and what not. Where as my new records to be added have only the values in 4 columns and the rest are null. Is there a parameter that can be used to bypass all these controls and add the records? You are going to say "You will destroy the integrity and consistency of the database".
View 1 Replies
View Related
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
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 1 Replies
View Related
Mar 24, 2006
I want to compare two queries as follows: Select all the records in query1 where the value in fieldA does NOT also appear in any record in fieldA of query2. Is there an easy way to do this using the query tools or will this require a vba function?
View 3 Replies
View Related
Nov 8, 2004
I have 2 tables. Quotation Details and Order Details. As you fill in the Order details Form I wish to compare fields from the
Quotation Details Table. Is this possible? Please advise.
Thank you.
View 1 Replies
View Related
Jul 18, 2014
Trying to create a query from a table. This table has some fields with zero all the way down for all the records and i wish for the query to omit the whole field if that is the case.
View 9 Replies
View Related
Oct 31, 2013
I have a material usage table that tracks material going into products...Oftentimes the same material will be used and multiple records will contain that material.
I want my query to return only records with unique material used. I'm clicking the "Unique Values" and "Unique Records" property setting but its not working.
View 1 Replies
View Related
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
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
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
Sep 16, 2013
As a Access newbie I am looking into a problem that I hope to solve structurally and "transparently" via Access in stead of Excel.
I would like to compare two similar tables for different months. I am looking for:
+ loan id's that are new in table(t), i.e. not exist in table(t-1)
+ loan id's that exited: that is exist in table(t-1) and not in table(t)
+ loan id's that stayed in both
How do I solve this in the easiest way?
View 4 Replies
View Related
