Look Ups As In MS Excel
Jul 9, 2007
I am very good with computer programs, microsoft and the like.
However, recently i have been working on Ms Access. I am trying to make it do a look up as you would do in excel
whereby for example:
I could just type in a DVD ID on a form and it uses a look up to find the DVD name or vice versa but i havent been able to get it to do it.
Look ups on access just help in the creation of drop down boxes (combo box) or list box Which i dont want!!
Simply: i want ms access to "fill in the blanks" for me i.e the dvd name when i enter the dvd id
Help!! Please!!!
View Replies
ADVERTISEMENT
Sep 10, 2007
I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
View 1 Replies
View Related
Mar 23, 2006
I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.
My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.
I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.
Thanks in advance for your help.
View 2 Replies
View Related
Apr 25, 2013
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
View 7 Replies
View Related
Aug 25, 2013
i want to export a table to excel , open this file and execute a macro from another file.
the code i have now is :
Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"
Opening the excel file goes ok, running the macro however not.
View 1 Replies
View Related
Sep 15, 2005
Hello,
sorry to post again my question but just cannot find a solution.
I have a table and would like to copy data into a template in excel. I know that the code will have to make a copy of the template and then copy the data into the new workbook into sheet1.
My table (table1) has 3 fields: SSN, FNAME and LNAME.
I want to copy these fields into cells B1 (for SSN), B2 (for FNAME) and B3 (for LNAME).
I will use a combo to select the recorset to copy.
My problem is how to copy data into the template. I understand that it is necessary to run a copy of the template and then copy the data into the new xls file.
Is there a way I can do this via code? Code help is appreciated. Thank you.
View 5 Replies
View Related
Oct 21, 2012
how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;
DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"
Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.
View 5 Replies
View Related
Jul 17, 2005
We currently have a spreadsheet to track all of a clients medical's bills and keep a running total? I'm trying to decide whether to continue to track these medical bills in the spreadsheet or create a table and make it a part of the client db. I'm leaning toward keeping the spreadsheet. It seems to be a task a spreadsheet was designed for and I can link it to the db or import it as needed. Any opinions on which is better? What would be the reason to to give up the spreadsheet and make it a part of the db?
View 6 Replies
View Related
Oct 18, 2005
Hi,
Can someone help, does anyone know how I can read values from an excel file, basically I have an excel sheet that I'm doing some vba work and I need to go to the excel sheet which resides some where, read the table and apply the values in my current excel file. I hope that I didn't confuse you guys. Thanks a lot.
View 6 Replies
View Related
Nov 3, 2005
Hi all,
I have added about 15 links on a form, linking to word and excel documents. The word documents open fine but all of my excel documents pop up with a messae saying the file cannot be open, yet when i just go to the document on my hard drive they all open fine...does anyone have any ideas please. Thanks
View 6 Replies
View Related
Nov 10, 2005
I want some guidance in regards to how should I export access form as a report to excel. I already have fields with formulas in it and here I have fields where I have written down the numbers, so when these numbers get exported to excel. Excel will be populated with with these numbers and all the calculation will be automatically done.
I hope I am making sense here.
I have fields in access that should export through a button and populate on excel sheet.
is there any vb scripting for this, I rem seeing something in the same context few months back, but I can't seem to find it rite now.
Thanks in adv
View 4 Replies
View Related
Jan 15, 2006
I want to use the data contained in Access table in an Excell app. What is the best way to link them - preferably in 'real time' - is there a way excell can read directly from the tables? We need to analyse some of the data in the database through Excell....
Any suggestions welcomed...?
View 8 Replies
View Related
Mar 16, 2006
Wondered if someone could help.
I am trying to Export an access report from an .mde (File>Export) and receive an error message "Overflow". the report itself is only 17 pages long.
Can anyone advice?
Thanks
Paul
View 3 Replies
View Related
Mar 21, 2006
I'm new to Access.
I Work in excel but the Sheets start to have many records and, sometimes it's difficult to apply the formulas.
My doubt is: Itīs possible to make with the Access "everything"
that i make with Excel.
Example I apply formulas like:
=IF(ISNA(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";IF(ISBLANK(VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE));"";VLOOKUP('[Total.xls]2006'!B2;$A$2:$AG$802;33;FALSE)))
Thanks in advance
View 2 Replies
View Related
Apr 11, 2006
Hi,
I'm new to this so please go easy!
I have an excel document which automatically refreshes data gathered from an access db.
I need to put a button on the switchboard on the same db to open the excel document, basically to make it easy for others to find.
I have tried hyperlinks but it doesn't want to play. It locks the db and then won't refresh the information.
I know i'm missing something blindingly obvious, can someone help?!
:confused:
Thanks
elsiegee
View 1 Replies
View Related
Jun 2, 2006
Hello,
Apologies if I have placed this in the wrong section but thought it was more access than excel.
I would like to on clicking a button, which simply has a close command behind it, for the database to zip the spreadsheet which it has just exported information to.
I have had a look round and tried to use the backup of database idea but could not convert it and did not know enough myself to change it.
I would greatly appreciate any help or guidance
Thanks
View 2 Replies
View Related
Aug 6, 2006
HI,
Excel is having difficulties calculating a workbook of mine. It complains about cirkular references, so I had to set the iterations manually.
The workbook holds about 5000 records and Iīm using a cosinus function.
The thing is I donīt know what to do. Excel calculates the workbook, but it doesnīt do it right. What setting should I use with the iterations?
Iīm thinking of running it in excel 97. Will that help?
I really need to be able to run this workbook. Does anyone have an idea?
Fuga.
View 6 Replies
View Related
Nov 22, 2006
Hi,
I was wondering if anyone might be able to help me here.
I have a question. Is it possible to create new worksheet in Access using VBA code?
What happen is that i have a form in Access that would export data from Access to Excel. I know that i could use the built-in feature provided in Access to export to excel. But because i guess i want to be more flexible in managing the data on excel spreadsheet. So would it be possible to write the excel programming in the Access?
Sorry for asking but i just want to know.
Thank you in advance
View 3 Replies
View Related
Mar 6, 2007
Guys I don't know if any of you have done this. I need to import a formatted excel file into an access table, I know that I need to write a vba code to do it, can someone please give me a tip or an example. The excel file is a formatted form, it's not based on columns & rows!!!! Please Please assist.
View 1 Replies
View Related
Apr 9, 2007
Hello there
i have this field in excel called project desciption that is mor than 255 characters.
i wish to import the excel file to access
unfortunatley my access table only has 255 characters.
it is not letting me do it.
any suggestions.
Help
View 10 Replies
View Related
Apr 26, 2007
Hi there
Does anyone know where can I find a tutorial for working Access with Excel?
I receive regulary a spreadsheet with information that I need to copy to the access database. I can do this manualy but I would like a query that could do it for me.
Thanks to your help
View 1 Replies
View Related
May 3, 2007
can anyone tell me how to open an excel spreadsheet from access 2003. I used to be able to do this in access 97 but I can't find a way to open or link a spreadsheet
View 1 Replies
View Related
Feb 9, 2008
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
Excel in Access (Part 3) (http://www.access-programmers.co.uk/forums/showthread.php?t=143970)
Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)
How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
Problem for Excel Developers
One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets.
MS Access is NOT Excel
This similarity of the “look” in both programs, the layout of the data, leads to the Excel developer mistakenly thinking that a database works in a similar way to a spreadsheet.
Flat File Database
Spreadsheets are very sophisticated tools for manipulating figures, and they can also handle data very well particularly in the form of a “flat file database”. You can also use the flat file approach in MS Access, however MS Access is primarily a relational database, meaning that it has the ability to relate tables of information together.
Relational
It is by taking advantage of this relational property of the database that you can really make significant improvements to data handling. So if you have a spreadsheet that requires upgrading to a database to take advantage of this, then this article is for you.
Typical Spreadsheet Layout
Below is an example which shows columns that you would typically find in a spreadsheet, first name, last name for example. Then a variety of subjects, with a check box against each indicating that the subject has been taken, passed or possibly even failed!
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableBoolean_2.png
Flat File is OK
A direct transfer of this spreadsheet layout into an MS Access table would be usable and indeed many Access databases are constructed in this way, and some sophisticated applications costing many hundreds of pounds are based on a flat file system. However as mentioned earlier, MS Access is a “relational” database, meaning that it has the ability to relate your data together.
You’re Not Relational????
So how and why should you apply a Relational structure? Well it solves a lot of problems, there are many but I have noted the main ones below.
Uncharted Territory
The first most common problem I have noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet, no doubt you will be able to come up with your own unique methods of extracting the data, however you will find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, “where no man has gone before” (student is a good substitute for “man”).
Maintenance
Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let’s say the school starts offering French lessons, in the spreadsheet all you would do is add a new column “French” and indeed in your MS Access database you could also add a new column “French”, however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!
Column Limit
Many modern spreadsheet programs can handle thousands of columns; however MS Access has a 255 limit to the number of fields in any particular table. So in the student table example, if you were at the 255 limit and you needed to add another subject then you would find yourself in a difficult position.
Relational Solution
So what’s the solution? This is the part that is sort of counter intuitive, you actually construct two tables from the original table and link them together. This is the “relational” aspect of the database coming into play.
How?
If you look at the original layout of the data above you can ask questions about it, is there any data in the original table that is related? Looking at it, I would suspect all of the boolean columns (the check box columns, yes/no data) they are all the same, so they are a likely candidate for a separate table. And indeed there is an obvious name for this new table, they are all “subjects” that the student is or could take.
The New Table
So now you have a name for the new table, “Subjects” and to link it to the data remaining in the original table, (first name,- last name) it will need to have a field which contains a match to the RecordUniqueID field. For this example let’s call this “MatchingID” then you need a field to record the subject and another field to record whether it is true or false. For the purposes of this demonstration I have terms these “TransposedSubject” and “TransposedData” and you can see what this should look like below:
http://i185.photobucket.com/albums/x317/UncleGizmo/StudentTableTransposed2.png
Free Normalization Tool (see attachment)
How do you get that new table you may ask? Well originally it was quite a tedious task, especially if you had many columns of data to move, you had to construct an append query and append each column. However I realized it may be a process that would lend itself to automation of some sort, and I came up with a form for handling this. This is my “Beta” version, and to make it “better” I need feedback!
Feedback!!!
Please be good enough to download the attached database and experiment, please tell me where it can be improved, where it's wrong and where I could do things better. Cheers Tony
Please note I have updated the attached form, Minor changes only. (10th Feb 2008 5.30pm)
Part 2 now available HERE: (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
View 7 Replies
View Related
Mar 16, 2008
i have a team register running on excel but now need to convert it to access is there a easy way for a total novice:)
View 2 Replies
View Related
Oct 12, 2004
Hello,
I have a table that I would like to update with information from a spreadsheet. The table are all of the property records for the county I live in. The table has a unique data field for every record called a "PIN" (Property Indentifier Number). It is in this format: 12-3456-78-90. Because the PIN's were hand entered, sometimes there is a space between the dashes and the numbers, but each PIN is different for every record.
The data from the spreadsheet I want to import has updated sale information for the property records in my table. I want to associate the PIN's in the spreadsheet to the PIN's in my table and only import the new owners name, sale date, and sale price from the spreadsheet to the table.
How would I do this import? It is probably straight forward, but I am a newbie with Access.
I am concerned that because the PIN from the Speadsheet was typed differently from the one table, the import won't work. The numbers are the same, there are just spaces between some of the numbers and dashes.
Thanks
View 3 Replies
View Related
Jul 18, 2005
I need some ideas here.
I export my tables to workbook sheets in excel using transferspreadsheet.The different sheets in my workbook are later imported to the corresponding access tables using transferspreadsheet.
1)I want to know how to totally hide my sheets in the workbook so that the users don't tamper with them after export using transferspreadsheet.
View 7 Replies
View Related
