Combining 2 Columns For Retrieval
Jan 11, 2008
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
View Replies
ADVERTISEMENT
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
Feb 7, 2006
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
View 8 Replies
View Related
Sep 4, 2007
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!)
View 3 Replies
View Related
Jun 21, 2012
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.
View 6 Replies
View Related
Aug 16, 2013
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.
View 2 Replies
View Related
May 20, 2013
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?
View 3 Replies
View Related
Apr 18, 2013
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
View 6 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
Aug 17, 2012
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
View 3 Replies
View Related
Mar 29, 2012
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.
View 5 Replies
View Related
Nov 6, 2006
I am not sure whether this is a problem with MS Access, Visual Basic or Windows.
I have taken over supporting & developing an MS Access 2000 DB for a small charity & am not an Access expert or a programmer. The Application includes processing to create, amend & store retrieve standard letters based on a Word document called MyMerge.doc. The operating systems is Windows XP for the PCs with a MS Server 2003.
Each letter is allocated a number ‘CallID’ which is used to retrieve the letters later. The letter text is in MessageC.
The VB code to store the letters (Save As) is
Dim strTest As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
MessageC = "%fa" & MessageE & "Db Letters" & MessageC & " " & (CStr(Forms!Contacts![PostalCode])) & " " & Trim(DLookup("[TitleType]", "Title Types", "[TitleTypeID] = Forms!Contacts![TitleTypeID]") & " " & Forms!Contacts![FirstName] & " " & Forms!Contacts![LastName])
objWord.Application.Activate
SendKeys MessageC
The VB code to retrieve the letters is
Dim MessageE As String, db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then MessageE = Mid(Left(td.Connect, InStrRev(td.Connect, "") - 1), 11)
GoTo jumpout
Next
jumpout:
Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True
WordApp.Application.Activate
MessageE = "%fo" & MessageE & "Db Letters*" & CallID & "*.doc"
SendKeys MessageE
Set WordApp = Nothing
GoTo Exit_Command53_Click
The success rate varies from PC to PC and user to user. It will work with one letter and not the next. It is very difficult to identify a pattern. When the Save As does not offer the expected name & path, the Application (or operating system?) offers to save ‘MyMerge’ to the user’s My Documents folder.
In that case I tell the users to correct the path themselves and save the document under the CallID. Theoretically, retrieval should work since this uses the CallID and wild cards. Sometimes it does but often it will instead retrieve a document in the user’s My Documents folder. If it does go to the right folder, you sometimes have to replace the last wild card with ‘.doc’. I have tried replacing the last wild card in the VB code with ‘.doc’ but this does not work!
Finally, I alone get the message ‘Save failed due to out of memory or disk space’, neither of which is true. This makes it very difficult to continue investigating the problem. If I use a copy of the DB on my hard drive rather than the network I can save but not retrieve.
View 2 Replies
View Related
Feb 5, 2008
I am trying to build a database for my digital photo collection.
(having pretty much gven up on the packages I see in the market)
Since most of the images reside on CD's and DVD's, I want to create
and store thumbnails and pointers (ie. location fully qualified name),
rather than a copy of the full image.
1. How do I define the fields in the table for the thumbnail, and the pointer?
2. How can I use the pointer value to retrieve the full image?
Any ideas and tips on where to start?
Thanks,
Roy
View 2 Replies
View Related
Jan 30, 2006
Hi,
Can someone please help. I am required to bring back a ranadom 10% of data where the [Total_Cost] field is less than £500.
Can this be done in one query? I have to use Access97 becuase this is the lowest Office Version we support.
:confused: .
View 1 Replies
View Related
Apr 19, 2005
I'm very confused. I have two tables in my db that should be the same except for columns(I copied one table from the other). In each I have a "default" entry as the first record in the table just to prevent my select queries from erroring out if they don't find a match for the criteria. When I use the following statement,
Code:SQL="SELECT ID, Name, Picture FROM background WHERE Name ='" & Fname & "' OR Name='Default'"
it returns the default value as the record, not the match for "Name", even though there is one(when I remove the OR Name='Default' it pulls the record).
When I use this query for the other table from the same .asp page,
Code:SQL="SELECT ID, Date_Time, URL, Icon, Title, Description, Status FROM Submission WHERE Technician ='" & Fname & "' OR Technician='Default'"
it returns the record where "Technician" matches even though it is below "default" in the table.
I want the query to return the "default" record only if there is NO match for the first criteria, which it does in the second case, but not the first. Is there some way to force this? Thanks in advance for any assistance provided! -Chris Gordon
View 4 Replies
View Related
Apr 17, 2008
I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:
SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));
But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!:confused::confused:
View 4 Replies
View Related
Feb 24, 2014
Why I getting an error when trying to run the below code ? If I take out
Me.ClientNameList.Column(1) = rs.Fields("[Tracking Date]") Then . . .
add "And" to
If Me.ClientNameList = rs.Fields("[Client Name]") . . .
I do get a record, but with the wrong date. I need to match the client's name and tracking date, then move the related fields to a MS Access form.
The code follows:
Private Sub ClientNameList_Click()
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Progress Tracking")
[Code] .....
View 14 Replies
View Related
Mar 1, 2006
Our organization (a University) has been engaged in becoming "paperless."
As part of that initiative, we have spent the past several months scanning several hundred thousand pages of documents relating to building systems (HVAC, e.g.) as well a a massive quantity of detailed lead and asbestos surveys and abatement records.
The initiative is indeed reducing the quanity of paper stored in binders and file cabinets, however now it is becomming evident that retrieving some of that information is a real problem for some folks.
At a meeting this morning a question arose about whether we could develop an Access application to retrieve the documents (which are very logically filed on a network share, but it seems to be beyond the compreshension of some staff how to actually navigate through the maze).
My question is this: has anyone in this forum ever tried to implement such a solution with Access?
Any advice or insight would be very much appreciated.
Thanks in advance.
View 1 Replies
View Related
Mar 11, 2005
Hi,
I build the WHERE clause in my form's record source dynamically, depending on the context in which the user opens the form. I can't put the WHERE predicates in the record source and refer to fields on another form, as the context will determine which predicates are required. So I have code like this:
Public gf_FormInstance As Form
Dim strSelect as String
:
Set gf_FormInstance = New Form_F_PerformanceSummary
:
gf_FormInstance.RecordSource = strSelect
gf_FormInstance.Refresh
gf_FormInstance.Visible = True
The problem is, the Set statement causes the New form instance to retrieve all records from the existing record source, slowing down the performance.
I'd like to find a trick to suppress the retrieval of records when the Set statement executes, and then allow them to retrieve when the Refresh statement executes.
Any suggestions would be much appreciated.
Thanks,
Keith.
View 2 Replies
View Related
Aug 31, 2014
I have an entry Form which is used to record prospective new members of a society in a Table named 'Foreigners' in a Membership Database using Access 2010. The entry form also records the name of the 'Interest Group' to which the prospective member is initially affiliated.
On entering this Group Name, selected from a drop down list from the Groups Table, The Group ID & Leader Member ID are automatically recorded.
However, I don't wish to record the Leader Member ID, what I wish to record is the Leader's Member Name which is identified by that ID in the related 'Mail List' Table.
In the Properties sheet the relevant source for this field is shown as Leader, but what is displayed is the Leader ID...
View 5 Replies
View Related
Feb 9, 2015
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
View 3 Replies
View Related
Aug 8, 2013
I was able to use the UNION ALL qry. But, when I have another file (like original2) that does NOT have all the columns listed in the UNION ALL qry, I get a Parameter value box asking for the missing columns when I run the qry.
Example:
original1IDDateGroupChristianJohnnySteve 18/5/2013A1528/5/2013B338/5/2013C2348/5/2013D2358/5/2013E5
original 2IDDateGroupChristianJohnny18/6/2013A212528/6/2013B2338/6/2013C2248/6/2013D22
The UNION ALL qry includes all the possible resources ( includes all the possible column fields Christan, Johnny, and Steve).
When I run the UNION ALL qry with the original2 file, An "Enter Parameter Value" box is displayed with the mssing column name "Steve".
Is there a way to Map the original2 table into a working table with all the columns, or use VBA code to construct the UNION ALL qry to only include the existing columns? My data has variable columns and I'm trying to avoid the parameter popups.
View 2 Replies
View Related
May 14, 2014
I have a MS ACCESS 2010 database with a data table which i am trying to create a query from. I have 6 columns of data( one with an ID Field and 5 Name Fields). Below i have made examples of how it first appears as a simple query and the second will show you what i would like it to look like.
What the simple query looks like: [URL] ...
Second what I want the query to look like: [URL] ....
View 2 Replies
View Related
Oct 30, 2007
Currently I'm building tables and forms. My first table (called Clients) lists the details of fictional clients. My second table is for invoices.
In my invoices table, I wish to link the column for client reference (note: stored in the Clients table) to the column that precedes it. This column will list the clients’ names and is selected from a drop down list that is linked to the Clients table.
What I want to do (if its possible) is to have the respective client ref. automatically show up in the next cell once I've selected the client to whom the invoice relates?
Am I making sense? Is that possible? If so, how do I do it?
Secondly, how do I do a sum of selected columns for my “totals” column? Basically, I want to add the figures found in several cells that precede it?
View 4 Replies
View Related
Mar 22, 2007
I'm affraid my confusing topic title is an indicator of how confused I am by this. I can't even understand the variables well enough to fully utilize Access Help or the Search function here...
What I have is a database hat has column headers that look something like this:
Customer_Name, Order_Date, Qty_Ord, Unit_Price, Total_Price
What I'm trying to get is a query output that will have
Customer_Name, Total Orders (in Dollars) for January, Total Orders (in Dollars) for February, Total Orders (in Dollars) for March, etc.
I've been able to set it up to SUM for one month, but not multiples.
I know I'm totally lame (for proof read any of my previous posts) but you guys totally bailed me out the other time I asked a lame question.
Thanks in advance!
View 3 Replies
View Related
Feb 19, 2006
I have just started using MS Access to enter customers into a database. I constructed the database and delegated the .mdb file to my coworkers so that they can enter their customers as well. Now I would like to combine all the customers entered by all the employees into one general table/database containing all of the customers entered by all of the employees. If anyone knows how I would go about doing that I would really appreciate the knowledge. Thank you.
View 3 Replies
View Related
