extract certain data

[mtagliaferri]

I have a very simple question that I have not find the result or maybe I do not know what to look for!
I have a query containing a field named year and one named sales, what I need is to have a single result of total sales per year. example
year sales
2005 100
2005 100
2006 80
2006 70
2006 60

The result I would like to put then in a form is:
total 2005 200
total 2006 210

All this by simply running a query without indicating any parameters.

Thanks
M

 

[KenHigg]

This should be a simple totals query.

 

[jc1104]

Try this SQL statement in your query, where Table1 is the name of the table containing your sales data:

SELECT DISTINCTROW Table1.Year, Sum(Table1.Sales) AS [Sum Of Sales]
FROM Table1
GROUP BY Table1.Year;

Hope this is the answer you are looking for.

JC

 

[mtagliaferri]

Hi JC, can you give me further details, I am quite new in access, do I have to create a query, or do I have to work on my form, sorry to ask again, put this problem doing my head in!!
Marco

 

[jc1104]

Hi,

Sorry for the delayed response, was abroad on holiday.

You can simply put this into a query. (Remember to change the table name to the name that you are using!)

As you are new to Access, I hope this part isn't condescending but will explain how to create this query.

Assuming Access is open, click on 'Queries' within the object list to show you a list of any existing queries you may have.
Press 'NEW' and select 'Design Query' from the next selection window.
Your query window will appear with another window in front called 'Show Table'.
Hit cancel.
Then in the Query design window, press the right mouse button on the title bar and select 'SQL View' from the pop-up menu.
This window will change to a white screen.
It is in this screen that you should enter the SQL statement.

Again, I apologise if this is condescending but no harm done if you didn't know how to go about this.

Hope this helps,

JC

 

[mtagliaferri]

No problem, hope you had a nice holiday!!!! Thanx I have managed to get it done, have now 2 questions that you might be able to solve!!!
1 - I have a form wich I fill a field with the year (example 2006) can I get on another field the result of the SQL for year 2006 ( in a simple way as soon as I type 2006 on the same form have the total???)
2 - I have a DATABASE to track, as a airline employee, all trips with flights and crew members.... on the main form I have TABS showing detailed flights, hotel accomodation; but what I need is on one TAB just have a summary of the flights... its bit difficult to explain and I think I got to the top of my knowledge with ACCESS... can I attach you the database?
Thanks
Marco

 

[jc1104]

Again, sorry about the delay. Busy work and social calendar this month.

I'm not sure how you go about attaching the database, but if you do, I'll have a look at it. No promises that I'll be able to do anything but it's worth a go.....

JC