Access 2010 Designing a Query

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:

Related Topics

Object spacing tools broken in Access 2010
I have recently installed and started using access 2010. So far I like it a great deal better than 2007, however I encountered a pretty nasty problem. When designing my forms, I rely heavily on the Size/Space tools to organize and align my objects. The spacing tools however seem to be broken, particularly the Horizontal once. In access 2007, I could select a number of controls, hit Equal Horizontal, and have the horizontal distance between the objects the same. Then I would use Increase/Decrease Horizontal to further tweak it. In Access 2010, none of this seems to work. Can anybody using Access 2010 confirm that this is indeed a bug, and if so what are some potential workarounds


Access 2010 query error
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
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.


Create new filed based on listbox selection
Can I create a new field during a query named for the selections in a listbox?

I have a list of dates and the user will select two, such as 10/30/2010 and 11/04/2010. I need the query to run with two new columns called 10/30/2010 and 11/04/2010 which will be populated with quantities from another part of the query.

I am currently doing this in Excel VBA/SQL but can't seem to translate it to Access


Cumulated values
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 equiv to the vLookup in a Query
How do I perform a lookup in a query?

Essentially I am wanting to perform what in excel would be a vlookup function where I pull the information from one query and put it into another query using the date as the lookup criteria.

Eg: Query 1 has the information and Query 2 is where I want to put it.

Qry 1
Date Value
19 May 2010 996.00
21 June 2010 1101.01
31 August 2010 701.03

Now in Query 2 I want to perform the lookup so the result will look like this

Qry2
Date Value
20 June 2010 996.00
21 June 2010 1101.01
22 June 2010 1101.01


Access 2010 Query parameters
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.


Relationship & Query Interconnect
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.


Cant get Yes/No to work in Query Access 2010
I just installed Access 2010 and for some reason my query of table that has a check mark ( yes/No) field wont work. All I get is a fault tone. I ran same query in 2003 and worked fine


Circular One-to-Many Relationship
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.


Move access 2010 screens to Desktop
I have an Access 2010 Table with a data entry form and two inquiry screens. I have to install these on a user's computer who also has Access 2010. The user needs for the Data Entry and two Query screens to be icons on his Desktop such that when he clicks on any of the three he sees only that particular screen without even being cognizant that Access 2010 is driving it all. He doesn't need to see any of Access 2010 bars, commands, etc.


Access 2010 Designing Your Own Database
Now that you know how to use and modify existing databases, you might be interested in designing your own. Database design can be very complicated-- so complicated, in fact, that people take extensive courses just to learn how to plan databases. For that reason, we haven't focused on creating a database from scratch in this course. However, we can help you get started.
In this lesson, you will learn how to create a database from an existing template. You will also learn about other resources you can use to learn database design.


Access 2010 not responding when viewing query
I am using Microsoft Access 2010 to view a query in a Access 2007 format database. When I try to open a query, Access is "not responding" and I have to close out and restart.


Specifying Date Format for Column Heading in Crosstab Query
Under Access 2003, the simple expression, attached below, allowed me to include the day of the week in the date field (column heading) of a cross tab query (Sunday, 12/25/2010 versus simply 12/25/2010).

Under Access 2010, I get the "There is no message for this error" when trying to open the query.

Recreating the object and code does not solve the problem.

Is there some new syntax that is required?


Help access 2010 to 2003 vba and sql problem!
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.


Access 2010 More Query Design Options
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.


Upgrading from 2000 to 2010
Is there a good reference to explain some of the differences in the 2010 version of MS Access? I have a database in 2000 that has ODBC calls to download multiple tables, execute a query, and output the data to a new table but each time I run it in 2010, Access crashes. I have gone through the save and publish steps but it still does not work.


A very simple query re totals........
At work, we time when our referrals come in and at the end of the year, we have to produce charts to show things like this. The query I've developed shows me how many referrals we get between each hour block (e.g. 07.00 - 07.59) which is fine, but I would also like it to total up the grand total of referrals at the bottom of that column.

Basically, this is my code (don't laugh, I'm a total newbie and it's very basic).

SELECT [Referrals 2010].Assess, Count(*) AS [Number], [Referrals 2010].Time
FROM [Referrals 2010]
GROUP BY [Referrals 2010].Assess, [Referrals 2010].Time
HAVING ((([Referrals 2010].Assess)="No") AND (([Referrals 2010].Time) Between "07.00-07.59" And "19.00-19.59"));

I can add an autosum into my 2007 Access at home, but not on the 2003 Access I'm running at work. Can someone tell me how to make it do this simple little thing please


Access 2010 auto insert from field above issue
I have just upgraded to office 2010 and I have noticed that in my Access Database, that it no longer auto inserts the next value in sequence in a column of records -

I am a basic user - no programming involved.

Basically, prior to Access 2010 if I had a entered information via a query, and one of the columns was a blank field. If I started the 1st record with say 101 and then cursored down to the same field in the next record and entered 102, a further scroll down would automatically add 103 and so forth.

Access 2010 does not perform this natively - and I have been using Access since Version 1 and it is the 1st time I have come across this.

I could not locate anything in the (very limited) Access Help and I cannot explain why this has changed.


Access 2010: How To Insert Rows / Columns In Query Design
Access 2010 offers almost every control on the ribbon which you might need to use while setting up query in query design mode. Many a time while writing conditions for setting up query in a required way, new rows and columns are to be added. This post covers how to add new rows and columns in a query design.