Topics Search

Access 2010 Designing a Query

Access 2010 Designing a Query
Views: 65
The real power of a relational database is in the ability to quickly retrieve and analyze your data by running a query. Queries allow you to pull information from one or more tables based on a set of search conditions you define.
In this lesson, you will learn how to create a simple one-table query. Then you will learn how to plan and run a slightly more complex multi-table query.
Sponsored Links:

Similar posts...

 

Access 2010 query error

Access 2010 query error Icon
I was working with a DB in Access 2010 and went to modify a query. However when you click on the query the View button on the Ribbon does not light up. You have to run the query then the button becomes active.

Well, this query has an error due to linking with an Excel workbook. I can't find a way to get into the query to fix the problem, at least in 2010. I fired up 2003 and had no problem selecting the query then clicking on Design.
 

access 2007 / 2010 problems

access 2007 / 2010 problems Icon
I have made a database with forms, query's and reports in access 2007. When I run it on access 2010 strange things happens.

This is the problem:

I have some queryes that sort out some records. I call these query from event on change from text boxes and combo boxes on a form. The problem is that it opens the query. I like to run this query in the background and then run a report based on this query.

How to fix this.

It works fine in access 2007 but not in access 2010.
 

Free MS Access Tutorials for Beginners

Free MS Access Tutorials for Beginners Icon
In Microsoft Access you can create different views of data using a select query, create a form in Microsoft Access and use it to enter and edit records, re-organise records in datasheet view using sorts and filters, create tables in Microsoft Access 2010 and few hints and tips about designing your database table.
 

Access 2010 Query parameters

Access 2010 Query parameters Icon
Query in Access 2010, Between Date() and Date()+1. This worked in 2007 but in 2010 it adds quotes around the 1 and does not work. My dates are date and time so I have not been able to just set date.
 

Cumulated values

Cumulated values Icon
I have a table that goes like this

Name Day Amount
Mike 01/09/2010 +12
Mike 02/09/2010 +19
Mike 03/09/2010. -8
Mike 04/09/2010 -15
Mike 05/09/2010 +22
Paul 01/09/2010 +13
Paul 02/09/2010 -9
Paul 03/09/2010 -8
Paul 04/09/2010 -25
Paul 05/09/2010 +12

[Real table has about 500.000 records]
and I need to calculate, for each name, the cumulated amount and the day over day % amount increase, like this

Name Day Amount Cum %
Mike 01/09/2010 +12 +12 n/a
Mike 02/09/2010 +19 +31 +58% Mike 03/09/2010. -8 +27 -142%
Mike 04/09/2010 -15 +12 +87%
Mike 05/09/2010 +22 +34 -246%
Paul 01/09/2010 +13 +13 n/a
Paul 02/09/2010 -9 +4 -169%
Paul 03/09/2010 -8 -4 -11%
.and so on

How can I achieve this?

This is what I have done:
Make a crosstab query with name on rows, day on columns and amount as value.
This reduces the number of records to about 8000 thus allowing an export to excel.
.But I don't like it! I'm sure access can do this
 

Access and SQL: Dynamics Reports

Access and SQL: Dynamics Reports Icon
An Access Report is similar to an Access Form. It displays data from a record source you specify (a table or query) and you can customize the way the data is displayed through its design. In fact designing a report uses the many of the same techniques as designing a form. Forms are chiefly used for entering and editing data whereas reports are used for viewing and printing data.
 

Access 2010 More Query Design Options

Access 2010 More Query Design Options Icon
Access 2010 offers many options that let you design and run queries that return exactly the information you're looking for. For instance, what if you need to find how many of something exists within your database? Or what if you would like your query results to automatically be sorted a certain way? If you know how to use Access's query options, you can design almost any query you want.
In this lesson, you'll learn how to modify and sort your queries within Query Design view. You'll also learn how to use the Totals function to create a query that can perform calculations with your data. You'll also learn about additional query-building options offered in Access.
 

Relationship & Query Interconnect

Relationship & Query Interconnect Icon
Access 2010 is very useful tool for designing database and provides intermediate-level data mining. It has done and continues to do wonders especially when it comes to managing, creating and most importantly making use of Relational Database up to its acme. This post will cover how relationships and queries interconnect to make database more dynamic.
 

Help access 2010 to 2003 vba and sql problem!

Help access 2010 to 2003 vba and sql problem! Icon
And a sql statement in the criteria part of my query.

Everything was working fine in access 2007 and 2010, when I converted it in access 2007 to access 2003 format when I open my form it keeps asking for a parameter, which is point to the textbox that the query criteria is based on.

I am supposed to implement this on our computers at work tomorrow, but I've got to get it working in access 2003 format.

The 2007/2010 and the 2003 access files. A professional could probably look over it in 5-10 min and be able to tell me what is wrong.
 

Circular One-to-Many Relationship

Circular One-to-Many Relationship Icon
I am a self-taught Access user and have been put in charge of designing a database for work. I am running Access 2010. and then today I hit a stumbling block. So:

1) I am designing a DB to track Projects for a consulting firm.

2) The area that I am having trouble with are these three tables:
-Projects (PK-[Project ID])
-On-Call Contracts (PK-[On-Call ID])
-Amendments (PK - [Amendment ID])

3) My relationships are: An On-Call Contract can have many Projects, and both a Project and an On-Call Contract can have many Amendments. (One-to-many)

4) In a form showing amendment info I can't seem to display either Project or On-Call as wires have been crossed or something.