General :: How To Combine 3 Field Into 1 In Query
Nov 20, 2014
how to combine 3 field into 1. For instance. I have a customer table with company name, branch, floor, department. then i am gonna create a query called customerextended and combine all these 3 field so that I can select them in the combobox.
I have a sample here but I just dont know how to edit it...
"Company name: IIf(IsNull([department]),IIf(IsNull([companyname]),[floor],[companyname]),IIf(IsNull([companyname]),[department],[companyname] & " " & [department]))"
View Replies
ADVERTISEMENT
Jul 9, 2014
Using Windows 7,
Access 2010.
Is there a way to combine four fields into one field in the same record? I am trying to build Equipment Code field by using the abbreviated values of Category, Item, Type and Dimension fields.
Example: if Category=Storage System (SS), Item=Wire Deck (WD), Type=Flared (F) Dim=42x52 are the user selected values the corresponding equipment code field would be: SS-WD-F-42x51 This is done in order for all users of the database to build a proper code in the correct format.
What I have so far: I have the four fields and the equipment code field setup in a form, the equipment code text box has a control source of: =[Category] & "-" & [Item] & "-" & [Type] But when I select values of the four fields and the equipment code is populated I try to save the record and I get an error stating that the Equipment code field needs to be filled out. Three fields (Category, Item, and Type) are look-ups from other tables and Dimension is typed in.
View 14 Replies
View Related
May 10, 2013
I'm trying to set the control source for a control on my report that describes the number of portions that goes into a box. Sometimes there is a specific number of portions per box and sometimes it's a range. When the number of units is specific, then it is entered into the MasterCaseMinUnits Field. If there is a range of units possible than the min is entered into MasterCaseMinUnits and the max is entered into the MasterCaseMaxUnits.
I want my report to look at the mastercasemaxunits, if it is blank it will only display what is in the MasterCaseMinUnits field. If there is a value in the masterCasemaxUnits field, then it will display the masterCaseMinUnits & " - " & MasterCaseMaxUnits. this is the code I'm Using:
Code:
=IIf(Nz([MasterCasemaxUnits],[MasterCaseMinUnits])=[MasterCaseMinUnits],[MasterCaseMinUnits],[MasterCaseMinUnits] & " - " & [MasterCaseMaxUnits])
When I run my report, the control displays: #Type!
View 2 Replies
View Related
Jul 20, 2012
So I have two tables. One table "tbl_SRVASSET" lists out servername, serialnumber and asset tag. Second table "tbl_SRVNIC" list out the same servername, IPAddress and IPAddressTypeDesc.
The issue is that I have records in the "tbl_SRVNIC" with the same server listed numerous times (because it has 2 or 3 NIC's). So there is obviously a one to many relationship betweeen the two tables (common I guess). What I'm trying to do is be able to take the servers in the "tbl_SRVNIC" table and a new table or create a query with a single record for that server and have all 2, 3, 4 or maybe more IPAddress's assigned to them listed in the same record. This is what I'm having trouble with...
The end result is that after I've got this new table that lists only a single server and it's associated NIC's in the same record I can then combine that data with the "tbl_SRVASSET" via a basic query so my end result is a brand new table "tbl_SRVDETAIL" that looks like this:
SERVERNAME, SERIALNUBMER, ASSET, SERVER NIC1 IPADDRESS, SERVER NIC1 IPADDRESSTYPE DESC, SERVER NIC2 IPADDRESS, SERVER NIC2 IPADDRESSTYPE DESC, SERVER NIC3 IPADDRESS, SERVER NIC3 IPADDRESSTYPE DESC, (and maybe more columns if there are more, but that is a variable as some servers have 2 NIC's, Some with 3 or more so...).
View 1 Replies
View Related
Jul 20, 2012
I'm a new Access user. I'm looking for an easiest solution to combine more records into one, using comma delimited. Let's say that I have a table T1 with two columns Code and Client like this:
Code Client
X C1
X C2
Y C1
Y C3
Z C1
Z C2
Z C4
Z C5
I need to see those records in a Query, grouped by "Code" with the Client's value combined like this:
Code New
X C1, C2
Y C1, C3
Z C2, C4, C5
View 3 Replies
View Related
Oct 18, 2012
How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have worked. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I upload new data from excel. How would I after I add the new data combine all tables to one super table?
View 7 Replies
View Related
Nov 7, 2006
Let's say I have two tables:
Product.
Item Description
A It's Red
B It's Blue
C It's Green
D You'll love it
Category.
Item Category_name
A Cat1
A Cat2
A Cat3
B Cat1
B Cat4
C Cat1
C Cat6
I want to create a query/table that takes all the possible values for Category.Item and mix them into one field seperated by say a comma, so it would look like this:
ItemCategoryConsolodation.
Item Desc Categories
A It's Red Cat1,Cat2,Cat3
B It's Blue Cat1,Cat4
...
How does one do that?!
What's it called when you try to do this so I can Google it?
I've looked through like 8 Access books at the book store and none of them address anything like this. Can someone recommend a book that would cover "weird" stuff like this?
View 3 Replies
View Related
Feb 25, 2013
I have 2 gig of order data which has duplicates due to different invoice dates. How can I combine the dates into one cell so I only had one row of Order data.
i.e. order number, price, order date, invoice date
same order number, same price, same order date, different invoice date.
How do I combine the second line with the first so that I only have the data once.
View 10 Replies
View Related
Apr 10, 2007
Hi all,Is there a Way to Combine Different Values in a field as a ONE value field?Any suggestions are appreciated.Thanks in advance.
View 8 Replies
View Related
Mar 28, 2008
Hello,
I am trying to find a way to combine multiple rows into one (field). I have 2 tables. One table is data the other table is notes. The notes table has a new record created for each new note. Both tables have the Account Number and and Sub Account Number. I am linking the two tables on these account numbers with a left join normally. There can be records in the data table and no records in the notes table. I guess what I want is a query or sql statement i can use in vba that will display all the data from the data table and all the notes from the notes table in on field.
This data is used in a large report. Right now I have the data table set to the report by itself and code on the report to pull the notes from the notes table using a ADO recordset with a do while that just adds each note to a single variable. This works fine and does the job. The only problem is the time it takes to run the report now. If the report has 1000 records with notes on 90% of them and each of those has multiple notes that recordset code has to run thu ALOT. I use to have all the notes just compile to one field in the data table and the report ran very fast that way. I had to change this since I need it this way as to filter notes by time and archive them after a period.
I have read thru so many posts and see lots of code functions to do this but its just the same thing I already have with the recordset code building the combined field. Any other way to do what I am trying? I think I have just been over thinking this stuff at this point an maybe a fresh set of eyes will help. Below are some simple table layouts for the 2.
**Account Number and Sub ID fields in both tables are not unquie.
Data Table: (this table has alot more data but this gives you the idea.)
Account_NUM - Sub_ID - Data1
1234 | 3 | 123 N Inc.
1234 | 3 | 666 DRN
4567 | 4 | 543 S Way.
7890 | 5 | zzz ABC
Notes Table: (Yes the notes field is a MEMO field)
Account_Num - Sub_ID - Notes
1234 | 3 | notes1
1234 | 3 | notes2
1234 | 3 | notes3
The output I am trying to Get:
Account_Num - Sub_ID - Data1 - Notes
1234 | 3 | 123 N Inc. | notes1 notes2 notes3
1234 | 3 | 666 DRN | notes1 notes2 notes3
4567 | 4 | 543 S Way. | NULL
7890 | 5 | zzz ABC | NULL
Thank you for any advice or thoughts.
Joe
View 2 Replies
View Related
Aug 20, 2004
Hi,
I am trying to combine 2 text fields into a new field (concatenate) so that I can use it as the primary field.
Can anyone pls help.
View 3 Replies
View Related
Jun 18, 2006
Hey all,
I'm wondering if creating something like this is too difficult, or where I'd start? (see image attached). Here's kind of the process:
Scenario 1 :
1) user selects value from combobox
2) clicks text link ("add") to add value to table
3) value is displayed on form, with "remove" link that can take previously written data out of table
4) combo refreshes, moves down and process can repeat
The values would be added to the table seperated by commas.
Scenario 2:
This might be too difficult, in which case I could just use four or so text inputs and the user could type in the values... all written to different table cells. In this case, I'd need to show be able to write all the cells to a single textbox string on a form in the format "cast member 1, cast member 2" etc.
Anyone know what I'm trying to do and that could lend some advice? If there's an easier way to do this, please let me know. haha, thank yoU!
View 10 Replies
View Related
May 10, 2013
I am having trouble creating a specifically formatted report.
Anyway, I have two fields: [Title] (plain text) and [Description] (memo). I want to combine the two but have the Title bold underlined but not the Description. I want it to look like this:
This is the Title. And this is the decription part that could go on for many, many more lines...
I have tried combining the two fields like: =[Title] & [Description] but if I set the properly to underline, then the whole thing is underlined.
Since the Title will vary in length, I cannot just underline the Title and then put the Description field next to it.
View 6 Replies
View Related
Aug 13, 2013
I have a problem when I try to combine in a text box a text and value from field in a table.
I would like to have a text like this:
Ref nr: 1
"Ref nr" would be a text in a report
"1" would be the nr of the ID of the client.
I tried:
="Ref nr " & [ID]
It simply does not work. No clue where I go wrong.
View 1 Replies
View Related
Sep 6, 2013
I'm making an accounts package.
I've got a query that shows all individual invoices with their total amounts. This works.
But now I'm trying to make graphs so I want show just total amounts combined by customer, for each customer.
So for example if I had 8 customers and they each had 10 invoices each my current query would be returning 80 records.
What I want is 8 records, once for each customer with the summed total for each customer so that I can then graph this.
View 3 Replies
View Related
Oct 8, 2007
I run queries against an SQL database where I have read only access. This database has the following fields
EventNumber
BriefDescription
DetailDescription
EventCode
CauseCode
SignificanceCode
Resolution
When users enter one of the 3 "Codes", they can enter as many as are appropriate. So an event may have 3 EventCodes, 2 CauseCodes and 2 SignificanceCodes for example.
When I run my query, I get a different record for each. So for the above scenario, I may get 12 records. Problem is I only want one record for this event. Using a query, how can I combine all the EventCodes together and the CauseCodes together and the SignificanceCodes together...maybe separated by a space or comma? If I have to copy the data down locally, that's okay. I am wondering if an Update query could be used somehow, but I am not sure how to do this.
Any help is appreciated.
Jim
View 10 Replies
View Related
Nov 4, 2007
I'd appreciate any ideas on how to combine the following code into 1 query, without "If ... Then ... Else":
If Now() >= TransitionStartDate And Now() <= TransitionEndDate Then
DoCmd.RunSQL "SELECT [Contractors Trained Query].LastName, [Contractors Trained Query].FirstName, [Contractors Trained Query].CompanyName FROM [Contractors Trained Query] WHERE [Employees Trained List].[This Years Training Date] Is Not Null OR [Employees Trained List].[Last Years Training Date] Is Not Null ORDER BY [Contractors Trained Query].LastName;”
Else
DoCmd.RunSQL "SELECT [Contractors Trained Query].LastName, [Contractors Trained Query].FirstName, [Contractors Trained Query].CompanyName FROM [Contractors Trained Query] WHERE [Employees Trained List].[This Years Training Date] Is Not Null ORDER BY [Contractors Trained Query].LastName;”
End If
Thanks ....
View 3 Replies
View Related
Apr 5, 2013
First query = Sum Products:
Code:
SELECT Sum(Tab1.Inputs) AS SumOfInputs, Sum(Tab1.ValInp) AS SumOfValInp, Sum(Tab1.Outputs) AS SumOfOutputs, Sum(Tab1.ValOut) AS SumOfValOut, Products.Product, Products.VAT, Products.UM
FROM Tab1 INNER JOIN Produse ON Tab1.ProductID = Products.ProductID
GROUP BY Products.Product, Products.VAT, Product.UM, Tab1.ProductID;
Second query :
Code:
SELECT Nz([SumOfInputs],0)-Nz([SumOfOutputs],0) AS Stoc, Nz([SumOfValInp],0)-Nz([SumOfValOut],0) AS ValStoc, IIf([Stoc]=0,0,([ValStoc]/[Stoc])) AS CMP, [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
FROM [Sum Products]
GROUP BY [Sum Products].Product, [Sum Products].SumOfInputs, [Sum Products].SumOfOutputs, [Sum Products].SumOfValInp, [Sum Products].SumOfValOut, [Sum Products].VAT, [Sum Products].UM
HAVING (((Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))>0.09 Or (Nz([SumOfInputs],0)-Nz([SumOfOutputs],0))<-0.09));
I need to combine those two query sql code to make only one query.
View 3 Replies
View Related
Jul 24, 2005
I am using the two lines of code below Each in separate columns of a query (or two text Boxes on a form) to calculate the elapsed time between two dates formatted as General Dates. The first line of code (in column A, "txtTime1") calculates to the total minutes between the two dates, the second line of code (in column B, "txtTime2") takes the minutes and converts them to total elapsed hours and minutes. They work great, however, I would like to combine this code and place it in only one query column or form Text Box. My VBA is not up to it, can someone out there show me how to combine the code below to produce the hours and minutes in one line of code? Thanks
=DateDiff("n",[StartDate],[CompleteDate])
=[txtTime1]60 & Format([txtTime1] Mod 60,":00") :confused:
View 2 Replies
View Related
Aug 8, 2005
Hi friend
I need your help.
I have made 2 query (named "QryReceivesum" & "QryWithdrawsum").
But query have the same common column called "ProductId".
I want to combine 2 query to be just one.
Currently I have mae one query named "QryStock".
but the data from this qry is not correct.
As you can see, it doesn't show the number of product from productID3.
can anyone help me??
View 8 Replies
View Related
Apr 28, 2006
Hi,
I am trying to create a general date field by combinging a date (short date) and time (medium time) field. The fields are named recDate and recTime for the two I am combining, and cmbDateTime for the result field.
When I run the query, the result I receive is an errror stating that it is a data type mismatch.
What am I missing?
Thanks in advance,
Jake
View 4 Replies
View Related
Nov 9, 2013
how to combine 3 cross tab queries values into one daily progress report, transpose format. The values from each crosstab query are
1) Spent hours
2) Qty installed
3) Earned hours.
View 4 Replies
View Related
Jun 3, 2015
I have three queries that make a training list based on a person's role, team, and ad-hoc exceptions. The personnelID field is filtered by a listbox on a form. Each of these work great on their own (nice!). Now I want to combine them. I made the below union query that works... however when it is run, I am prompted to enter the personnelID again. This prompt only happens once. Do I need to incorporate a qhere statement somewhere, even though each of the individual queries have them already?
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByRole
UNION ALL
SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByTeam
UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
FROM qryPersonnelTrainingByAdHocPersonnel;
The where statement of potential use:
Code:
WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));
View 6 Replies
View Related
Jul 10, 2006
Can anyone help me with how I can accomplish this?
Here is what I currently have:
10 SQL Pass-through queries to update different Date field (Date1, Date2, Date3, etc.) based on the value of the next date field.
This is how each individual query is set up (there are 10 in total)
UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
Here is what I would like to have: (as 1 query)
UPDATE tblApplications SET tblApplications.LastContact = [date1]
WHERE (((tblApplications.Date2) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date2]
WHERE (((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Null) AND ((tblApplications.[Date Completed]) Is Null));
UPDATE tblApplications SET tblApplications.LastContact = [date3]
WHERE (((tblApplications.[Date Completed]) Is Null) AND ((tblApplications.Date1) Is Not Null) AND ((tblApplications.Date2) Is Not Null) AND ((tblApplications.Date3) Is Not Null) AND ((tblApplications.Date4) Is Null));
Can I somehow combine them together like this or do I have to keep them as 10 seperate queries?
View 5 Replies
View Related
Oct 3, 2007
Hey guys-
I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')-
xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this-
Field1 Field2 Field3
xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow...
Thanks!
View 6 Replies
View Related
Feb 27, 2008
I was wondering if there is a way to combine sales by month for a year where it would show the product then for say January and the total sales and so on for each month.
View 7 Replies
View Related
