Microsoft Access Tutorial Pdf Bangla
Nella Mcnairy
If you read Simon Hurst's pain-free introduction to Microsoft Access and are interested in learning more, this tutorial shows how you can use the database to produce a simple trial balance. A full version of the tutorial, with illustrations, can also be downloaded in Word format.Introduction
This is a very short tutorial designed to demonstrate the use of a special type of database query in Microsoft Access. Usually when more than one table is included in a query, the tables are joined. However, it is also possible to include tables in a query that are not joined. This has a rather strange effect. If two unjoined tables are included in the query, then there will be an output row for each record in table one, for each record in table two. So if table one has 1000 rows, and table two 1000 rows, the output will consist of 1000 x 1000 rows or 1,000,000 rows. Though this is generally not what you want, there are situations where it can be useful.
We want to add a second reference to the AnalysisCodes table in the ExpenseItems table - this will be used to record the source of the transaction as opposed to the analysis. The easiest way to do this is to select the AnalysisCode field, copy it, and paste it at the end of the list of fields 'then change the Field Name to BankCode.
Travel New client Petty cash89/05/04Train fare 24.500.00Travel HJ Fruiterers Petty cash[Add similar entries of your own devising if you want a fuller collection of test data]Source and Analysis
We will now create a query that shows both source and analysis descriptions. We go to the Queries section and choose the Create query in Design view option. Double click the AnalysisCodes table to add it to the query, then double-click it again to add it a second time, finally add the ExpenseItems table and Close the Show Table window.
Move the tables around the screen and change the size and shape of the table windows so that it is possible to see all the fields and how the tables are joined. Note that the AnalysisCode that we added second is named AnalysisCodes_1 to identify it.
At the moment, both of the AnalysisCode tables are linked to the AnalysisCode field in ExpenseItems. We want to link AnalysisCodes_1 to the BankCode field, so we right-click the existing link, and choose delete from the shortcut menu.
We will now add our fields to the output grid. We have used the Date, Description and GrossAmount fields from the ExpenseItems table and the AnalysisDescription fields from both of the AnalysisCodes tables. We will save our Query as Analysis and then run it to see it if works as we intend. After running it, we should be able to see both the analysis and the source code descriptions.
Creating a trial balance
So we have a query that includes both codes, now we will turn it into a list of codes and amounts posted to them. To do this we have to split the single lines with both a source and analysis code into two separate lines - one for the debit side of the entry and the other for the credit. This is where our special query comes in. First of all we need to set up another - very small - table.
So back to tables, and Create Table in Design view. The TB table design will need just one field: Sign - a number field. The table will have just two records in it: one with the value 1, and the other -1.
Now back to queries. We will base our Trial Balance query on the Analysis query we have just created. First of all we will add our new table to our query - click on Query, Show Table and double click the TB table, and then close the Show Table window.
Now we add the Sign field from TB. You will notice in the output that for each row in ExpenseItems, there is a row with each value from TB - because there are only two records in TB this means each expense item has one row with Sign '1' and one row with Sign '-1'.
The next thing we need to do is to replace the two analysis descriptions with one - the analysis description from AnalysisCodes for the debit line, and the bank description from AnalysisCodes_1 for the credits. We also need to leave the amounts as they are for the debit lines, but make them negative for the credits. So it's back to the design screen.
We will return to design view to change this to a totals query by deleting the Date and Description fields, and pressing the Totals button. Swapped the two columns around by selecting the Total column, and dragging it to the right of the Nominal code column. Finally set the Total row for Total to Sum. The output should display a simple two column view with the Nominal Code account on the left and the relevant total on the right.
A TB report
Finally we will take our Analysis query and turn it into a report. First we will close the query and save it. Now, in the Queries window select Analysis, and then choose Report from the New Object drop down list. Choose AutoReport: Tabular and press OK.
About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of AccountingWEB's Office ProductivITy Kit and '100 Best Tips', visit the The Knowledge Base website. AccountingWEB members can also access his back catalogue and IT Clinic online email support via the Office ProductivITy service.
Subscribe to the ExcelZone newswire
To keep up with all spreadsheet-related developments, click the button below to subscribe to the free fortnightly ExcelZone newswire. The system will take you back to the AccountingWEB home page after it adds your name to the ExcelZone subscription list.
slightly confused
I have looked at the previous tutorial and I am now trying to bridge the gap to this tutorial. However, am i right in assuming that you have not indicated anywhere what properties the Analysis Code table and Expense Item tables should have. Should I not be going into design mode of the table and first be shown how to set up the said tables together with properties for each?
Become proficient in Microsoft Excel and go from beginner to pro with our hands-on Excel Bootcamp. Learn functions, formulas, Pivot Tables, macros, and time-saving techniques to conquer all the key skills needed to excel in Excel.
Explore the top ten YouTube channels to learn Excel at your own pace, ranging from Microsoft Office's official channel to channels dedicated to Excel enthusiasts. Whether you're a beginner or seeking to enhance your existing skills, these channels offer a wealth of information on formulas, functions, PivotTables, and more.
Microsoft Excel is a spreadsheet application that was created to help users organize data and numbers by applying various functions and formulas. Excel places data like formulas, numbers, or text, into cells within columns or rows. It provides a way to organize data as well as perform calculations on it. Excel can also be used to create charts, PivotTables, or graphs that depict data visually. Subscriptions to Microsoft 365, which come with Excel and other productivity applications, are available for professional and personal use.
If you want to study Excel from your own home or office, and learn at your own pace, there are multiple learning options available on YouTube. Dozens of channels are devoted exclusively to this helping beginners learn the basics of Excel, as well as channels and playlists pertaining to more advanced Excel topics, tips, and formulas. Both long and short videos are available for free, which provide quick tips, answer specific questions, or provide detailed instruction on Excel-specific subjects.
Nearly half a million people currently subscribe to this Microsoft channel. Users can access videos on a variety of Microsoft products, apps, and platforms, such as PowerPoint, Outlook, Teams, and OneDrive. Their Excel channel contains videos on a range of helpful Excel topics, like visualizing data, formatting, and creating PivotTables.
ExcellsFun has been around since 2008 and currently offers more than 3,000 videos and free courses designed to help users master Excel basics as well as more advanced Excel functions. This is a great resource for those who wish to explore beginner-friendly topics like keyboard shortcuts and basic formulas, as well as more complex skills like data validation and array formulas.
Excel Campus was created by Jon Acampora, who has provided 38 million viewers with over 270 tutorials on Excel, ranging from beginner-level instruction to useful hacks, as well as more advanced tools and procedures. Video content includes topics such as how to create a personal macro workbook and how to add a search box to a slicer in Excel.
TeachExcel posts both long and short Excel videos each week. Their videos take viewers through the steps needed to complete simple and complex Excel tasks, like making graphs, recording macros, securing worksheets, and formatting spreadsheets. In addition, they offer dozens of playlists for learners to explore timely Excel tips and tricks, VBA, financial modeling, and other relevant topics.
This is a great resource for Excel freelancers to learn the ins and outs of designing and marketing software based on Excel. Dozens of practical videos can be found there dealing with how to turn Excel into a point of sale, its purchasing applications, and creating automated Excel forms, among others. This channel also provides subscribers with the option of joining a mentorship program that teaches the necessary skills to earn a passive income from Excel.
Excel Dude has both long instructional videos, as well as short videos that contain tips and tricks for working with this app. There are also 320 shorts, or one-minute Excel clips, each of which covers a specific function. In addition to Excel playlists, this channel also contains instruction on other Microsoft apps, such as PowerPoint, Word, and Teams, among others.
Exceljet offers a variety of straightforward Excel videos and playlists that cover the core skills required to work with this application. Playlists are available on topics like conditional formatting, functions, formulas, data entry, and shortcuts. In addition, more advanced and specific Excel content is also available, such as how to chart sunrise and sunset times, as well as how to add unique content to a PivotTable.
c80f0f1006