General :: Multiple Excel Columns To One Field
Apr 14, 2014
I have an excel sheet, sent to me by someone else. It contains a column of unique information (site IDs), followed by three columns which each contain either a unique value or a blank, something like this:
Code:
sID|Code1|Code2|Code3
011|1234A|1234B|
021| |2345B|3456C
031|3456A|3456D|
041| | |7890E
The ID represents a particular business with which we interact, whilst the codes represent particular types of contract that we have with them.
This is then followed by a lot of columns containing information about each entry, name, address, post code, contract data etc.
I need to relate this to the employees at those businesses, whose data I have recorded in a separate spreadsheet, so that addresses and such are also related to people, not just the business unit.
This spreadsheet records each person by employee ID (a unique value for each entry) and Code, without taking account of whether it is a code of type 1, 2 or 3. Like this:
Code:
eID|CodeX|FirsN|SecoN
011|1234A|Teddy|Bears
021|1234B|Harry|Horse
031|3456A|Edwin|Eagle
041|3456A|David|Tyrex
I figure the best way to create a relationship between these tables once imported into access is via a third table containing a column with all the Codes in a single field and a second field with the sID related to each code.
Code:
sID|CodeX|
011|1234A|
011|1234B|
031|3456A|
041|7890E|
My question is, how do I get all the Codes from the three different fields into a single field and relate it to the relevant sID for each?
View Replies
ADVERTISEMENT
Sep 5, 2012
I'm having problems importing a spreadsheet from Excel for a client.
They are using Access 2003 and Excel 2007, 3.5GB RAM
It works perfectly on my machine - Excel 2010 and Access 2007-2010, 4 GB RAM.
The file is imported in Excel 97-2003 format via a macro using the transferspreadsheet function.
It returns error 3274: "external table is not in the expected format."
There are 1488 rows and 71 columns in the spreadsheet and resulting table - in future additional columns may need to be added representing new critical data.
The interesting thing was that it worked fine on my machine. Then as a trial and error process I cut the file down to 26 columns and it worked fine. 52 columns also imported. But it gave up when there were 71 columns.
if 52 columns * 1488 rows =77,376 record worked, that's more than the magic number of 65,536. but it doesn't like 71*1488 = 105648 records. Is there a limit at 100,000, or some other number in between? I would have thought Access could hold/import much more than this.
View 1 Replies
View Related
Sep 28, 2013
I am just starting out learning how to connect Access to Excel. From an Access form, I need to connect to an Excel spreadsheet, and loop through every row/column to look for data that matches a database field data, then populates the associated cell data. I don't want to import the spreadsheet since the user who created it is using it manually, and I am attempting to automate the task. This is probably the most elementary code, but I would like to start out with the best way to do this. What I am finding is code that appears to be much more complicated than I need, for more complicated situations.
View 2 Replies
View Related
Dec 13, 2012
I am using Access 2010 and Excel 2010. I need to have VB script to export the access table 502 records by 38 fields into Multiple Excel workbooks each having multiple tabs. In the Access table each record has two fields: Div and Tab that will be used to name each workbook and each tab (sheet). There are 6 unique "Div"'s to name the 6 workbooks and there are several "Tab" names for each Div (workbook).
Note: These 6 workbooks with multiple tabs were originally imported into Access from one common folder on my desktop by this routine:
Option Compare Database
Option Explicit
Private Sub Command1_Click()
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
[Code] .....
View 7 Replies
View Related
Dec 18, 2013
I have a database with employee folders containing various bits of information. My intent is to have a main page with a sub report or from containing employee names. Instead of the names being displayed in a single vertical column with a scroll bar, I would like to display multiple columns of say twelve names each with a horizontal scroll bar no information with be edited from the "main" page..
The information would be "last name, first name" from a query, you would select (highlight) the name and hit a button to bring up a different form with that employees information or double click for the same results.
View 6 Replies
View Related
Nov 28, 2014
I made a new column in a table in access. The table is not linked and linking unfortunately is not an option. In the new column, I want to create a sum for 5 years of funding. There are multiple columns for a different fiscal year.
In excel, it is simply =sum(range), but I am drawing completely blank how to fill in the column with the sum data.
The end result is the data from 4 tables will be displayed in Forms and Reports.
Essentially what I need is an ability to bulk update a column in access table without a need to reupload the entire datasheet (over 100k rows with about 30 columns).
View 2 Replies
View Related
Sep 22, 2013
I have a problem to count a multi columns into one field. For example, I have a table consist of these fields (simplified version):
- id
- block_name
- handle_type (lookup: big and small)
- kernel_type (lookup: big and small)
- fruit_set (lookup: 1, 2, 3 and 4)
I want to create a query to count a combination of handle_type and kernel_type columns as one column. So the query field would shows these fields:
- block_name
- count_of_big_handle_big_kernel
- count_of_big_handle_small_kernel
- count_of_small_handle_big_kernel
- count_of_small_handle_small_kernel
- count_of_fruit_set_1
- count_of_fruit_set_2
- count_of_fruit_set_3
- count_of_fruit_set_4
View 1 Replies
View Related
Jul 17, 2013
I have a multiple select combobox which shows up in many forms- SubCategories. The user can pick from a list of 154 SubCategories.
Everything is working properly however it is difficult for the user to scroll through the entire list.
My question: Is there a way to represent this ONE field in multiple columns in the combobox?
I know how to add columns for multiple fields to my combobox, but this is one field.
Maybe there is a better way to represent the data... a subform would have the same issue.
View 6 Replies
View Related
Jan 3, 2013
Number of Operators per Permit
Permit
Operator Name
6
065-24088
EOG RESOURCES INC
6
065-24088
PDC MOUNTAINEER LLC
[code]....
I want to take this data and create six new columns (operator1; operator2; operator3; operator4; operator5; operator6) for each permit number so that each unique operator name will be housed in each of those six columns, with the rows being defined by the permit number. So, I want to produce something that looks like this...
Permit
operator1
operator2
[code]....
At present, there are as many as 6 operators per permit, but the number of operators per permit varies from 1 to 6. I included an example that has 5 operators, to show that I want the sixth column to be null in this instance. So if a permit had only one operator, only the operator1 field would take on a non-null value in the new table/query.I know how to do this with 2 operators per permit by using min/max or first/last functions in a query, but I don't know how to deal with more than two operators per permit.
View 4 Replies
View Related
Sep 25, 2013
i want to display a single field values in list box with query as row source.the query will produce variable number of records or rows. sometime 1, sometimes 3 or 6 my thought is to distribute these records/row into list box column. for example:
if query = 1 then listbox column = 1
if query = 6 then listbox column = 3 (two rows per column).
View 1 Replies
View Related
Jun 30, 2015
I have information held in 3 different tables and I would like to extract this information to three different tabs in a singe Excel workbook - preferably in one step.
My access knowledge is fairly basic but I have been looking online and I can only find out how to do it using a VBA script - which is quite terrifying! Is there a simple way to do this?
View 1 Replies
View Related
Apr 23, 2013
I am using Access and Excel 2007. This Db is for a mental health practice to track and store the paper questionnaires that the patients fill out. The data then needs to be exported to Excel so that it can be imported into a proprietary software that analyzes the data and recommends treatment plans. (It does NOT play nice with Access, forget it.) There are many forms and all of them are fine and export to separate worksheets no problems.
Now for the problem child: One form has 493 fields. Obviously I could fit that into two tables, but it seemed cleaner to use a main form and main table with the patient information (ID_Number, Name, Date, etc.), then tabbed subforms and separate tables for each “section” of the questionnaire (School, Work, Home, etc. There are 11 tables/forms in all.) These tables are all related by the ID_Number. The problem is the export. I need all 493 fields to write to one worksheet in order. This would of course involve removing the ID_Number field from all the tables except the main one. A query obviously can’t handle that many fields. VBA I can TransferSpreadsheet but then each table goes to a separate worksheet.
I’m thinking maybe calling some SQL code that will drop the unneeded fields and keep appending the data to the worksheet one table at a time? Of course the rows would somehow need to be defined WHERE ID_Number = ID_Number so that the same patients information is all on one row.
View 10 Replies
View Related
Jul 3, 2014
Work have asked for a lot of information to be run from Access and exported into Excel. The info they require will need to be exported into 4 Excel worksheets in the same workbook. Is it possible to tell Access that when they click on the report button on the form, it will automatically run the various queries and then put them into separate worksheets in the same book? I think this is perhaps too complex for Access to do?
View 14 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
Jan 10, 2007
Hello,
Not sure I posted to the correct forum, but here goes
I would like to import an excel sheet into a new access table. However I am having problems
with multiple fields.
Lets say for instance that in my excel sheet I have the following fields
Field nameName ExamExam_scoreDate
PurgeMaths607-01-2007
--------------------------
Then I import that into a table. Then that is no problem, but then going back to the excel sheet I have noticed that one study could take many different exams. Hence the below
NameExamExam_scoreDate
PurgeEnglish707-01-2007
------------------------
we have a problem in the table because the fields begin to multiply if Purge has taken 20 exams
fieldname
name
exam
exam_score
date
exam2
exam_score2
exam3
exam_score3
exam4
and so on....can you imagine if I hit 40?
I was thinking maybe I could add some lookup field to a cell in excel or access? Am I on the correct path? Since I want to import, but the multiple fields/columns are getting in the way.
View 1 Replies
View Related
Nov 2, 2012
I'm learning Access by myself and i have some problem with exporting a multiple value field to an excel.
I have a field name "Users" in a table wherein this field is a multiple value field and looku up the value from the other table. When I export the table to excel, the data in the multiple value field does not export properly and it just shows some symbol in the excel.
Is there something wrong with my field?
View 2 Replies
View Related
Sep 4, 2012
I'm relatively new to Access. I've created a database where we track product information and testing done on the products. I have the Product Code set up as a combo box with five other fields auto populating based on what is entered into the Product Code field. However, when I export the table or form to Excel, the Product Code changes to another number, which I think is an auto number but does not directly relate to the product entered (either one up or down from the product).
View 2 Replies
View Related
Apr 10, 2014
I exported about 190,000 records (about half of all records in the table) from Access DB to Excel. I then updated two fields (that were previously blank on the database) on the Excel sheet with values. What is the easiest way to import these new values back into the database table so that the records receive their respective values?
View 1 Replies
View Related
Aug 30, 2012
I am the new for the vb little bit know the access, while exporting excel from access query, i require save field name as file name.
View 2 Replies
View Related
Oct 5, 2012
I actually have an ms access database with the following format(assuming)
title1title2title3title4title5title6
1a1b1c1d1e1f
2b2b2c2d2e2f
3a3b3c3d3e3f
4a4b4c4d4e4f
for each row in the ms access database above i need to retrieve an excel in the format below
title11a
title21b
title31c
title41d
title51e
title61f
title12a
title22b
title32c
title42d
title52e
title62f
and so on....
Where I can proceed, what tool can I use??
View 2 Replies
View Related
Jul 31, 2013
I have three tables. Risk, Names and RiskAndNamesJunction table. I have the junction table because I have many to many relation (meaning many people can be connected to one risk and many risks can be connected to one people).
The problem is that If I make a query to show the people related to the risks, if there are many people for one risk then it will put the people in different rows. Meaning that for risk 2 I will have three rows, because there are three people connected to this rows. See the attached file!
What I would like to do is to have a query which (in case there are more than one risk owners) puts the second name in another column, the third name in another column and so on. So I will have only one row per risks.
The attached file is a dummy file, so there are only maximum three names per risk. In the real file the maximum is five names per risk. So I am talking about no more then five extra columns. (So I am talking about a query which would put the first finding in the first extra column, then the second item in the second and so on till five. It there is no third or fourth or fifht item then the columns remain blank).
Unfortunately I have to do this because our mother company works with excel and they are sticking to this format in excel.
See the attached file ....
View 2 Replies
View Related
Nov 7, 2007
Hi all :)
I'm not sure if this is the right forum to post on but here goes,
I am exporting a query from access 2007 to excel 2007 and my yes / no columns are changing to true / false in excel is there any way to make these appear in excel as yes / no instead?
regards
Jackie :)
View 4 Replies
View Related
Aug 8, 2013
Basically, I'm compiling a table of funding sources and whilst all the fields are relatively straight forward, how best to approach the matter of where the funding is available. For example, Funding A is available in America only, Funding B is available in America, Canada and Europe and Funding C is available Internationally etc (a huge number of possible options and infinite different combinations). How's best for me to lay this out because there will be hundreds of funding sources all with different availability criteria? If the field name is 'Countries funding is available', can a data type input be multiple entries?
Ultimately I'll want to be able to query show all funding sources available internationally or show those available to America and Canada only etc...and when I query on another field or show all, I'll want to see where each of the funding sources are available, listed out .
View 1 Replies
View Related
Aug 23, 2006
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
View 9 Replies
View Related
Nov 18, 2004
I don't know if it is the right place to post this question, but I know many people here are good at both Access and Excel. The question is:
In an excel sheet,
A1: Smith, John
A2: 111 Pine St.
A3: San Diego, CA
A4: (555) 128-549
A5: Jones, Sue
A6: 222 Oak Ln.
A7: New York, NY
A8: (555) 238-1845
A9: Anderson, Tom
A10: 333 Cherry Ave.
A11: Chicago, IL
A12: (555) 581-4914
2. Type the following formula in cell C1:
=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))
3. Fill this formula across to column F, and then down to row 3.
4.Note that the data is now displayed in cells C1 through F3 as follows:
Smith, John 111 Pine St. San Diego, CA (555) 128-549
Jones, Sue 222 Oak Ln. New York, NY (555) 238-1845
Anderson, Tom 333 Cherry Ave. Chicago, IL (555) 581-4914
This is exactly what I want. And I can do step 3 manually to copy that formula to each cell. But,
My question is in step 3. If I have thousands of lines, how can I fill that formula to thousands cells? from C1-C1000, F1-F1000.
I really appreciate it. Thanks.
View 1 Replies
View Related
Jan 11, 2005
Hey, does anyone know how to set up a formula that will allow me to turn either one column on, or another, but not both at the same time for data entry eg. one column for dollar figures and another for sterling currencies?
Much appreciated!
View 2 Replies
View Related
