Access 2010 Designing a Query
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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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.
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
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
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.
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
20 June 2010 996.00
21 June 2010 1101.01
22 June 2010 1101.01