I have a problem thats really hurting my head and probably has a simple solutions. Please help!
Basicly, I have a query that adds to fields that are numbers, which works fine but when one of the fields is 0 it doesnt add, it just leaves it blank. I have used the nz function in my first 2 fields as some of them are blanks. My calcuation field is simply like:
[returned]+[loaded]
but I get:
Returned Loaded total
0 2
I tried wrapping the nz around my calculation but that gave me like this:
Returned Loaded total
0 2 02
7 8 78
Help! Why doesnt it add 0 and 2 to make 2?
Any help might save my sanity or computer screen.:mad:
I have developed a query which adds accumulated costs for each client in my DB, I am having trouble adding the VAT tax which is 17.5% to the query though, I seem to get a lesser total once tax is added which can't be correct.
Here is my query: SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.[CostPerNight], Rooms![CostPerNight]*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_ Facilities!FacilityCost AS TotalCost FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID=Rooms.ClientID) ON Hotels.HotelID=Rooms.HotelID) ON Room_Facilities.FacilityID=Rooms.FacilityID;
I’m looking to find CASE_ID's where the BEGIN_DATE is greater than 09/30/2005 and less than 10/01/2006 where the CASE_STATUS went from O to C and where the STAT_REASON_CODE does not equal INT27 and I have come up with this query which works fine.
WHERE (((DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)>#9/30/2005# And (DSERDBA_CASE_STATUS_CHANGE.STAT_BEGIN_DATE)<#10/1/2006#) AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_FROM_CASE_STATUS )="O") AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_TO_CASE_STATUS)="C") AND ((DSERDBA_CASE_STATUS_CHANGE.STAT_REASON_CODE)<>"INT27"));
My problem is that I also want to exclude cases where the case went from CASE_STATUS C to O on the same date that it went from O to C (it closed and reopened on the same day) And I don’t know how to adjust this query to accomplishment this.
i have the following query and i am getting the following output
SELECT Sum(tbl_RFQ_Details_AVOB.[AVOB RFQ]) AS [SumOfAVOB RFQ], tbl_Status.Status FROM tbl_Status LEFT JOIN tbl_RFQ_Details_AVOB ON tbl_Status.ID = tbl_RFQ_Details_AVOB.Status GROUP BY tbl_Status.Status, tbl_RFQ_Details_AVOB.Status;
SumOfAVOB RFQStatus 16900110RFQ in Process 12610000Under Review 1200000 Decided 3500000 Terminated 1000000 Terminated Supplier 6000000 Leverage Back 40000000Uncompetitive 500000 RFQ in Preparation
Now i require a new line in addition to this that says that the total value is and the sum of those values must be given.
required output:
SumOfAVOB RFQStatus 16900110RFQ in Process 12610000Under Review 1200000 Decided 3500000 Terminated 1000000 Terminated Supplier 6000000 Leverage Back 40000000Uncompetitive 500000 RFQ in Preparation totalvalue Total RFQ
My company has a microsoft access table with about 1200 IDs containing names, addresses etc. Now I want to print this out in an excel sheet because I want to create address stickers, but I want every cell in the excell sheet to add information about the columns in the database together. I might be very unclear, let me demonstrate what I mean: The Acess file might look like this: id | name | address 1 | john | Street 01 2 | sara | Street 02
Now I want to have 2 excell cells which contain the information like this: name_address name_address
In my example, 2 cells with the following information:
john Street 01 sara Street 02
So I dont want 4 cells, I want to add the information together from 1 Id to 1 cell, but I also want to add linebreaks to it, It must eventually become an address sticker
can someone help with this. I have 2 fields in a query
tbl1_nbil = 3 tbl2_nbil = 0
however when I add them using
Bill = [tbl1_nbil] + [tbl2_nbil], I only get a few of the added numbers in the calculated field. In some records it works, but in the majority it does not
It onl seems to add when both fields are greater than zero
I hope I can ask this question so that it makes sense.
I have a totals query that I'm using to make a Chart. The query works fine but I want to change the look abit. What I'm doing is quering a table looking for the total number of times that there is a check box in a field called PDC RTA and totaling the Hrs as well. The data comes back looking looking like this. PDC RTA-----Count-----Total Hrs -1 ---------- 6 -------- 53 0 -----------25 -------- 78
My problem is that when I chart this the -1 and 0 show up at the bottom of the bars on the chart. I would like to rename these to fields on the qry qrid then I can chart the new field names so that it will make sense to the user.
Hello folks first post here. I tried reading through the boards to find someone with a similiar question, but not luck yet. I have been working with a basics of Access for about a year. I did take take a bit of programming in college but I don't remember much of it, so I looking for a little help. I can usually Google for answers but I cannot seem to find the write key words this time.
A while back I created a table that tracks employees' data and recently I started working with the insurance data I have gathering for sometime.
It goes something like
Field Name Data Type
SS# Number (primary key) Insurance $ Number PPO YES/NO HMO YES/NO Self YES/NO Spouse YESNO Children YES/NO Family YES/NO Dental Self YES/NO Dental Spouse.......etc.
What I'm trying to do is turn the yes/no's into total dollars for each person. I have done this in Excel but the fields were differnet and I had to use a many, many if.'s or a lookup table(data array)
PPO doesn't = a dollar amount by itself, but PPO + Self = $19.03 or PPO + Self + Dental Self = $23.00. Or the person can just have Dental Spouse = 8.86. Pretty much the people can take a piece and leave another.
"Insurance $" is another field name and is where I am trying to get the total to go.
I was thinking of a lookup table but I don't know how to do that in outside of Excel.
I am thankful for any help or even a nudge in the right direction.
Hi all, I have this select query that pulls out the last 13 distinct values in in descending order from a table. However in order for me to play with these values I need an ID number and ideally i'd like to do this on the fly rather then do a make table and then use that. Is there any way of adding a 'autonumber' column to a select query on the fly in so that it can be used as reference ?
I have a field, quantity, which i want to add all the quantities together to show a total. I want to do this using a query as i will use this total for other calculations. When in the query, i have selected "sum" in the totals column in the quantity. What do i need to do extra? I am not getting any results at the moment.
Im trying to add fields together in a query (these fields are results churned out by an iif expression), however access just concatenates the fields instead of summing. Access will not allow me to change the format of the original fields either.
My form is based on a select query. There are 2 combos that filter the data displayed in the form by status and property. All of the data is contained in the same table and the query is looking at just the one table. Clicking a button opens a listbox that contains all of the filtered data and clicking a record in the listbox populates the form with that record and closes the listbox. It works good like this.
When I try to add one field (labor) from a second table to the query with that same field added to my form, as long as there is a value in the labor field in the table then it works. If there is no value for labor then the form returns no records. There are records there but they do not have labor assigned yet. I set a default value for the field on my form - (not assigned yet). The listbox displays the records even though there is nothing in the labor field, but clicking a record in the listbox returns an error of 'no current record'.
Actually I dont even have to add the labor field to the form, just having it in the query mucks up the form. I must need an isnotnull statement in the criteria or something.......
Just trying to figure out how I can run a select query on some data I have and how to also include an additional field which has a value counting the number of rows.... eg in the first row there would be the usual results and the new field would have a value of 10001, second row would be 10002, third would be 10003.
I've done some searches for sequential numbering but couldnt find anything so far.
Is there anyway I can add a sort of count field to my query, for example what I require is the result of my query to include a column which counts the rows in the queries.
Is there anyway I can add a sort of count field to my query, for example what I require is the result of my query to include a column which counts the rows in the queries.
I have a query that captures date values from a table based on a between function that works fine. What I would like to do is associate a sequence number fro each date value returned and either perform a make table or leave it as a select query. If I create a table I know that I aam able to add an autonumber field but the query is executed frequently and performing the addition of the autonumber field is not feesible. Any thoughts how I can generate the sequence number each time the query is executed?
Hi folks I have what I reckon is a complex problem that I need to solve for my warehouse.
We hold replacement parts for the machines we sell. Some parts can be subsititued for others, so where we possible we use these parts to simplify our warehousing. Every day we get a file with demand for each part in and we look to see where we could move this demand to for those parts with a common alternative. When we find one we remove the demand from that part in the database and add it to the demand for the common part.
In operational terms we upload the demand file (.xls) to a table of the following structure Date_added - Date Part_number - Text QTY - number
Thus the table holds daily demand by part. What I would like to do is use a update query to: a) Identify the parts that have common parts to which they can be moved. b) Add that demand to that of the common part today. c) remove it from the original part (set to zero).
Idenitfying the parts and restricting to todays date is not a problem, however the additions and deletions are.
Help...I am new to Access and am trying to get a query completed to run a query on contract end dates. My goal is to be able to run the query on a date such as 10/2007 and have it pull all contract end dates that would term for the month of October, 2007. I have been pulling my hair out trying to build expressions and sifting through my access books to no avail.
I have created a form from a query. I have then at a later date added a new field to the query. I have returned to the form and attempted to add it to the form but it doesn't show the new field within the control source options. Does anyone know why? Do I really have to start all over again with my form?
I have a field in a query that shows amount of hours a site is covered. This field name is Coverage hours and it changes depending on the number of work days in a month. This part is working correctly but some times additional hours may be requested by the customer.
I want to add a another field to this query that will prompt me if their were any additional covered hours. Then I would have the ability to enter a value and the shown output for this field would be the value entered plus the standard coverage hours in the other field.
I have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.Here is the query:
Code: SELECT * FROM Duplicates_Rule1 WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp GROUP BY [BSEG_DocumentNo] HAVING Count(*)=1 )) AND (Duplicates_Rule1.Check) Is Null) and (Duplicates_Rule1.BSAK_Text <> "FREIGHT") ORDER BY Duplicates_Rule1.BSEG_DocumentNo;
