Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)
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)
Using the normalization form --- Download Here (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804) we converted part of a spreadsheet type table into the beginnings of a relational table.
From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.
Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:
Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.
Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.
This was achieved with a form based tool available to DOWNLOAD here. (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804)
See instructions on its use here: (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)
However you may be looking at the new resultant table and wondering what on earth to do with it. I hope to take you through the process of making it into something useful in this thread.
The first thing you will notice is that where the check box is not checked, then that whole row is redundant, for instance there’s no need to record that ID number “1” --- “Has Not” taken Maths, English, Geography, Physics etc, it would suffice just to record the subjects that have been taken , In this case Biology, PT and Social. Looking at those entries in particular, then a general rule of logic can be defined, “delete all the rows where the check boxes are false”.
then it becomes obvious that the check boxes themselves which now “All” contain a true value are also redundant, they can be deleted just leaving you the text entry identifying the subject taken by each student.
Using the “relational” properties of the database that is one more thing you can do which will improve efficiency and that is to replace each text entry --- Maths, English, Geography, Physics etc, with a number linking that field to a look up table.
First of all you need to create a look up table; this can be done by applying a create table query to extract just the unique values for the “subject” There is a video showing how to do this here: (At time index 1min) (http://www.viddler.com/explore/TonyHine/videos/67/fullscreen)
This unique list should be called “tblSubject” this table is not quite finished, you need to add an identity column to the left of the text representing the individual subject, this identity will then appear in the previous table.
Once you have completed the “look up table” you then need to replace the entries in the student subject table “tblStudentSubject” where it shows subject in text form with the number representing the link to the look up table. This is the query:
But this will import all the data in that worksheet into the Access table.What I want to implement is to import part of the data. For example, the data I need is start from Row30 till the end, thus I don't need the data from Row1 to Row29.
I have what I think is a difficult problem to overcome...
I am designing a form to create an invoice. The user will select a workstream and a date range in form frmInvByHrs. Within this I want two sub-forms, one is frmInvByHrsTsht and the other is frmInvByHrsBill. I want the first one to display all the staff and their hours done, and the second one to be in data entry mode where you can enter the hours you want to bill. Each sub-form is based on a separate query.
Is it possible to do this? ie. to have one sub-form in data entry mode, and the other not? It seems to me that the data entry mode is controlled by the MAIN form regardless of the sub-form settings!
If this is not possible, do you know how I can acheive this?
I have refined my query from previous threads to involved a module function. This calculates more acurately no of working days between dates and takes into account a holidays table. (All credit to Arvin Meyer on the module:) ) However because the Leave Year starts at the 1 July and finishes 30 Jun I need to compose the date for any current year Year(Now())
Enclosed scrdmp shows my query design. I can easily get it to work as you see it, but obviously as each year rolls over, the year needs to change.
Have looked at many posts but can't find what I'm looking for. This one will get me over the hurdle.
I have a sql server query that I need to use in access 2003 but I can not figure out how to convert the "not in" part of the query. Here is the sql server query
Select * from accounts where beg_date between '1/1/2013' and '12/31/2013' and cast(acctNo,integer) in (2,3,4,5,7,12,20) and acct_type not in ('Individual','User','Viewer')
I can not find how to do the "not in" in access sql.
We are trying to use Access to generate, job, task and craft numbers in Access. Job and task will be unique. Craft numbers can use the auto number function. We need the numbers to be 4 digits for Job and Task.
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.
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 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)
