I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.
When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.
I'm creating a query which pulls together the numerical values of 32 separate fields. Each field has the potential to have a value in it ranging between 1 and 9, but most fields will be blank.
I want to prefix the value (if the value is not null) with a 2 or 3 character-long code relating to the field name.I then want to combine them all together in a query so I can easily copy and paste the output to a spreadsheet.Here is what I have so far, with just 9 of the field names, and it's already looking a bit clunky.
Code:
IIf(IsNull([zam_adm]),"","ADM") & [zam_adm] & " " &
IIf(IsNull([zam_cts]),"","CTS") & [zam_cts] & " " &
IIf(IsNull([zam_crs]),"","CRS") & [zam_crs]& " " &
IIf(IsNull([zam_IPB]),"","IPB") & [zam_IPB]& " " &
IIf(IsNull([zam_opr]),"","OPR") & [zam_opr]& " " &
IIf(IsNull([zam_owl]),"","OWL") & [zam_owl]& " " &
IIf(IsNull([zam_pmi]),"","PMI") & [zam_pmi]& " " &
IIf(IsNull([zam_wl]),"","WL") & [zam_wl]& " " &
IIf(IsNull([zam_sgt]),"","SGT") & [zam_sgt]
The output, unfortunately, is putting spaces between empty fields and I'm not sure how to prevent this in the syntax?
I need to create a query that combines two columns (lets say Column A and Column B for example) however the problem is that whatever non-null values that are in Column B must replace any value in Column A. If Column B has a value that is null then Column A's value is shown.
I have an example of what I'm working with (access file) and what result I want (excel file).
I have two queries:
1. a sum of payments by individuals
2. a sum of charges of individuals.
I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).
I have a form (DropDown form) that has 3 drop down fields, you select your values from the drop downs and you would push a command button that runs an event procedure which runs a query (DropDown qry test). The user should have the option of picking any combination of fields to filter by. Or no combination, which would return all values in all fields. So I am basically using the form as parameter's for the query.
The problem I'm having is that my query is returning values for one field AND values for another field. Even if the other values selected are not in the same record. It's not combining the fields together to filter. For example: you pick a Project name and Supplier name, the query will return records that have the project name you selected but it will also return records with the supplier name you selected that have a different project name.
I've attached screen shots of the form and the design view of the query (the screen shot cut off the last column name. It is meant to say "Expr3: [Forms]![DropDown form]![Combo7]").
Using Windows 7,
Access 2010
Is there a way to select multiple values from the drop downs?
I have a query that performs some calculations, these calculations feed into another query and produce a final value. If there are no null values everything works perfectly. But when there are null values, I get errors.
What I have tried:
1) I tried to implement the Nz function for each field of the formula that is not calculated but from user input.
2) I tried to implement the Nz function for just the field that calculated the last result before feeding into the next query, but it didn't show the 0 value I gave.
3) I tried to use UPDATE TABLE SET in SQL, but I don't think I was using it right. I tried to use it for all records but always got an error when it ran.
The first two above result in showing no values at all, even if there are some. The third didn't work. How to easily update null values in the query to 0? All I could think of was to somehow use Criteria or SQL.
I am pretty new to access and struggling with a query problem. I currently have a table containing data like:
Name_____________Color Chosen
Bill..............................Red
Bill..............................Blue
Tom............................Red
Mark...........................Green
Bill..............................Green
Tom............................Blue
And I would like to be able to get to
Name______________Colors Chosen
Bill..............................Red, Blue, Green
Tom............................Red, Blue
Mark...........................Green
Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.
Many thanks in advance
Chris
Hi! I have a problem combining values of comboboxes, let me explain me better...
I have a text box and two comboboxes on a form
First, you introduce a data on the text box, then you select something of one of the comboboxes
the third combo box have to have the value of the text box and the value of the combo box separated by a comma or a space. Please help me with this. I need to finish this database soon:confused:
I basically have two yes/no fields in an access table. I want field one to have the value yes if field two has value no, and the other way around.
I had a look at the Boolean operators, but im not sure where to go from there.
I'm trying to create a text box on a form which displays a value based on numerous criteria.
Basically I manage a database of employees. Some are external employees, some are internal. Some have left the organisation, and some are still employees.
I have two fields which need to be pulled into this expression:
is_leaver - text box, with value either "Yes" or NULL
organisation - name of organisation (text)... lets say my organisation is called Happy People Ltd
The text box I have is called employee_status
I can see that there are 4 possible combinations:
1. Internal Employee - Is an internal employee and still works here
2. Internal Ex-Employee - Is an internal employee but has left
3. External Employee - Is an external employee who still works here
4. External Ex-Employee - Is an external employee who has left
So far I've managed this:
Code:
=IIf(IsNull([is_leaver]),"Employee","Ex-Employee")
This displays whether they're an employee or ex-employee. How would I form the expression to combine the two fields, and calcuate whether they're internal or external?
Due to the way the form is set up I don't want to have two separate text boxes and would prefer to combine it.
I would like to make a report to show how many employees and which employees are attending to which colleges/universities.
In my data (800 records), I have two fields which is "College 1" and "College 2" for each employee.
There are values that are enter in college 1 for some employees, other employees have the same value that is enter in in college 2. How do I get to show a report that has all the employees who attended the same college in either college 1 or 2?
For instance, this is my raw data:
Name - College 1 - College 2
Bob - University of HI - Honolulu CC
Sandy - Honolulu CC - University of HI
Clare - Kapiolani CC - University of HI
John - University of HI - Windardward CC
I want my report to show:
Colleges -
University of HI
Bob
Sandy
Clare
John
Honolulu CC
Bob
Sandy
Kapiolani CC
Clare
Windard CC
John
I am working with an Excel file of raw data aggregated from an annual customer relationship survey that has been sent out since 2010. The file has approximately 20,000 rows, meaning the survey has been taken around 20,000 times. Unfortunately, each time a customer takes the survey, it is included in the raw data as a separate entry. Therefore the file has numerous duplicate email addresses, corresponding with unique data for each time the survey was taken. Another issue regarding the data, is that in the first year the survey was sent out, the distribution mechanism "broke" and the survey was sent out multiple times (and completed multiple times) in the same year by the same customer, so the surveys are not necessarily uniformly distributed, if that makes sense.
I have been interested in isolating the common respondents (those who have taken the survey across multiple years, albeit not necessarily consecutive years). Up to this point, these respondents have been isolated manually using a pivot table, however I am now looking to enter the raw data into SPSS (a statistical analysis package), in order to view the drivers of these common respondents.
Therefore, I would like to be able to isolate these common respondents and the data corresponding with their surveys from the raw data in a separate worksheet or file. I have tried various formulas to do so in Excel to no avail. Is there anyway to accomplish this in Access or would a more complex database be needed?
Ideally the final product would have the common respondents' information from multiple surveys in one row and would be able to be filtered by feedback date (found in the raw data), so that hypothetically one could select a month and be able to tell how many customers considered to be common respondents completed a survey within that month.
I have two text boxes, and i want to make there value be the label for the form. Here is what i have in the Label on the form:
=Nz([Last Name] And [First Game],"Untitled")
Where am i going wrong?
I'm trying to create my own database dealing with hiring bikes on a daily basis.
I have created a form for the basic input, including StartDate, StartTime, EndDate and EndTime, followed by TotalDays. I added the time field so that if a client brings a bike back an hour or two after the pickup time on a later date (sounds complicated - e.g he takes it on Monday at 12pm until Thursday 14pm) then he would be charged for an extra day. It is similar to the car rental system used worldwide.
The TotalDays is currently using a simple expression =EndDate-StartDate.
Is there any way to make the form incorporate the time difference so that if EndTime-StartTime>1 then it would add an extra day to the "=EndDate-StartDate" calculation.
I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?
QUERY1
Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT
[code]...
I have two queries, one base upon the other. I would like to combine them (If Possible) into one query so I can embed them into a form or report. I have tried without success at finding the answer on the forum as well as searching the web.
The table lists employee numbers and dates they worked. I need a count of how many employees worked each year, based upon the paycheck date, not the actual date worked. Pay check dates are two weeks apart. An extreme example, is the first pay check date of 2010 was on 1/1/2010, but all the days worked were in 2009, this would have to be included in 2010 not 2009(See the query for further date calculation understanding). Anyway, the date calculations are not the issue here.
I only have one table, so if I am not mistaken, I can't use the WHERE (SELECT... JOIN) feature. I also was unsuccessful at using SQL DISTINCT.
I am running ACCESS 2010 Tables are ACCESS 2007.
OS is Windows 7 Ultimate.
I have included a same database with the queries. qryEmployeesAnually2 is the results I am trying to achieve.
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
hi I am making a late report of each employee's data and I calculated for each individual day by having late for how long, but I would like to make another column which displays the total amount of time late. This is currently what I have now:
http://i8.photobucket.com/albums/a45/janue/num1.jpg
My SQl looks like this:
SELECT username, DateValue(clocktime) AS Date_Clocked, Min(TimeValue(clocktime)) AS Clock_In_Time, ((Clock_In_Time-#9:00:00 AM#)*24) AS Late_By_inHours
FROM tablename
GROUP BY username, DateValue(clocktime)
HAVING Min(TimeValue(clocktime))>#9:00:00#;
clocktime is a date/time field, in reality it is regardless whether it is clock in or clock out time. And now I want my table to look smth like this:
http://i8.photobucket.com/albums/a45/janue/num2.jpg
Has anyone knows how to do smth like this? I tried using the SUM() but it states error stating it cant take the expression ((Clock_In_Time-#9:00:00 AM#)*24) smth lidat
I have been reading previous post s regarding the storing of calculated fields. Basically everybody says don't do it. However, if I don't I am not sure how to achieve what I need to achieve.
I am storing data on tool vibration levels. Data is input for X, Y and Z axes and a vector sum (total) is calculated on the fly using a query. So I only ever store X,Y and Z.
Ultimately I am looking for this database to be able to give me useful statistical outputs in particular a range of vector sums for a tool type. If I don't actually store the vector sum for each test then how I am ever going to report a range? The data would never change for each test as these become statictics, it's not the same as working out rates for example which may vary according to parameters.
What is the best way to do this?
Im trying to join two queries as I am unable to use just a single query but I cant use a union query as the query fields aren't exactly the same.
Both queries have a PO_Detail field as every PO has a PO_Detail number assigned to it. 3 of the same records are in both query results but one query is missing the other two results.
If I create a join between the two queries based on this field I don't get all the results. Unfortunately I cant upload the database as it has sensitive data which would take me ages to clear out but I can show a picture of the results.
I trying to combine three columns that I have into one column without combining fields.
Currently what I have:
(see image below)
What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc
The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this.
I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.
attached is my table and Input Screen.
I have a textbox in a report that I would like to calculate the sum of values that are less than 600.Then have a second textbox that counts the number of records that have values less than 600..This is based off of a query with calculated fields..I have been working with a formula that looks something like this
=Sum(IIf([BaleETime]="<600",0))
But all I get is a value of 0.BaleETime is the elapsed time between bales.
hi
i want to comibine some queries into one table
the first quierie contains like 6 records and the second one contains 3 records
the problem is is that when combining these columns into the one query the second contains more then 3 records when it should be only displaying data for the 3 bits ?
Thank you in advance for your assistance.
I've read through the other threads but can't seem to find anything that is simple (not an experienced SQLer so can't really tweak too much) and that would work for my problem.
I have a Project table linked to an Invoice Table (1 to many respectively)
The Invoice table linked to a Transaction table (1 to many respectively). 1 invoice can be paid out to many different cost centers and amounts.
I also have a Reclass table that is linked to the Project table.
Basically Invoices can be processed to multiple cost centers originally. But can also be reclassed at a later date to other cost centers.
The way the database is setup is so that if there are 10 invoices each having multiple records in the Transaction table (charging 1 invoice to multiple cost centers) AND we need to reclass them. We don't want to reclass each an every invoice and transaction record. It setup so that we can take the total invoiced amount for the project (sumed up all the invoices for that project) and reclass one total amount.
The problem is now I have 2 separate "financial" tables. One for the original invoice payment transaction (Transaction table which has the original cost centers and amounts) and a Reclass table with the "new" cost centers and amounts.
I need to combine them so that the financial reporting will reflect the reclasses (moving dollars from one cost center to another).
I tried creating a separate table (All Financials Table) that I can append the "original" Transaction table and Reclass table to use for reporting. I have this working but I can forsee possible problems going forward (i.e. user may append multiple times by a button click).
Is there a simple way to do a combined query on both the Transaction and Reclass table. Both queries have the following: Project ID, Cost Center and Amount. The problem I'm having is that if one table has 10 records of say cost center 123456 and the other has 3 records of 123456. The query ends up giving me 30 records.
I know my explanation is long but I think it will help paint the bigger picture.
Thanks again for reading this!
I have a query called Spans1 that calculates the difference between dates:
SELECT [Rasp].[Issue Number], [Rasp].Issues, [Rasp].Status, [Rasp].Date_Received, [Rasp].Date_Resolved, DateDiff("d",[Date_Received],[Date_Resolved]) AS Span
FROM [Rasp]
WHERE ((([Rasp].Status) Like "*" & [Enter status]));
I have another query called Average_Span that gives me an overall average number of days between the dates in the above query:
SELECT Format(Avg([Span]),"0.0") AS Expr2
FROM Spans1;
Is there a way to combine the 2 so that I get the spans and an overall average? Any help is greatly appreciated.