Hello everyone,
I'm quite new to access (used to php mostly) and for usual task it's ok but now I have trouble for a quite simple query I think.
I have to compare the values from 1 field and select only the rows just before the value becomes smaller and also the very last row.
example:
if I have a row with 1, 2, 3, 4, 1, 2, 3, 4, 5, 1
I need to put 4 and 5 in an other table
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.
Hi guys- totally in the frying pan here...I can't seem to get past this initial stumbling block with Access
i've made a simple form that "should" create a primary key from the first two digits of a clients last name and last four digits of their phone number...i've tried an update query button and a refresh data button...the key field gets filled in on the form, but it will not transfer to the underlying table...I keep getting a null value error for the key....any help would be greatly appreciated...
heath
Hi,
I need to compare records in the same table. I know that this is typically accomplished by joining the table with itself. However, this is not entirely suitable for my needs. I need to compare the first record to every other record - excluding itself - and then compare the second record to every other record excluding itself AND the first record. I want to continue this pattern for all the records. Is there any way I could go about doing this?
Any help would be much appreciated.
Edit: Each record has an autonumber generated key
I have been searching on here and have not found an answer, could be due to me not seeing it or its not here. I have a multi part question.
Anyways, here is what I have. I am getting data from a time clock, which dumps the data into an access database. It dumps the data as EmployeeNumber (Number), DateTime(text). I use the format command and make a new table to give me EmployeeNumber(Number), Date(text), Time(text).
Question 1:How can I convert these to date and time fields instead of text fields?
After I do that I need to be able to compare the records within the table, such as: I need to check to see if the date is the same between one record and the one above it (assume that I have sorted this correctly) if the date is the same then I need to subtract the first record from the second record to give me the number of hours between the two records.
Question 2: How would I go about comparing those records in the same table?
Thanks in advance to those who can help. Even ways not to do this would be good.
Okay that Title sounds confusing, but so is this task. Let me explain what I'm trying to do:
I work for a property management company and I'm trying to build a database that will allow me to catalog the inventory of furniture in several (thousand) rooms in several different buildings.
There are several types of rooms on campus all with different requirements. I want to eventually create a "standard" inventory list for every TYPE of room and then compare the actual inventory of each room to the standard inventory of each TYPE of room to make sure it is correct. The problem is that every room has several possible acceptable standards.
I was thinking that for each TYPE of room I could have several possible standards and then run a query that returns all rooms that do not match any of their standards. Does this sound doable? Any suggestions for how I might run that? Am I in way over my head? haha
I need to identify pairs of records by their ID and date fields, then compare their number values to get a text result.
I have a query that has the following fields
AuthInstanceID, ConsumerID, AuthNumberID, AuthStart, AuthEnd, PSRUnits, BSTUnits.
Sample data is below (PSRChange and BSTChange are fields I want to self populate based on the comparison I'm trying to do)
AuthInstanceID....ConsumerID....AuthNumberID....Au thStart....AuthEnd....PSRUnits....BSTUnits - PSRChange -BSTChange
1374006036.........356679..........20255102....... ....4/22/13.......7/21/13....0.............416~~~~-Loss~~~~~-None
-432536491.........356679...........20255102....... ...1/21/13.......4/21/13....104..........416~~~~-Loss~~~~~-Loss
-124970517.........356679...........20147863....... ...10/23/12.....1/20/13....208..........520~~~~-None~~~~~-Null
504564357...........469432..........20254788...... ....4/22/13.......7/21/13....0.............520~~~~-None~~~~~-None
282523535...........469432..........20254788...... ....1/21/13.......4/21/13....0.............520~~~~-None~~~~~-Gain
356661326...........469432..........20254788...... ....11/20/12......1/20/13....0.............416~~~~-None~~~~~-Null
What I need to do is compare these records in the following fashion:
Identify pairs of records where ConsumerID is the same AND AuthStart dates are successive. Then compare PSRUnits of more recent record (by AuthStart) with the previous record (by AuthStart) and determine the change of PSRUnits between them. If change is positive, "Gain" in field PSRChange, "Loss" for a loss, "None" for no change, and "Null" if there is an error due to no previous record to compare with. Do the same with BSTUnits/BSTChange
I've tried adding the following fields, but this did not work
PrevEnd: (Select Max(AuthEnd) from tblAuthorizations Where AuthEnd < Auths.[AuthStart])
PrevBST: DLookUp("[BSTUnits]","tblAuthorizations","AuthEnd=" & [PrevEnd] And "ConsumerID=" & [ConsumerID])
Very new to access, I need an easy way to compare 2 tables with a common field (the name field) and list just the records that appear in the second table but not the first (primary) table.
Please help, thanks.
Hi,
This may be a little difficult to explain but here goes:
I have a table in access 97 with the field Account. An account name is usually written as 0000000_A, but it MAY also have a secondary account and is usually written with the same first series of digits but ends in B e.g. 0000000_B. Therefore, we could have an account 0023654_A and 0023654_B.
Now for each account name there is another field called Items which are chosen from a combo box on a form (item 1, item 2, item 3, item 4, item 5, item 6, item 7 and item 8). An account may have more than one item.
Now if account B exists then it MUST contain the same entries as account A but can also have additional items.
So we could have a situation where:
0023654_A
item 1
item 2
item 3
item 4
0023654_B
item 1
item 2
item 3
item 4
item 5
item 6
Now what I am trying to implement is that if the core items (these are those that are contained in both - which would be item 1, item 2, item 3 and item 4 in the above example) are changed in either accounts making them not equal then a warning message should come up stating so. IT IS IMPERATIVE THAT THE CORE ITEMS ARE PRESENT IN BOTH.
I have some experience in Access 97, but unsure of how to tackle this. Could somebody please guide me ???
Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"
Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.
Is there a beter way to do this?
Thanks!
P.S. Sorry for my poor english. It's not my native language.
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?
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.
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.
I have a table to store "issues" with fields Issue name(text), target date(date) and status (combo box-active & pending statuses). default status for new issue is active. when clicking a button i want to change the status of every record to "pending" status (combo box value should be pending) if the target date<current date.
View 2 Replies View RelatedI am building a database to track contract of employees so that I can know which contracts are valid and which are expired.
My table has the following fields:
ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)
Challenge
I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.
Hi,
I am trying to compare a value in my databse produced by the date() function, short date format, to one that is exactly 24 hours after the value recorded by the date() function. If the value in the database is 24 hours prior to the current date(), I need to flag a text box a diffrent color to alert the user. I am unsure on the If statement that I will need to produce this result.
Any help would be greatly appreciated!!!!
Thanks
Mikeco555
Hey everyone,
I currently have an Excel Spreadsheet w/ 20 columns and 800+ rows. The columns are such things as Product, Product Code, Description, etc. It has clearly grown to big for an Excel file so I created an Access table from the spreadsheet called All.
Goal - have employees open a form and select a product from a dropdown box and then place check marks next to the info they would like to see for the product they chose.
I created a seperate table called Product, and a query for table All, and a report for the query. I created a form w/ a combo box that lists table Product and has a submit button. I added a criteria in the query under the column product that looks at this combobox. When you run the form, select a product, and click submit it opens a report that shows the results of the query for that product.
The part I cannot get is the check boxes that determine what columns are show in the report. I added a checkbox to my form and then went to my query and unchecked show and added a criteria to look at the checkbox in my form. All that did was erase the column from the query permanently.
I'm sure I'm going about this is the wrong way, but this is my first hack at Access and I'm trying to learn as I go.
thanks for any suggestions,
-Eric
a a aa a a a a a aa a a a
View 4 Replies View RelatedI'm a complete newbie to MS Access, and I'm working on my 2nd database.
Just a small and probably retarded issue: I have a table which includes a field, which contains four numbers. In a query I want my users to be able to enter a number, like 6, and the three numbers after that will have to be wildcards.
Example: The field has these entries:
8243
8184
6423
When the user enters "8", it should show the first two records. When the user enters "6", it should show the third record.
How can I accomplish this?
Hi Guys
i have created a table and am trying to run a qry that counts specific data, i have been searching but cannot find the answer. I have a standard qry that has the criteria of "staff name" and dates between ## And ##. Now when that information is returned, how do i get a count figure for the results. So for example if there were 15 entries for september it would say september = 15 (ish). I have been told to assign a recordset but no other information was forth coming. Can anybody help with code or qrys for creating a recordset.
Please remember the noob status when it comes to access - lol
Cheers Guys
Anthony
I have taken over the Access DB from Hell.....
Recently our system crashed and so I fixed compacted and repaired, and all seemed to be good to go. However, next day a bunch of random fields that our reps were once able to add information to are no longer able to add info to. They seemed to be locked out, however, I go into the various forms that were set up and enable is set to Yes, and Locked = No.
Any ideas?
This also effected a Check box field of 4 also.
Thnx for any help
Hope nobody minds but I don't really know Access and I have a table that needs sorting pretty desperately.I have a table with 5 columns that I need to filter out so just the remaing records are left over.http://img208.imageshack.us/img208/7328/untitled1wb8.th.jpg (http://img208.imageshack.us/my.php?image=untitled1wb8.jpg)The column 'fax' is the column with the records in it and the following columns need the record removing if the box is ticked.TimFPS, OurFPS, OnFPS, NeedsToBeFPS, however the column 'NumberWeHave' I'm not too sure about so I'll ignore it for now (I may need to take them out later)Sorry to be a complete noob and if anyone can help it will be muchos appreciated.
View 2 Replies View RelatedI have this program that adds information to a ms database file in a very odd manner.i cant change the way the information comes in but is there a way to move it into the correct location in a new table. information is put in as a group of 19 records.
whats happening is this:
these are the colums
primary key,group id,info,info,info,group order,info,info,info
i need to information from 4 of the colums into a new table so i have 19 colums and 1 record.
is this even possible.
Frustrated net admin
I am trying to make a query in which I want to get the data from the following:
i put in order ID number like this:
=(214)
and I successfully get the data from order id in data sheet view
but I want to get data from 214,220 and 200 at the same time how should i enter in the design view criteria field?
I tried =(214,220,200) but it did not work
thanks in advance
Hello im new to this and i been working since yesterday on a database. Basically I have 1 table that holds names and numbers asociated with and also the current state. that would be "ACTIVE" or "INACTIVE" and after a week or so (actually after i run a report) should change to "ACTIVE Z" and "INACTIVE X".
Now I have all the querrys and tables and forms and all. but what i want to do is to automate something im doing manually.
I run a querry that shows me only the records that have the state "ACTIVE" and i need to change them to "ACTIVE Z". Now what i am doing is, after running the querry i select all and go Control H and replace "ACTIVE" for "ACTIVE Z".
Is there any way to automate this.
cus i tryecd to do a macro that 1st open the querry and then executes the replace command but that just opens the replace pop up. I still have to tipe "ACTIVE" and "ACTIVE Z" in the fields to replace it.
Is there any way to automate this??
I would apreciate the help
THANK U
As the title suggests i am brand new to Access (about 2 days). I am trying to create a query that will group the fields according to their names and then add the income for all orders for each individual customer. After that i simply have to sort them. I want to sum the total income per customer and put them into descending order.
Here's a small sample of the list to get the idea:
Customer NameIncome per Order
Sanchez, Cindy$1,326.00
Lizzack, Mark$1,326.00
Patel, Mitesh$1,092.00
Sanchez, Cindy$1,000.00
Patel, Maria$780.00
Mui, Sylvia$780.00
Patel, Mitesh$500.00
And what I'd like the results to be:
Customer NameIncome per Order
Sanchez, Cindy$2,326.00
Patel, Mitesh$1,592.00
Lizzack, Mark$1,326.00
Patel, Maria$780.00
Mui, Sylvia$780.00
Thanks immensely for any help you can offer. I realize there is probably a very simple solution to my question, but after 5 hours of searching the help button, and various online forums, I'm at my wits end.