1 / 25

CIS 338: Using Queries in Access as a RecordSource

CIS 338: Using Queries in Access as a RecordSource. Dr. Ralph D. Westfall May, 2011. What Is a Query. SQL code in a database that gets specific data based on selected fields (columns) selected records (rows) selected (one or more) table(s) in contrast to getting all data from one table.

rocco
Download Presentation

CIS 338: Using Queries in Access as a RecordSource

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CIS 338: Using Queries in Access as a RecordSource Dr. Ralph D. Westfall May, 2011

  2. What Is a Query • SQL code in a database that gets specific data based on • selected fields (columns) • selected records (rows) • selected (one or more) table(s) • in contrast to getting all data from one table

  3. Initial Database Set-Up • download dbquery2000.mdb file • for demonstration, NOT for this quarter's project • start Microsoft Access • open dbquery2000.mdb file • if prompted, follow directions to convert database to latest version of Access • might have to convert back later

  4. Creating Queries for Totals • click Create tab • click Query Design • select Payments table, click Add • Close the Show Table window

  5. Creating Queries for Totals - 2 • double click BldgNo, AcctNo and Payment from Payments table • when double clicked, item should go into query table (or can drag & drop) • click totals button (Greek letter sigma) on toolbar at top of screen (view effect) • click 3rd (Total) line in Payment column and select Sum from drop down list

  6. Creating Queries for Totals - 3 • click 3rd (Total) line in AcctNo column and select Where from drop down list • put 5401 in Criteria (6th row) of AcctNo column • don't need to use a criterion for this quarter's project • change to Group By in BldgNo column

  7. Creating Queries for Totals - 4 • make sure that Show is checked for all columns except AcctNo • close and save as default, Q5401 or [ ] • right-click to Open this query to make sure it is getting the data it should • could calculate totals in Excel to verify • make changes if necessary

  8. Creating Queries for Totals - 5 • repeat steps in the preceding 4 slides • or copy and paste Query1 twice • change the Criteria to 5601 for the 2nd query, and 5801 the 3rd query • you should now have 3 queries • Query1, Query2 and Query3 (or whatever you want to call them)

  9. Setting Up a Combined Query • click on Create then click Query Design • click Both tab in Show Table window • select Buildings table, click Add • select the 3 queries you just created, click Add • Close the Show Table window

  10. Combined Query - 2 • left click, drag and drop BldgNo from Buildings table to BldgNo on 1st query • should result in a connecting line • repeat with Buildings table and 2nd query, and then with Buildings table and 3rd query • click and drag query tables up and down • should see separate connecting lines from Buildings table to each query

  11. Combined Query - 3 • right click on line between the Buildings table and the 1st query • select Join Properties • click the radio button for 2. (Include all records from 'Buildings' …), then OK • left join: all buildings , even if no cost data • repeat for line between Buildings table and 2nd query, and then between Buildings table and 3rd query

  12. Combined Query - 4 • double click BldgNo, Building, and SquareFeet and anything else you need from Buildings table • when double clicked, item should go into query table (or can drag & drop) • double click SumOfPayment in 1st query, then in 2nd and 3rd queries • items should go into query table

  13. Create Calculated Columns • in 1st open column, type: LeasePSF: [Q5401].[SumofPayment]/[SquareFeet] • source needs to match query name • repeat named as TaxPSF and OtherPSF, using data from 2nd and 3rd queries • uncheck Show for all 3 SumofPayment columns

  14. Create Calculated Columns - 2 • run query • note that some cells are blank • "null" instead of a zero value • buildings where there is no data for that account # • null values can cause problems with VB.NET calculations and elsewhere

  15. Create Calculated Columns - 3 • getting zeros from Null data • create calculated columns with code like LeaseCost: Nz([Q5401].[SumOfPayment], 0) • Nz returns 0 for Null numeric data and a zero-length string for a Null text value • after this is coded, it is possible to divide [SumOfPayment] by [SquareFeet] to get LeasePSF for all properties • run, close and save as Query4 or [ ] Notes

  16. SQL Code • Microsoft Access generated SQL code based on the query you just created • use View>SQL View to see SQL code • could copy and paste this code into VB.NET when need to create SQL statement for Access (or modify to SQL Server syntax)

  17. Ending Database Set-Up • if need to use an earlier version of Access in your VB.NET project, convert this database to the previous version under another name • e.g., propmgt2.mdb • close the database

  18. SQL Server Query Editor • third-party tools can provide capabilities for SQL Server similar to those in Access • also see How do you use the Query Designer in SQL Server 2008?

  19. Using Queries in VB.NET • open VB.NET, start a Windows project • Data>Add New Data Source > Database > Next > New Connection > Data source: Change > Microsoft Access Database File > OK > Database file name: Browse > dbquery2000.mdb > Test Connection >OK > OK> [like to copy?] > Next [save the connection?] > Yes > Next > expand Views > [select combined query] > Finish

  20. Using Queries in VB.NET - 2 • drag and drop the Combined Query View from Data Sources onto form • expand form and DataGridView to show all fields • run the project

  21. Query Data Doesn't Update • data in this type of query: • come from multiple tables • include calculated and summary fields • changes entered by user in textbox do not go into database • queries OK for viewing data, or creating reports, but not good for data entry

  22. Queries as a DataSource • add a ListBox to the form • set the DataSourceName property • browse to find the [query]Binding Source • set the DisplayMember property • browse to find one of the fields in the query • run the code and note how ListBox selections navigate in DataGridView

  23. Expressions in DataSet Schema • VB.NET provides capabilities to create formulas inside the schema (.xsd file) • arithmetic, concatenation, decoding, etc. • less efficient for larger volumes of data, but provides more independence from data sources

  24. Stop Here • following slide worked for a previous Visual Studio version

  25. Using Queries in VB.NET - 3 • right click [da]>Generate Dataset • it may be helpful to rename it to ds • be sure to check Add this dataset to the designer, then OK • on Toolbox, click Data button • then drag a DataView onto the designer • in Properties window, rename it (dv1), set its Table property to ds1.QueryCombined

More Related