I have a table that has two fields; odometer reading and reading date. I want to able input the reading dates along with the odometer readings. Then I want my query to calculate the milage difference between each date.
I was hoping someone might be able to help me here. I have a table which has amongst this,two fields which are date values. I need to perform a function to subtract Date (A) from Date (B) an produce a further field showing the difference in the date values.
In a nutshell
Field (A) = 02/01/2006 Field (B) = 07/01/2006 Field (C) = 5
My employer uses a third party employee tracking system and I do back end work to massage the raw data for ad hoc reports using tables created by this third party software.
Is there a simple means to have a query compare Table A and Table B where Table A is a list of all jobs in a company - both filled and vacant - and Table B is a list of all employees in the company? The table containing jobs data has no field designating whether a job is filled or not. The only way to determine vacancies is to compare the two tables and select only those job numbers where there is no matching employee.
So, using examples from an earlier question - one that KeithG answered perfectly - let me see if I can demonstrate what I'm trying to do...
The Jobs table has two fields; JobNum and JobDesc. The Employee table has three fields; EmpNum, EmpName, JobNum.
Here's Table A, Jobs;
100, Boss 105, Aide 200, Manager 201, Worker 202, Worker
Here's a sticky one? (maybe...) I have a number of aggregate totals from several queries all linked to the Input Query and all Nz'd as 0's
From the image enclosed the totaling text box (top right left) is calculating the value in the properties box. The value should be 3.52 + 5 - 8 = 0.52 the value showing is -4.475 (which is 3.52 - 8) so I see it's ignoring the 5 value but I can't see why.
The ACDays and ALDays values are both generated by the same module, but with different Leave types. and as I mentioned previously all Nz 0'd
Hi, I have a query with two fields. One has the "MEASUREMENT" and the second one is the month containing the values. In the MEASUREMENTs I have gross revenues and discounts, how can I create a new MEASUREMENT (net revenue) that is calculated by subtracting the discounts from the gross revenue.
Hi, I hope someone can help me out with this problem:
I have 2 tables with identical stucture, and no unique ID number. Both had identical data, and the databse was then copied onto 2 laptops. Now further data was added onto each.
I want to now merge the three databses (ie original one, and 2 laptops) into 1, without creating duplicates- so I need to identify which are the new fields added to the databases. I figured that by somehow subtracting the original table from the new ones, i would be left with the new data, but dont know how to do this. Or is their another way?
I have a report based on a query where I get results sorted by month after i have chosen 'from date' and 'to date' in the beginning. I also want a set number for each month to be subtracted from the total sum, and then added to the next month (even if it is a negative number). Ex. Total in january is 100, then it is supposed to subtract 50 from that number, report the result of 50 in one own field at the end of january, then send this to february where the total sum is ex. 200, then the total sum for february is supposed to be 250, and then again a different number, like 60 is automaticly subtracted from the total sum and displayed in one field as the result 190. And so on.. Does anyone know if this is possible, and how? I am a noob, both in access and in the english language :P
On my Form, I want to have a query look at one specific field for a number (1). If the number is found then I would like to have the query do basic subtraction on two other fields. (one from each table)
What I have so far is one unbound text box with the following, =IIf(tblDaStrategy!DaNumber=1,(tblDaStrategy!DaTar get-tblDaReport!DaActual1))
Simply put, if one field has the number one in it, then take the other two fields and subtract (ex. 100-75) to display 25.
It's not working. I keep getting the #Name?
Update: I think I got it. I went back to square one and made a master query that contains the data from both tables. Then I bound one of the forms to the query. I updated my IIF statement and poof. It seems to be working. Now, where's the Asprin and my pillow?
Update 2: Bummer, now my magical 25 is in place but now my form is locked tight. Guess I need to consider abandoning this concept and simply producing a report with the 25 on it. Unless someone out there has a better idea.
I'm sure this is simple but my brain is mushy this morning!
I have two datetime fields ADMISSION and DISCHARGE with values such as 03/02/2008 15:00. How do I convert these to dates only? I don't want to just *format* them as dates, which I can do. In this example I would want to convert that date to 03/02/2008 (or 03/02/2008 00:00)
It's an idiosyncrasy of the way the NHS calculates hospital length of stay. Our previous IT system had dates and times in separate fields so I could just ignore the time field and work out length of stay based on dates only. However, our new system returns a combined datetime.
Patient A 03/02/2008 00:15 to 03/02/2008 23:45 = 0 days but Patient B 03/02/2008 23:45 to 04/02/2008 00:15 = 1 day
Basically, you count a day if there's a date change, even if that was only half an hour (or even a few minutes) either side of midnight, but you DON'T count a day if there was no date change, even if that was almost 24 hours.
So formatting them as dd/mm/yyyy wont work ,as I will still have partial days when I subtract one from the other and INT([DISCHARGE]-[ADMISSION]) wont always work either as it would calculate 0 for patient B.
OK, I have a DB that is storing tickets data. Basically I want to, in code, subtract from the no_of_tickets field each time someone books a ticket so that when it gets to zero, no more tickets can be booked (i'm ok with the checking the field to see what the value is).
Problem is, never done this before so I don't know what the proper phrase for it is so when I'm trying to search on here and google its not much use. Can someone please help me with what it is i'm trying to do so I can search and hopefully find a solution?
Hi, I am very new to access. I have designed a db for my employer, but I can't figure out how to subtract one field from another without having it subtract fom all others. My employer is a counselor and wants to know how many units are left per client. There are several diffent services available per client. (88K, 86H, 81H, ect) For instance I tried to use a query, but when it was subtracting (number of units)-(number of used) It subtracted from all the different service types. Then I tried to do it on the form and all I get is #Name? I can email you a copy of the database with a mock client if i'm not explaing myself enough. I know it's probably something very simple and I would appreciate any help. Also, if you know of any good books or websites where I can learn more I'd appreciate the info. I'm learning from Access 2002 Bible.
Hello I have a form with a control named CourseDate. I also have a text box that will show the user the cancellation date based on the what the course date is. I am looking to have this populate in the on current event of the form, but I am haveing difficult getting it to work. The Date in the text box should be 3 Working days less the course date. Any help would be greatly appreciated.
I have a form named "Inventory" which i input inventory items into the "inventory" table. the field name is Quantity that i put the quantity of the inventory item in. There is also a SKU number for the item. I also have a form "Sales Receipts" with a subform "Receipt Line Items" in which i add products that get sold. it has a SKU number field and Quantity field also for amount of selected item sold (For example qty is 3 for the item). What i want is when the qty in the receipt line item is added i want the inventory item to automatically decrease by this amount(which would be 3 in this example), i want to be able to add more quantity of stock to the inventory item and it only decrease when sales are made. Is this possible?? Thanks for anyone willing to help me on this.
I have a DB where after I close out a month of sales I no longer want to be able to view records in my SalesEntryForm. I have a little check box that is the ClosedOut Check box. When I close out Febuary, I want to change all of the ClosedOut Check boxs to yes if the invoice was made in February. I have the query to get all of the records I want to close. How can I automate changing the values of these check boxes
I need to write a query that will let me determine the values to use as input for another query which will calculate results based on deciles:
eg I have a series of data ranging from 0 to 100 and I need to sort by this field and then pull out the value of the 10th, 20th, 30, ....90th and 100th records.
is there any way I can do this? also what if I want to pull out every 23rd record for example?
edited to add:
The way I would do this manually in excel is to sort by the target value column and then fill down the numbers 1-23 in a new column and then sort by that column, copy the results and load them into access so I can run a query based on them. This is a little time consuming and hardly elegant but gets the job done. Just hoping someone can help me with a niftier way.
Hi I have looked at the forum to see if I can find my answer, but it doesn't seem to be there.
I have 3 tbl SchoolDetails, School Activities and Keystages
The values from each table SchoolDetails ID & School Name SchoolActivities: Activity Title, Date, Start Time, No of Workshops Keystages: Keystage and NoOfPupils
I am trying to send one confirmation to each school. My problem is that when we book out the time it is booked out either by HalfAM or HalfPM, therefore if a school books a full day they get 2 records in the SchoolActivities tbl.
They also get numerous records in the Keystage tbl depending on how many classes are involved.
I am trying to bring together the sum of NoOfPupils - even though this can have two records (AM & PM) and also the numbers can be in numerous records in Keystage.
I am using a form where I select values from combo boxes to enter parameters for a select query. It works fine where the table queried only has one value in the field concerned. However, it does not work on the fields that have several values (entered through a multi-selection box). Are there any ways to query fields with a multi-selection? Thanks, Niels
I need to create a query with the MTD field I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?
Here are the fields in the query
First Field: Field: PartNum Table: Part Total: Group By
Second Field: Field: UnitPrice Table: OrderDetail Total: Group By
Third Field: Field: NumOrdered Table: OrderDetail Total: Sum
Fifth Field: Field: CurrentYear: Year([OrderDate]) Table: (blank) Total: Group By Criteria: Year(Date())
Sixth Field: Field: CurrentMonth: Month([OrderDate]) Table: (blank) Total: Group By Criteria: Month(Date())
When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.
Hi. Can someone help me? I am trying to make a query return all values (please see attached jpg) It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?) In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.
Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.
hello, I am just wondering whether there is any way I could define a query in such a way to include a record in Table A if field FA is approximately equal to field FB in Table B by no more than, say 12.