Cis 338 using queries in access as a recordsource
1 / 25

CIS 338: Using Queries in Access as a RecordSource - PowerPoint PPT Presentation

  • Uploaded on

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'CIS 338: Using Queries in Access as a RecordSource' - rocco

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

What is a query
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

Initial database set up
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

Creating queries for totals
Creating Queries for Totals

  • click Create tab

  • click Query Design

  • select Payments table, click Add

  • Close the Show Table window

Creating queries for totals 2
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

Creating queries for totals 3
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

Creating queries for totals 4
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

Creating queries for totals 5
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)

Setting up a combined query
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

Combined query 2
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

Combined query 3
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

Combined query 4
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

Create calculated columns
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

Create calculated columns 2
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

Create calculated columns 3
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 [ ]


Sql code
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)

Ending database set up
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

Sql server query editor
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?

Using queries in vb net
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

Using queries in vb net 2
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

Query data doesn t update
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

Queries as a datasource
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

Expressions in dataset schema
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

Stop here
Stop Here

  • following slide worked for a previous Visual Studio version

Using queries in vb net 3
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