Queries :: Combining Fields - Spacing Between Values
Mar 6, 2014
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?
View Replies
ADVERTISEMENT
Aug 21, 2013
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
View 4 Replies
View Related
Aug 24, 2006
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.
View 1 Replies
View Related
Sep 29, 2014
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).
View 2 Replies
View Related
Feb 2, 2015
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).
View 5 Replies
View Related
Dec 2, 2014
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]...
View 2 Replies
View Related
Jul 22, 2014
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?
View 14 Replies
View Related
Jan 21, 2014
I know how to concatenate fields in a query but have not done it with memos before. I have three memo fields and I want each to show up in one field with a bullet in front of each. Memo1 may be empty and memo2 and memo3 may have something or memo 3 may have some text but the others are empty so I need to be able to list the memos without the empty spaces. Is a query the best place to do this or in the report and how do I do it?
View 4 Replies
View Related
Jul 17, 2013
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.
View 14 Replies
View Related
Jul 10, 2013
I have two tables. One table is a list of classes with the number of enrolled students:
Class............StEnrolled
English 1A........6
English 1B........12
English 1C........20
English 1D........25
Reading 1.........4
Reading 2.........15
And the other table is a list of "combined" classes (meaning they are, essentially, the same class and should be counted as such):
Class...........CombinedWith
English 1A.......English 1B
Reading 1........Reading 2
Using the first table, I currently generate a query (and build a report) that displays class enrollment levels. What I need is for this query to identify combined sections from the second table and display them as such:
Class........................StEnrolled
English 1A/English 1B........18
English 1C.......................20
English 1D.......................25
Reading 1/Reading 2..........19
View 7 Replies
View Related
Sep 9, 2005
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
View 5 Replies
View Related
Dec 20, 2006
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:
View 3 Replies
View Related
Feb 11, 2012
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.
View 6 Replies
View Related
Apr 30, 2014
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.
View 5 Replies
View Related
Jun 17, 2013
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.
View 8 Replies
View Related
Jun 12, 2013
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?
View 5 Replies
View Related
Oct 3, 2013
I'm doing a medical db, where I'm trying to calculate ISS
Code:
en.wikipedia.org/wiki/Injury_Severity_Score
There are 6 values, and i have to sum the largest three (squares to be precise). How can I get three highest values from 6 fields in the same record?
View 1 Replies
View Related
Aug 13, 2015
I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:
Field 1; Field 2
Jones; 200
Smith;150
Jones; 300
Smith;100
In this example, I need the sum of field 2 for the records where Jones is the value in field 1.
So the result in this case is that records where Field 1 = Jones, Field 2 = 500.
View 2 Replies
View Related
May 8, 2013
I created a simple calculation query to add the values of three fields:
Program_Cost, Auditorium_Cost and Millage_Fee.
I followed the steps found here: [URL] ....
But it doesn't work. The query pulls the values for the relevant fields but doesn't actually calculate the total. What am I doing wrong? Here's the query's SQL:
SELECT [Event Information].Event_ID, Sum([Program_Cost]+[Millage_Fee]+[Auditorium_Cost]) AS Total_Cost, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee
FROM [Event Information]
GROUP BY [Event Information].Event_ID, [Event Information].Program_Cost, [Event Information].Auditorium_Cost, [Event Information].Millage_Fee;
View 2 Replies
View Related
Jul 8, 2014
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.
View 3 Replies
View Related
Sep 24, 2014
i made a query to display students results after an exam. there are 12 subjects but a student chooses 8 from that. After an exam, the results are keyed in and a result slip is printed later. The problem is that the result slip displays all subjects including those not done by the student. I typed a criteria >0 in one of the fields and no records appeared. Is there a way that i can use to display only the subjects chosen and marks attained by a student and leave out the rest?
View 1 Replies
View Related
Jun 12, 2013
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.
View 14 Replies
View Related
Apr 11, 2013
I'm trying to create a query that can sum values of different fields in different tables...Can I sum values of a field and put the result into another field in different table?
View 3 Replies
View Related
Nov 15, 2014
I have six fields in a record that look like Median.University1, Median.University2, Median.University3, Median. University4, Median.University5,Median.Unive rsity6. How can I create a calculated field that counts where values equals 4 in the fields . I have tried Dcount and Count with no success.
View 7 Replies
View Related
Oct 12, 2007
Hello,
I need to find a way to modify the following structure:
Name, Page, Grid
Acton Rd, G10, 12
Acton Rd, G10, 4
Acton Rd, G10, 8
Adams Ct, F6, 2
Into something like this:
Name Page Grid
Acton Rd, G10, '4, 8, 12'
Adams Ct, F6, '2'
What is the best way to go about tackling this?? I need to create this in a table so that I can export it from Access and into a .dbf to be used with another program.
Thanks,
View 3 Replies
View Related
May 9, 2013
I have a query that pull data from hours worked fields that have values with decimal places. Hours is multiplied to rates to create my amount to charge in my query which is then displayed on my invoice report.
The issue is that when I sum my amount, my subtotal is off by $.01 due to the decimals entered for the hours worked (e.g. hours worked is 1.5*32.75=49.125)
I need the rounding to work in the query and display on my report as $49.13 and the subtotal to reflect it. I have the properties in my table as double with decimal place at 2, and my query and report properties with decimal place at 2 as well. It still isn't working.
View 2 Replies
View Related
