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!
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:
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)
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?
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;
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.
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
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?
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)))
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 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?
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.
I was importing the large array of accounting data into Access from Excel through File - Get External Data - Import. Several columns contained tangible accounting data (e.g. registration numbers) that had different formats like "23423 34", "32-3545" and in some cases even contained letter constants. Therefore, I have assigned TEXT type to this column while importing and was sure that all values are going to be stored as text. However, what has happened is: 18307769 turned into 1.83078e+007! this means tangible accounting data that was used as a key field for further linking turned into bull&&$%! :-(
I know this problem is very simple, so please, can you give me a hint as to how do I solve it in the future.
Thank you in advance.
-- Regards, vb707 securely sell goods and services on-line (http://www.securemarket.org)
Is there a way to open an Excel file in the background, run an Excel macro to format the data as I need it, then import/append data into my Access table all with one button click?
Very new to access, having a little trouble :/ trying to combine 2 excel sheets in a Access DB.
I am able to import each sheet individually but I cant seem to get them to combine.
In each sheet their is 7 columns, 300 rows, with one similar column "Serial_Number" per sheet so what I wanted to do was combine both Excel sheets by using the S/N column, and keeping both sets of information intact.
For instance if the 1st excel sheet was the following. ¦ 1 ¦ 2 ¦ 3 ¦ Serial Number ¦ 4 ¦ 5 ¦ 6 ¦
And the 2nd was ¦ A ¦ B ¦ C ¦ D ¦ E ¦ Serial Number ¦ F ¦
I would like to combine them to equal in any order ¦Serial Number ¦ 1 ¦ 2 ¦ 3 ¦ ¦ A ¦ B ¦ C ¦ D ¦ E ¦ 4 ¦ 5 ¦ 6 ¦
Is this possible? I will have to eventually do this to a couple of Excel sheets so it would be handy if the process was repeatable.
i have a form in my database that on a buttons on click event it creates a excel spreadsheet. my question is there a way that when the spreadsheet is created instead of over writing the data already created in my spreadsheet that i would be able to to just add to it.
This might have been asked before but I just dont seem to find one!!
I need to retreive data from 15 differnt tables every moring for various reasons. I am looking for a query that selects these records and writes it to a excel spread sheet using vba codes probably on a single click.
I have asked a similar question before but not got a very clear response.
I had hoped to be able to just cut and paste Excel macro code into an Access module and then make a reference to Excel and run the code but it doesn't seem that simple.
I have an Access Form that allows the user to enter a name (Text Box)(Owner name) they then hit a submit button which then querys a table and returns the records in a List Box below. I can then hit another button and Preview a report of the results, print the report etc. I have to take this one step further and create a way to print these returns on envelopes, mailing labels, and certificates. I tried this in Access and I cannot get information to line up correctly. ( I have specific layouts I need).
What I do have is an Excel spread sheet that has macros in it. I was thinking that If I could export those returned records from Access into Excel (The Spreadsheet with the MACROS) then I could just run the Macros in that Spreadsheet.
QUESTION: Does anyone know how I can open a specific Excel Spreadsheet and import the records from my Access Query (The ones that show up in the List Box)
hello i would like to find out if this can be done.on a excel file because we have row and columns that are like a1 a2 a3 we could multiply each row and have a answer.and we can add b1+a3 so we have a continue at next row. in access can we have such work be done? for example.i have 3 columns that the first contain event and the others contains numbers but not in all rows.on excel i have made a column thatis an if so it works like that.
on d2 i have this (if c2>0 then c2 else c1) on d3 i have this (if c3>0 then c3 else c2)
i am creating a database that will house some supplier information. once designed, the db will be populated with all the data and then it will be used as a reference tool for future projects.
it was requested that an "excel front end" be used for lookup and ease of use to access the data when it is finished... i am not really sure how that works.
also, what is the best way to create contingent variables in access-- for example, if a certain value is in the database then you have a corresponding action. like if there were a field asking how many suppliers there were, and if say the number is 4, then 4 entry windows come up. a sort of if/then statement?
Hi, I'm terribly new to Access and am mighty confused!
I'm creating a DB to use with www.cart32.com shopping cart. Here's my question.
I have 50 spreadsheets in MS Excel from 50 different manufacturers (each manufacturer's spreadsheet has the same type of info: : Part #, Description, Price, Weight). I have one main DB in Access called Products.mdb. When I import or link the Excel spreadsheets to Products.mdb they become tables attached to the DB yet when I open the actual products.mdb there is nothing listed..?! When I open the individual tables, everything is there from the Excel spreadsheets. So are tables part of the main database? I'm confused.
Basically, I need some advice on how to manage 50-100 spreadsheets of various manufacturer's products info, import it easily and quickly into MS Access from Excel into a single database, be able to update it when I get new spreadsheets and be able to FTP the DB to my account with Cart32.
I have a situation. In completing our Annual Report for our Accrediting body for my school, it requires us to complete a 'Completion and Placement Chart'. This chart was provided to us in Excel. The problem was that we needed a system to automaticaly update this chart as the year progressed so we can visually see our completion and placement rates. With this, I created an Excel sheet with the Completion and Placement Charts and made it so it can be updated as I entered a students information in.
Although it does calculate the data properly, the process is extremely ugly. It is terrible in appearance, function, and by all means, far from user friendly (I understand it, but it takes some explaining to do for someone else). This Workbook that I created houses almost all the information I need with the exception of it being entirely unsafe and far from being secure and data entry is a nightmare.
So, this brought me to Access. I created a database for this purpose (first time using access as well). It works like a dream in storing all the data that I need and keeping it safe. I needed to have a 'Placement Tracking Document' for each student outlining the students information for the accrediting body. This was done wonderfuly in Access and could not be accomplished through Excel (that I know of). However, I NEED to have Access tally all the information in the 'Completion and Placement Charts' like the Excel Workbook does. I need to keep the exact format of the 'Completion and Placement Chart' because it is a standard form in Excel and I can not steer away from it.
How can I get my DB in Access to comunicate with the 'Completion and Placement Charts' and have it automatically tally like my workbook???
I have attached some screen shots of my database and the entire Excel Workbook (Its edited because of confidential data) so you can understand a little of the chaos.
I rarely used Excel and never used Access until now. It took me nine months to create what I have because I was learning as I went. The books that I purchased to help me out do not touch this subject nearly enough (Access Cookbook, Access for Dummies and VB & VBA in a nutshell).
My point is, any help or direction in where to get thorough information on how to acomplish this is more then welcome. Any comments on what I have as well is more then welcome (negative or positive).
Thank you thank you thank you thank you in advance!
Oh, and use the buttons in the Excel Workbook to navigate.
I have a database that works as a sales system. From a table in the database I run a query that calculates the totals for that day. i.e.
Date……….Dept 1…..Dep2 07/11/05…...£10………£10
What I need is: 1.You click a button 2.It copies the date and finds it in the excel spreadsheet as the sheet will already have a field called date. 3.It will then copy the Dept 1 figure and Dept 2 figure into the spreadsheet where the date matches (in a certain column)
I would like to set up a form in Access that accepts the input of an Excel spreadsheet file (browse for file) and then creates a pivot table on one of the sheets, and appends that data to an Access database table..
can you give me some ideas on how i should approach the problem?
I have an existing database in access now I need to add records (10000 rows) in this table from excel. When I try to do an import from Excel to Access it gives me an error. Please Help!!
