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