Help with adding costs up for each machine

[cretam]

Hi,
I have sent up a simple table with the following fields:
ID
Date
Time
Machine
Description
Supplier
Parts Description
Cost



Can someone pls tell me how to get a total of costs for each machine?

Any help would be greatly appreciated.

Thanks.
Tammy

[pbaldy]

A totals query

SELECT Machine, Sum(Cost) As TotalCost
FROM TableName
GROUP BY Machine

[rzw0wr]

Welcome Tammy.

Some more information is required.
Where do you want to do the calculations?

What defines the cost of your machines?

Dale

[ssanfu]

Also be aware that "Date" and "Time" are reserved words in Access and shouldn't be used as object names.

Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html

You might also read about naming conventions:
http://access.mvps.org/access/tencommandments.htm (#3)
http://access.mvps.org/access/lookupfields.htm

Only use letters, numbers and the underscore in names; spaces and special characters in names are not "good".

[cretam]

Thanks pbaldy - you solved my problem. I will change the objects to name them correctly ssanfu - thanks for the tip.
I have setup some reports to display the results.
Thank you again for your help - much appreciated.

[pbaldy]

No problem. Welcome to the site by the way!

[cretam]

Hi, Sorry to bother you again pbaldy, but would you mind helping me again? I need to be able to do the query below but also select the dates ie. 1/7/12 to 30/6/13. How would I do this?

A totals query

SELECT Machine, Sum(Cost) As TotalCost
FROM TableName
GROUP BY Machine

[pbaldy]

This type of thing, though more commonly done with a form so the user can specify the dates.

SELECT Machine, Sum(Cost) As TotalCost
FROM TableName
WHERE DateFieldName Between #7/1/12# And #6/30/13#
GROUP BY Machine

[cretam]

Thank you for your help. I have been trying to find a good example with a form but am not having much luck. I even searched the forum and got a book from the library but no luck. Could you point me in the right direction please as I think it would be better if the user can select the date range ie. might select each month?

This type of thing, though more commonly done with a form so the user can specify the dates.

SELECT Machine, Sum(Cost) As TotalCost
FROM TableName
WHERE DateFieldName Between #7/1/12# And #6/30/13#
GROUP BY Machine

[cretam]

Thank you for your help. I have been trying to find a good example with a form but am not having much luck. I even searched the forum and got a book from the library but no luck. Could you point me in the right direction please as I think it would be better if the user can select the date range ie. might select each month?

[orange]

For well documented and step by step tutorials see Martin Green's site at
http://www.fontstuff.com/access/index.htm

[cretam]

Thank you for your help - that worked well.

This type of thing, though more commonly done with a form so the user can specify the dates.

SELECT Machine, Sum(Cost) As TotalCost
FROM TableName
WHERE DateFieldName Between #7/1/12# And #6/30/13#
GROUP BY Machine

[cretam]

Thanks for the link. These tutorials are excellent.

For well documented and step by step tutorials see Martin Green's site at
http://www.fontstuff.com/access/index.htm