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:

More topics

 

Access 2010 query error

Preview
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.
 

Object spacing tools broken in Access 2010

Preview
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 2007 / 2010 problems

Preview
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

Preview
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
 

Free MS Access Tutorials for Beginners

Preview
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.
 

Cumulated values

Preview
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

Preview
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

Preview
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.
 

Cant get Yes/No to work in Query Access 2010

Preview
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
 

Move access 2010 screens to Desktop

Preview
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.