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?
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.
Hello Ladies and Gentlemen,
My Access-table contains a Destination and Time field, i want to compare records for Time field with next records of same field.How can I do it..? Let me tell u an exp..
Time field is having a record like 06:23:45 AM for the destination - 'A', so i want to get all the records where the difference is upto 5 Seconds (i.e - 06:23:50 AM) and the Destination is same. It might be possible that there are one more records having time value 06:23:55 AM.
Hi all,
Does anyone have a database lying around for registering and rating wines?
If not, I will just start from scratch and maybe if it goes well, post it here in the Sample Database section.
Cheers!
Hi
I'm using MS Office 2000, I have an Access database table that contain many fields, namely I need to update FieldA from an Excel Spreadsheet according to the specified primary key of CustomerID. Is there a way to write automate process to import the records from an excel spreadsheet and then place those records in FieldA of the Access Database according to the corresponding CustomerID? The reason I ask is that these two fields are not exactly in the right order in my spreadsheet and my table and to do it manually will take lots of effort.
Your prompt response is greatly appreciated
Thanks
Not even sure where to start so if this isn't a query question, please point me in the right direction.
I work in a plant that has a number of cranes. Each crane should be inspected each shift (three shifts per day), each day (less Sunday).
I have exactly what I need to capture the inspections being done. Inspections are identified by crane #, shift, and date.
How do I determine if an inspection hasn't been done? How do I compare the inspections that have been entered against the standard of inspections for each crane, each shift, each day?
I am below average with Access on my best day so please type slowly and be specific. Thanks for any help you can give.
Forrest
I have a table that contains the following fields: ID (autonumber), Item, billing date(mmm/yyyy), count, and approved.
My first query returns records where the approved field is null. My second query returns returns all records for 2 months ago where the approved field is not null.
This is my problem, I need to pull records that have NOT been approved for the prior month BUT if they were approved 2 months ago AND the count is the same, it does not need to be returned in the query. Also, if it was approved 2 months ago but the count is different than what it is for 1 month ago, it needs to show in the query.
I have a table called tblAuthorizations. It appears as follows
AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits
1374006036.........356679..........20255102....... ...4/22/13......7/21/13.....0..............416
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520
504564357...........469432..........20254788...... ....4/22/13......7/21/13....0..............520
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520
I work in mental health, and when we put in a request for a consumer to receive PSR & BST services, we put the above information into our database. Each record identifies which consumer the authorization is for, a start date and end date for them to receive services, and the amount of two types of services (PSR and BST). When one period ends, we request another period, such as in the first three records above, and then the final 2 for another consumer.
What I need to be able to tell is how their service authorizations change. For instance, if I were to compare the first and second records, it would tell me that PSRunits decreased 4/22/13-7/21/13 from 1/21/13-4/21/13 levels.
I have a query that displays the records for each consumer for the current period, but I can't figure out how to identify the previous period's number of PSRUnits or BSTUnits.I've tried adding the following fields, but this isn't working.
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
I have created a mock employee database to work out kinks before I create the real one. One table has employee info (employeeID, name, address, email, phone numbers, etc.). The other table has "employee actions" with fields: employeeID, actiontype, salary, jobtype, jobtitle, jobhours, bonus, actiondate. Actiontypes are "hire", "salary change", "bonus", "leave". It is a little more complicated but that is irrelevant to my query question...
I created a query to show employee salary changes, amount and action date. I want to create a calculated field that shows the dollar change and percentage change from last salary to new salary. So the query involves multiple records or rows, which seems to be a black hole in terms of information on how to do this. If it were Excel, I would just do a quick nested function calculation and be done with it but with Access, where each record is independent of the others, how to calculate it. I know there must be a way to do this because otherwise, what good would an employee database be?
So the questions are: (1) can you do calculations (other than totals or counts) using multiple records/rows? and (2) if so, how do I do it? It seems I would have to do a a specific sort and it would not be easily updatable.
I am trying to create a query based on a transaction table which compares the time between two types of transactions.
The initial record would have "A' in the type field and the second record would have "D" in the type field.
each pair of A and D records would be matched on o_time and idnum fields, and I would want to pull each pair and us the datediff function on c_time, using records marked with A as the initial time and records marked with D as the followup time.
Is there any way to accomplish this in a single query or do I need to create two subqueries?
Hi all
I have a rating table with the rate ID and title. Is there a way to represent the rate as stars image like this
http://65.19.160.24/forums/images/rating/rating_5.gif
http://65.19.160.24/forums/images/rating/rating_4.gif
http://65.19.160.24/forums/images/rating/rating_3.gif
http://65.19.160.24/forums/images/rating/rating_2.gif
http://65.19.160.24/forums/images/rating/rating_1.gif
instead of just showing the title of it.
i tried with this code
Select Case Me.RateID
Case 1
Me.Image0.Visible = True
Me.Image1.Visible = False
Me.Image2.Visible = False
Me.Image3.Visible = False
Case 2
Me.Image0.Visible = False
Me.Image1.Visible = True
Me.Image2.Visible = False
Me.Image3.Visible = False
Case 3
Me.Image0.Visible = False
Me.Image1.Visible = False
Me.Image2.Visible = True
Me.Image3.Visible = False
Case 4
Me.Image0.Visible = False
Me.Image1.Visible = False
Me.Image2.Visible = False
Me.Image3.Visible = True
End Select
but there is somthing wrong it dosnt change ?? correct me if im wrong please
I currently have 2 tables in a database that I'm trying to build a query off of. The first table lists personnel & their position #s, and the second table has a column for "rated person", "rater", "senior rater" and "reviewer" which are all part of our annual evaluations. I'm trying to program the query to display the names of the person attached to a position #, but can only get the query to return the actual position #s.
I'm using the table to assign each position # appropriately, i.e. position # 10202 is rated by 10103 and senior rated by 10101; I do this with the lookup data type. Once each position # is assigned, I'd like the query to display the name of the personnel instead of the position #, i.e. when queried, it would return under the rater column JOHN SMITH instead of 10202, and in the senior rater column JOHN JONES instead of 10103, etc.
I need to create a record of Users. I created a form with fields containing Username,FirstName,LastName & Email Address wherein the UserName is equals the value of the fields FirstName & LastName. I have a save command button and when I click it, I want it to be able to check if the new entry has a similar UserName that exists in the database, if it exists then it will allow me to edit the UserName and then save the data otherwise will allow me to save it.
View 3 Replies View RelatedHi All,
I have fought hard to try and figure this out and I have gotten 50% there.
I have a query that generates the following calculated fields through numerous calculations etc....:
[De-Merit Marks]
[No Del with Major Faults]
Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.
Rating: IIf([qry_vend_rating2]![De-Merit Marks]<=10,"A",IIf([qry_vend_rating2]![De-Merit Marks]<=30,"B",IIf([qry_vend_rating2]![De-Merit Marks]<=50,"C",IIf([qry_vend_rating2]![De-Merit Marks]>=51,"D",""))))
However I have no idea how to integrate the Major Faults part.
Basically:
0-10 = A
11-30 = B
31-50 = C
51-100+=D
If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".
Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.
TIA
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")
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).
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 RelatedI want to be able to compare two tables to see whether their Column Names & sequence are the same.
View 1 Replies View RelatedI 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.
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 RelatedI'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?
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] .....
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
.....
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" .
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
I have attached a sample database. Basically I want to have some lines of code that generate the result table, which is tbl_readmit_result.
As you can see, the difference is the addition of a new column called re_admit_status.
Rule is:
Status = "y" when the admission date, compared to the previous discharge is less than 7 days, otherwise "n", for the same pt_id. You cannot compare the two dates on different pt_ids.
Is there a way that this can be done automatically without having to go through the record manually?