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 am a beginner in SQL, I have a problem regarding combining rows/ columns having value. Below is my example:
Table1:
1 123 23 43
1 234 3 431
2 34 34 34
2 54 3 123
3 1 32 32
2 341 1 32
Output Table:
1 123 23 43 234 3 431
2 34 34 34 54 3 123 341 1 32
3 1 32 32
or instead of combining rows, getting the result by combining columns with the same condition.How can I obtain such result?
I'm trying to combine 4 columns. Some of the columns have leading 0's and when I run my query I loose the leading 0's.
BATCH SOURCE NO SEQLINE
11012011 P 004800525
SELECT [BATCH] & "" & [SOURCE] & "" & [NO] & "" & [SEQ] AS RECEIPT, [7ef].LINE
FROM 7ef;
screen shot attached
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 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 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?
Attaching a sample mdb file. I have TABLE1, TABLE2 and QUERY1, QUERY2 in my mdb file.
I want Query1 Value 4 once in Query2. It should not be repeated again with [SBNO] values 5 time.
Value 4 in Query 2 shows with only 1st record and remaing 5 records shows 0 value instead of 4.
How is it possible.? Can anyone help me.
Hi
I am trying to replace the numeric values of a field with a text in querry using Choose function, something like this aa5:
Choose([a5]-1="one";[a5]-2="two";[a5]-3="three";[a5]-4="four")
But it is not giving me no results :(( I am missing something but I do not what. Maybe I should use some other function?
I hope someone will help me!
Hi,
i have created a query from just one table, which is basically a list of Financial transactions.
There is a field called supplier code, in this query.
Most of the transactions have a supplier code, but there are a few that don't.
i've got another table that has the supplier name against the supplier code.
i want to bring this table into the query, so that i can show the supplier names against those records in the query that have a supplier code.
when i add this table and link the 2 tables up and put the supplier name into the query. The query excludes the records with no supplier code.
This is presumably, because they contain null values.
I've been reading about the Nz function and i think this is what i need to use.
i've tried this, but it still only brings through the records with a supplier code and not all of them.
How i've done this is to go into the design mode of the query and in the column that would have null values i have click "build"
in here i have the following formula.
Nz([Supplier Name],"No")
Am i doing anything wrong?
Is it possible to create the following logic in query:
"If there is a "SHIFT2" or "SHIFT3" value on the last Friday in month,
then change the date values for these entries to the next date (Saturday)"
In the attached sample, there is a number of such values within the "Adv Track Shift" field dated on Friday 04/25/08 in the "shift_date" field. The logic should replace the applicable dates to Saturday 04/26/08. This should work for any month, regardless if it finds either of the two shifts or both of them.
Hello,
I am trying to combine 2 columns from 2 different tables. Table names are Center and TeachingSite. These tables are stored in an Oracle database, so i can't really change the table structure. I need to combine the Center.Name and TeachingSite.Name field into a list box on a form. This way, everything is listed into one list box instead of a list box for Center.Name and 1 for TeachingSite.Name. Is there a way to create a SQL statement or anything else that would work that will combine these into one listbox and be able to select any item from the list to show related data on subforms and so on. Thanks.
Jared
I have a table that has two columns "Nationality" and "Gender". I wanted to run a query that will Group the nationality and then split the gender column into two columns and after that it will count both gender columns for each nationality. When I posted this question in "Reports" section I got the suggestion to use the SELECT COUNT in SQL. It worked but only for nationality. I couldn't get it work for the Gender column. I searched alot and the only thing I got was the SQL function to split data from one column into two but that also didn't serve the purpose (check the link to see why: [URL]) At last, I went on to create a cross tab query. Selected Nationality as Row header, Gender as Column header and Customer ID as calculation point. And there I got the result I needed. The SQL Code looks like this:
Code:
TRANSFORM Count(Register.[Customer ID]) AS [CountOfCustomer ID]
SELECT Register.National, Count(Register.[Customer ID]) AS [Total Of Customer ID]
FROM Register
GROUP BY Register.National
PIVOT Register.P_Gender;
Crosstab query did the trick..
Hi,
I want to retrieve data from 2 columns using only 1 search option. At the moment I have 2 ID fields which contain various numbers. My data retrieval functionality only retrieves data on either one column or the other using 2 search buttons. So for example, if they want to retrieve data on ID column1 they have to click the button named this and if they want to retrieve data on ID column2 they have another button for this. What I want to do is when the user clicks the button I want access to prompt them for an ID number (only want 1 prompt). Once the ID number is input I want access to search both columns for the ID number and return it. Any ideas on how this can be achieved plz?
Thanks
I'm trying to make a payment report which prints a separate pay sheet for each employee based on what job they did. I don't want to get into too much detail here, but basically, a name is associated with each action in a setup like this:
Job1......Job2......Job3......Job4......Job5
Joe.......Bob.......Jim.......Fred......Jeff
Joe.......Joe.......Jeff......Jim.......Jeff
Bob.......Bob.......Jeff......Bob.......Jim
and so on and so forth.
I want to have a query that gives one column that looks like this:
Bob
Fred
Jeff
Jim
Joe
I already know how to sort and group by, but how would I write a query that contains one column, then another column when that one ends, then another, et cetera?
(I need to know this, because it would be a lot cleaner to show one report for all 5 jobs than a report for each job separately, and of course, that's how the boss wants it!)
I have a table in Access with 184,000 rows or records of data and 20 columns or field names. When looking at the table ten of the columns represent the identifying information for the data stored in the other ten columns. I need to convert the last ten columns of data into one column with a second column added to identify the original field name. Then end result would be one current record of data being converted into ten records with the original 10 columns of identifying data, a new column containing the original field name and a column for the data value from the original column identified in the new column.
I have been able to do this in Excel using a consolidation pivot table, un-checking the rows and columns then clicking on the final sum value to get a detailed list of the records. Unfortunately there still appears to be a cap because I get a message saying some of the data has been dropped after a little over a million rows.
I am using the 2010 version of Access and Excel.This is the smaller table. I have several others that have up to 200 columns that need to be converted the same way.
My Approach database contains records relating to nearly 800 sites in London. There are radically different amounts of data held about each site. The database contains a lot of different tables, each containing a different class of information. Not all the sites listed in the database have information in all of the tables.
The unique thing that holds it all together is the unique site reference number. Unfortunately this is split into two separate columns in the Approach database, "Reference" and "Suffix". Not all sites have a suffix. (The purpose of the suffix is to identify sub-sites which are subordinate to the main site reference, but need to have their own individual records.) Because many sites have no suffix, most of the fields in the second column are blank.
In order to link all the tables together in access I need a Primary Key which is unique to each site. In this case the reference/suffix number is the obvious (only) candidate. There is no problem using two different columns to create a primary key. The problem I face is that it doesn't like the fact many of the fields in the second column are blank.
My solution to this is to combine the two columns into one. That would give every site a unique reference, and none of the fields in the combined column would be blank. Can I work out how to do it? All I want is a new column that displays the reference and suffix (if any) in a single field, no spaces.
I'm trying to retrieve information from 3 tables (Order, Receive and Issue) to one table as Inventory Table/Query. it looks like as follows
Order Table:
OrderID
ProductID
OrderDate
OrderQTY
1
1
3/1/2012
30
2
1
4/1/2012
10
3
2
4/1/2012
20
Receive Table:
ReceiveID
ProductID
ReceiveDate
ReceiveQTY
1
1
3/3/2012
30
2
1
4/3/2012
10
3
2
4/2/2012
15
Issue Table:
IssuedID
ProductID
IssuedDate
IssueQTY
1
1
3/5/2012
20
2
1
4/6/2012
10
3
2
4/5/2012
5
I'm looking for Inventory table/query which will give all of the data per date, which is like the following
Inventory:
Date
ProductID
OrderQTY
ReceiveQTY
IssueQTY
3/1/2012
1
30
3/3/2012
1
30
3/5/2012
1
20
4/1/2012
1
10
4/1/2012
2
20
4/2/2012
2
15
4/3/2012
1
10
4/5/2012
2
5
4/6/2012
1
10
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
Current:
ID / Regular Hours / OT Hours
101 / 70 / 7.5
102 / 30 / 0
103 / 5 / 0
Needed:
ID/ Hours / Type
101 / 70 / Reg
101 / 7.5 / OT
102 / 30 / Reg
103 / 5 / Reg
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
Access 2007, Windows 7.
I am having a query, where a few date records are null because of no entry or record, what a want that the field with no date entry to be come up with zero "0" .
View 1 Replies View RelatedI 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 am trying to replace a # character and all characters after in a text field.
U set up a Select query and used the following expression:
Expr1: Replace([Part Number],"[#]*","")
It doesn't change any of the records. Is this possible to do in Access 2007?
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.