Modules & VBA :: Compare The Last Two Data-points With 1?
Jul 19, 2014
one more thread of mine today
I have this table:
-> tblTest
Code:
test_id | test_Name | test_Date | test_value
------------------------------------
1 | Company A | 01.01.2010 | 0,90
2 | Company A | 02.01.2010 | 0,95
3 | Company A | 03.01.2010 | 1,10
4 | Company A | 05.01.2010 | 1,05
5 | Company B | 01.01.2010 | 0,9
6 | Company B | 02.01.2010 | 1,20
7 | Company B | 03.01.2010 | 1,15
8 | Company B | 05.01.2010 | 0,95
9 | Company B | 07.01.2010 | 0,80
The important level is 1,00
Now I will, for each company, check:
- Is the value of ID-1 >1 AND the value of the ID-2 <1, THAN give me a 1
- Is the value of ID-1 <1 AND the value of the ID-2 >1, THAN give me a -1
In this example the result should look like this:
Code:
test_id | test_Name | test_Date | test_value | test_Code_result
-------------------------------------------------------------
1 | Company A | 01.01.2010 | 0,90 |
2 | Company A | 02.01.2010 | 0,95 |
3 | Company A | 03.01.2010 | 1,10 |
4 | Company A | 05.01.2010 | 1,05 | 1
5 | Company B | 01.01.2010 | 0,90 |
6 | Company B | 02.01.2010 | 1,20 |
7 | Company B | 03.01.2010 | 1,15 | 1
8 | Company B | 05.01.2010 | 0,95 |
9 | Company B | 07.01.2010 | 0,80 | -1
Probably I have to carry out this in vba.but i dont know how I can calculate across the datapoints (rows).
View Replies
ADVERTISEMENT
Mar 12, 2014
Compile error: Method or data member not found
Code:
Option Compare Database
Option Explicit
Dim intItems As Integer
Dim strName() As String, datBirthDate() As Date, intAge() As Integer
Private Sub Form_Load()
intItems = Val(InputBox("How many names do you have?", "Name List"))
[Code] .....
View 8 Replies
View Related
Dec 2, 2014
I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:
Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:
1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.
2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .
View 14 Replies
View Related
Apr 24, 2015
I want the user to be able to see the source/reference of (many of) the different data points in my database, so I need to store sources/references in the database itself. I want to have all references in one single/common table (list of references). Each reference may be the source of many data points, in different columns in a given table, and in different tables. Is this possible, and can it be done in an elegant way?
View 2 Replies
View Related
Apr 13, 2006
I am a newbie, so please forgive me for such an easy question, but I am stumped. I attached a text file that shows the data I am working with. What I need to do is take each product (labeled Prod) and do a weekly sum on the quantities and compare against a set number to see if the quantity is lower or higher. For instance, I need to take column 12, regardless of value and compare it against set number. If the quantity is less, then I need to add the value of column 12 to column 13 and compare the summed value against set number. Again, if the sum is less than set number, I then need to take the value of column 14 and add it to the summed value of the previous step (sum of 12 & 13), then compare this new sum to set number. This process keeps taking place until I reach a summed value that is greater than set number. Once that happens I need to identify the column that sent me over the set value and hold that data. For instance, if column 33's (out of 52) summed value takes me over the set number, I want to know that it was column 33, so I can run further calculations against that value. The column header's are week numbers and I need to identify order points based on lead times and when I will run out of material. Is this beyond queries? I think so, but if it is, I don't know how to exactly begin the code in VBA either. I think I would use an If then Else stucture with a counter switch set from 1 to 52, unless comparison exits function, but not certain. HELP?????:confused:
View 3 Replies
View Related
Oct 28, 2007
I have a task that will require me to subtract the current time from the previous time as long as the records fall within the same Extract Count. For example:
I have 8 transactions associated with ExtractCount #2 and 8 start/end & processing times. I need to subtract each row's time from the previous row's time. I have a conceptual idea of how to do it but need real help to figure out how to do this using a query.
Thanks,
Phoenix81
View 4 Replies
View Related
Aug 12, 2014
The following code gives me error message. There is problem in Format function. How can I write using VBA that "Scantime is less than 08:00"
Code:
strsql = "SELECT Count(BatchNo) AS CountOfBatchNo " _
& "FROM Table1 " _
& "GROUP BY ScanDate " _
& "HAVING ScanDate=" & J & " and Scantime< format("08:00:00","hh:mm:ss")
View 10 Replies
View Related
Aug 15, 2006
Hi guys,
I have 23k records
I have run the wizard to find all the duplicates according to one field.
So i have the results, except that some of the records contain nothing within the searchable field - so it returns all the records with nothing in the searchable field as duplicates.
How would i modify the query to perform the search but exclude the records with no data.
The SQL query below is the default wizard output for find duplicates, how would i change this??
SELECT Master.EMAIL, Master.MemID, Master.MEMBERNO, Master.FIRSTNAME, Master.LASTNAME, Master.POSITION, Master.ORGAN, Master.ADDRESS1, Master.ADDRESS2, Master.ADDRESS3, Master.ADDRESS4, Master.COUNTY, Master.COUNTRY, Master.POSTCODE, Master.SALUTATION, Master.MOBILE, Master.TELEPHONE, Master.WORKPHONE, Master.FAX, Master.RATE, Master.CLASS, Master.ADDREF, Master.DATEJOIN, Master.WEB, Master.Field26
FROM Master
WHERE (((Master.EMAIL) In (SELECT [EMAIL] FROM [Master] As Tmp GROUP BY [EMAIL] HAVING Count(*)>1 )))
ORDER BY Master.EMAIL;
Another thing is, once i have the result... which query would i use to actually delete the duplicate data??
Cheers, and advance thanks!! :cool:
View 9 Replies
View Related
Jan 5, 2014
So I have a table:
table1: company_name, year_month, company_rating, rating_change
For each company, I would like to compare the rating from 201201 and 201202 (and keep going for each month) and if there's a change in the rating, update rating_change to the difference in rating values.
The company_rating is a value from 1 to 20.
How should I go about doing that?
View 6 Replies
View Related
Dec 4, 2014
I'm trying to compare a Currency formatted field to a Number formatted one and getting wrong results.other then formatting the Number as Currency too ? Tried to use Val() but the Currency returned 0
View 7 Replies
View Related
Dec 17, 2004
I'm willing to be this is simple, but I haven't been able to figure it out.
I have created a database with one table, tblUserData. I have one form called frmLogin.
tblUserData has two fields, Username, and Password.
frmLogin has two textboxes for user input, User and Pass, and has one control, cmdLogin.
What I am trying to do is - When a user types their username and password, and clicks on the control
button, the value of User.Value is compared to the Username field of the UserData table.
If a matching record is found, then Pass.value is compared to the Password field from the matching record.
If they match then the program proceeds.
Can anyone point me in the right direction please?
View 1 Replies
View Related
Sep 5, 2013
I want to be able to compare two tables to see whether their Column Names & sequence are the same.
View 1 Replies
View Related
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
Mar 20, 2005
I would like to compare 2 tables by looking only at the names of the fields, data types and their properties (e.g. required yes/no). I am searching for a method to export this information to another table and then make comparison. Maybe sth. similar to “documenter” or another tool. Some ideas would very much appreciated. Thanks Bartek.
View 1 Replies
View Related
Aug 25, 2005
Been trying to crack this one for a while hoping someone on here might be able to help me. ;)
I have a table with a list of required software and a table with a list of computers and the software installed software. I made a query that displays the machines with the required installed. My problem is if a machine has more than required then it does not display the record. If it needs Office, Photoshop but has office, Photoshop and quark installed then it does not display that machine.
This should be quite a simple thing, I have played with Like, NOT, Where and others but with no luck...
Any help would be greatly appreciated.
Graeme
View 2 Replies
View Related
Jun 13, 2013
I am running into an issue when trying to compare data stored within the same table.
tblParcels - holds data relating to an individual parcel of land; unique identifier is ParcelID. No repeating data.
tblValues - holds data relating to the value(s) of each parcel; new values entered annually; unique identifier is TaxID; has field to link to tblParcels. One parcel can have multiple entries for each year tracked.
I have two unbound text boxes on frmMainForm where users can enter Tax Years to compare (txtYear1 and txtYear2). Theory being that users will be able to select any two tax years and compare the parcel values.The report (rptMaster) is built using three queries and is used to show the change in value between txtYear1 and txtYear2:
qryYear1 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear1.
qryYear2 - pulls all values from tblValues where the TaxYear equals the year typed in txtYear2.
qryCompare - joins the results from qryYear1 and qryYear2, along with other common fields, and populates rptMaster.
My problem lies when a parcel does not have any values for one or both of the years entered into the text boxes. I completely understand why, qryCompare can't show any records if there are no results coming from either qryYear1 or qryYear2. What I am not getting is how to work around this issue.I would like for the report to show "$0.00" for the value if there is not a value for that Parcel in tblValues for the year entered.
tblValues example data:
TaxID ParcelID TaxYear Value
1 5 2011 100,000
2 5 2012 200,000
3 6 2012 75,000
In the example above, when comparing 2012 values to 2011 values, my report shows the values for ParcelID 5 but not for ParcelID 6 as it does not have a 2011 value (and won't since information about that parcel didn't exist in 2011).
View 5 Replies
View Related
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
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
Feb 28, 2013
I have a table "Customer Plans" with 100s of rows of data with 3 columns
'Name', 'Company', 'Plan Type'
I want to create a new column or table "Customer Details" with the the column name 'Customer' under the following criteria:
If the data in 'Customer Plans.Company' Column meets a certain criteria as in = "Corp&Gov" or "Head Office" or is "blank", I want it to populate the new column/table "Customer Details.Company" with the corresponding data in the 'Customer Plans.Name' column
e.g.
if
Name = Anna B
Company = Corp&Gov
then
Customer = Anna B
I want to populate 'Customer' with Anna B for that record and so on down the list of data in 'Company' column.
If however the 'Company' column doesn't meet the criteria, I want to populate the new column/table 'Customer' with the current data in 'Company'
if
Name = Anna B
Company = Dealer
then
Customer = Dealer
View 1 Replies
View Related
May 22, 2015
I'm working on creating an events log. In my table I've got a field that pulls the individual's username (that they use to log into the computer) and logs it against their log entry.
As part of my query (for my daily events log report) I'm pulling up said username.
I'd like to give access to the specific user to edit his (and only his) entries from the report, but I'm getting a type mismatch with the below code:
Code:
If me.UsernameID = "KraussLJ" Or Environ$("Username") Then
DoCmd.OpenForm "frmShiftLog"
DoCmd.FindRecord Me.ID, acStart, , acSearchAll, , acAll
Else
MsgBox ("You are not authorized to edit this entry")
End If
(before you ask, "KraussLJ" is our ops manager who insists on having final editorial control over the shift log before sending it higher up the chain)
I can get the msgbox to run if I block out the
Code : Environ$("Username")
, but beyond that I'm a little stuck. Should this be a dlookup function or am I overanalyzing this way too much?
View 2 Replies
View Related
Dec 4, 2013
I'm trying to create a query that will compare the data in 3 fields in a record, choose the largest (I also have a criteria to order by if more than 1 field has the same entry and it's the largest of the 3), and then group by that.The fields I will need are as follows:
PRODUCT table:
ProductName
Chemical
ChemicalAbstract
PhysicalState
NFPAHealth
NFPAFlammability
NFPAReactivity
qryQuantityOnHand query (which doesn't link directly to the PRODUCT table, it links through associations with other tables):QOH...I will eventually need information from another table for the final reports, but I don't think it has to be included in this query.
The fields NFPAHealth, NFPAFlammability, and NFPAReactivity each may be 0, 1, 2, 3, or 4...I need to ignore blanks; if 1 of the above fields is blank, they will all be blank.For any record, I need to compare the number in those 3 fields to each other, and choose the largest number and group by that rating.
In other words, if the largest of the 3 numbers is a 3 in the NFPAFlammability field, all those products need to be grouped together.If the same number appears in at least 2 of the fields, the order that determines the grouping is: Flammability, then Health, then Reactivity..Ultimately the report will be grouped as follows:
Flammability
Rating 4
Product 1
Product 2
Product 3
Rating 3
Product 1
Product 2
Product 3
Rating 2
Product 1
Product 2
Product 3
Health
Rating 4
Product 1
Product 2
Product 3
[code]....
and each of the groups will be sub-totalled.I'm stumped at trying to create the query in the first place.The added aggravation here is that we are dealing with 23 stores, each with their own mix of products. I have another table that contains the information about which products are in which store.
View 4 Replies
View Related
Jul 11, 2014
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
View 2 Replies
View Related
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
Jan 28, 2008
This is probably an easy question, but I can't figure out how to get a table to show numbers in 2 decimals points when they are inputted with 2-5 decimal points. I changed the DATA TYPE to Number and DECIMAL PLACES to 2, but when I do that, it just rounds it to a whole number. Please help, thanks!
View 6 Replies
View Related
Jan 6, 2006
Hi, I have this query and I would like to have the avg display with only 2 decimal points. This is my SQL and I think I have to use this code but I'm not sure.
FORMAT(CountOfStudent Attended,'.00')
If I ad this after the SELECT statement my query will not work. What am I doing wrong?
Thanks!
~D
This works
SELECT [Attendance for Avg].CRN, Avg([Attendance for Avg].[CountOfStudent Attended])
AS [AvgOfCountOfStudent Attended]
FROM [Attendance for Avg]
GROUP BY [Attendance for Avg].CRN;
View 3 Replies
View Related
May 25, 2005
I have a very annoying problem with my break points that I can not figure out how to fix. I have a database that when I place a break points on code it will not break the code and go into debug. I have created a very basic form with one button and the following code:
Option Compare Database
Private Sub Command0_Click()
Dim str As String
str = "this is a test"
MsgBox (str)
End Sub
The message box will pop up but the program will not stop on either on the str... line or the msgbox... line. I'm sure some how or another I turned off the debugger but I do not know how to turn it back on.
Thank you for any help.
View 1 Replies
View Related
