Original Table
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450
C D 4/1/08 15 890
E F 9/1/08 78 750
A B 12/1/07 42 789
X Y 1/1/08 17 900
C D 4/1/08 34 90
Needed Output
ozip dzip shipdate wgt Transport cost
A B 12/1/07 20 450
A B 12/1/07 42 789
E F 9/1/08 78 750
I need to create a query which extracts rows with wgt >60 and also WHEN the fields ozip, dzip and shipdate are same, the query should add the wgts and then sum of wgts should be >60- for example the first and fifth rows should be extracted since sum is 62.
I have attached the output that is required for the above example. please help
Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent
A record in a table linked to various others has recently been corrupted in my database. When i try to delete the record an error message appears telling me that the 'Search key was not found in any record' and the record will not be deleted. This corrupted record is causing me serious problems and i need rid of it by the end of the day.
I am having a problem with all of my comment fields showing up as Japanese and I need to get some reports printed... Does anyone know how or why this would or could happen?
I have checked the tables but they are normal. When you query the tables is when the comments start to mess up.
Hello, I'm new to the board and I need an urgent problem fixed. Ok, I have a query that contains the type of TRAINING REQUIRED FOR EACH POSITION/PERSON IN A COMPANY . It has:-
1. Clock no. (uniqe for each employee) 2. Job Title 3. Position effective date 4. Training code 5. Description of training code 6. Retrain requirements ( number of days that are needed after initial training to reach retraining date, this varies from 0 to 365 days depending on the code) 7. Status of employee (whether active or inactive, the criteria for this is active) 8. Due date for completed training
I have another query which shows the TRAINING RECEIVED FOR EACH EMPLOYEE. IT has:-
1. Clock no. 2. Job title 3.Last names (employee) 4. First name 5. Status of employee 6. Training code 7. Date of training received
QUESTION IS HOW DO I CREATE I GUESS ANOTHER QUERY THAT WILL TELL ME WHAT REQUIRED TRAINING CODES THAT EACH EMPLOYEE HAS NOT RECEIVED? I BASICALLY NEED TO KNOW IF EVERY EMPLOYEE HAS BEEN TRAINED CORRECTLY ACCORDING TO THEIR POSITION OR NOT.
I need to make a database for a association and collect the Business information and Personal information the following information is needed For business Business name Corporation Name Address city state Zip Code County Phone fax then Type alcahol yes/no tobacco yes/no EIn number STI Number Sale tax Number
Personal
Name Address city state zip phone cell email
*some things one owner might have more than one store. i can make the form and everything just need help with relationships and that quiries that sort of stuff.
hi i am making a database for a company, in which i have the feild "joining date" and the feild "paymant method" which can either be yearly or half yearly
im looking for a quiery that will work out who need to pay when and also i need it to bring them up two weeks before due payment..... im then going to mailmerge them.. which i am not 100% sure how to do. :eek:
Hi. I am creating a website for my employees. I have created a table, query and a form in microsoft access containing a lot of different fields, including employee ID and password. I have also created a form in Dreamweaver. How do i link the database to the form and how do i create a code that will log in the employee if his password and username are correct, into a new page and if the info is wrong, to another page. PLEASE HELP. This is very very urgent!
"The Visual Basic for applications project in the database is corrupt."
I've had a quick search but can't find a solution. This db has always been access 2003 (Not converted from prior file version). I can't even import into a blank db as the same error occurs.
Hello all being my first post i will try and make this as clear as possible...
its a new system my company.
my manager would like if possible the system to work in this way..
firstly a form whcih will book rides so when opened will ask current members to etner their card no which is a 16 digit number always beggining with 5555 1946 and the customer no. added on which is 0000 0001.
If a customer has not registered another link so they can register and enter details and once done this press enter and message generating a new 16 digit card number 1 mroe than the highest number in the system. new members are allocated 2000 points on their card and points are taken off when booking new rides plus a 10 point booking fee.
then a button will be clicked to take back to reserve rides which allows the customer to book a ride using the ride id and ride number on a specific Session of which there are 3 times in the day 11:00, 14:00 and 17:00 and with a maximum of 5 places on each ride during each session being able to book.
once session ID and time,date selected the next box should show the available rides
if member does not have enough points to book an error message is displayed.
I know this all sounds confusing and ive attached my progress so far its very basic being a basic user i hope users out there can help as this has to be done before friday
Basicly I am doing an Access Project for my school coursework, but I am really confused and my teacher isn't that great.
My coursework is based on a video rental company. The database (in breif) has to be able to bring up a customers details, and add a loan to/under that customers details. (as you would expect from a video shop).
Now, to be honest, I dont know if I have the correct items in each table, but was wondering if someone would be so kind to have a quick look to tell me what to change and where to put the relationships for the database to work correctly.
I have taken a screen shot of my current database: http://img259.imageshack.us/img259/8216/accessproblem4de.th.jpg (http://img259.imageshack.us/my.php?image=accessproblem4de.jpg)
If you would like any other information, please let me know. I am really grateful for this!
I have used sum() function in a query where I have got other attributes. However, the sum() doesn't work for some reason.
I could not attach my database as its size is too big. i have uploaded it to this server where you can download it. aimreda.co.uk/db (http://aimreda.co.uk/db)
Hello all I am having a problem with my vb code. I am realatively new to vb and what I am trying to do is search through a table according to what is selected on my form and delete the values that equal the value selected int the table. What I have so far is this
Private Sub cmdDel_Click() Dim rsSource As DAO.Recordset Dim rsDestination As DAO.Recordset Dim theBug As String Dim SelItm As Control Set SelItm = Me.Users If Me.Schedule.ItemsSelected.Count = 0 Then Beep Exit Sub End If Set rsDestination = _ CurrentDb.OpenRecordset("User_Course_Room", _ dbOpenDynaset)
For Each Itm In Me.Schedule.ItemsSelected rsDestination.FindFirst "User_ID = " & Me.Users & "" And "Course_Room_ID = " & Me.Schedule.ItemData(Itm) & "" ### THIS LINE GIVES ME A ERROR 'rsDestination!Course_Room_ID = Me.Schedule.ItemData(Itm) 'rsDestination.FindFirst "Course_Room_ID = " & Me.Schedule.ItemData(Itm) & "" rsDestination.Edit rsDestination.Delete
Next Itm
Me.Sessions.Requery Me.Schedule.Requery
End Sub
In the line with the bold writing next to it I get an error. What I am trying to do is search for the record with the user id selected and the course room id seleceted and delete that record but I get an error. Any help would be highly appreciated.
I'm looking for advice on the best method to accomplish the following from the esteemed members of this Forum (You all have provided excellent advice in the past to this Access Dummy, with my thanks), (I've also searched the forums without result):
I would like to make several fields "required" fields on my form, easy enough, in that I set the Required property on the table to "Yes".
What I would like to happen on the form is that when a user tabs out of a required field, a message box pops up that says "This is a required field" and/or when they click any of the following command buttons I've created, "Save Record", "New Record" or "Close Form", that a message box pop up and list the required fields that they missed.
Any ideas, with code, macros, or other solutions would be greatly appreciated, keeping in mind that I'm just not that swift to start with.
I had a query which used to work perfectly fine, then I changed one of the field names that the query was returning. And then, I rechanged the same field back to what it was and I save it. Later, whenever I run that query, it asks for a parameter with the same field name I changed even though I don't have it anymore.
I have written 2 SQL queries which I wanted to combine using IF-Then statement, if possible. the first one: SELECT NewNumber as [Main_number] FRom try_SQL Where Type='A' OR type ='V'
The second one is: SELECT OldNumber as [Main_number] FRom try_SQL Where Type<>'A' AND type <>'V'
I want to output the Main_number as a text file on my D drive. At the moment I have to to output 2 different text files, then combine them. can someone please help me?
Can some one please tell me how I can use a delete query. I have the following code, but it does not work. Please let me know where I am going wrong --------------------------------- Dim StrSQL As String StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & PLANT_CODE_txt & " ' " DoCmd.RunSQL StrSQL ---------------------------------- In the above and below code PLANT_CODE_txt is the form field that I am taking values from to delete from the table
(or)
---------------------------------------- Dim StrSQL As String dim pt_code as string pt_code = me.PLANT_CODE_txt StrSQL = "Delete FROM PLANT WHERE PLANT_CODE = ' " & pt_code & " ' " DoCmd.RunSQL StrSQL --------------------------------------------------------------
And if we are using multiple delete queries should we have the DoCmd.RunSQL <variable> after every delete statement or is it after all the statements
Hi All, Can anyone help me count postcode instances. I have a query that looks at my customers table (tbl_Customer_Details) post code field (PostCode), at the moment I have got it to strip out the right side of the postcode leaving me the left district side eg. HG12 8EN becomes HG12. I would then like to count how many times each postcode instance occurs so I can create a report on the result so I can track which district the customers are coming from. I hope this explains the problem. Any help would be much appreciated as I promised the Boss this for tomorrow....:confused:
this is my SQL so far, it strips out the left of the postcode:
SELECT tbl_Customer_Details.PostCode, Left([Postcode],4) AS Code FROM tbl_Customer_Details GROUP BY tbl_Customer_Details.PostCode;
I have an indexed table (unique autonumber) in Access. I have in this table an other column with every X rows a number (X varies) and the rest of the rows below are empty. This situation repeats again with another number and again all the fields under it are empty. Is there a possibility to run an update query to update all fields below to that certain number? Can you define a range (by using a range in the autonumber) to update all the fields in that range to that specific number ??
Hi Folks, I have a table that has Student_ID, Course, and Grade. So each student has multiple records, one for each course they took. But what I want to return is only those people who have All F's. If I just put "F" as a criteria on the grade field then it will pump out those records that have an F, including those who maybe only have 1 F, but all I want is a list of Student_ID's of all of those people who have nothing but F's. Any ideas? HELP..i'm really in a bind with this one.
How can I get the record with a date field that is the earliest and the latest in a table.
tblSample(ID, Name, Type, ItemDate)
The ItemDate can be any date entered by the user, so the ID will not give me the earliest and the latest record. How do I make a query that will give me the earliest ItemDate and the latest ItemDate. I need to do this in Access. Thank you.
I am trying to create a sequential number field in a query. I cant use an auto number for the fact that i am combining multiple tables. Any help with this would be greatly appreciated.
