Counting specific records

[mattyb]

Hi,

I'm pretty much a novice on Access so please go easy on me!

I have a little database that i set up to keep track of my finances on a four weekly basis (ties in with my pay) although the only way I do this is create new queries with the correct dates.

All the data is on 1 table with the following information

ID | Type | Category | Price

There are 12 Categories, eg bills, food etc

Basically I want to run a report for a four weekly period that lists each category and how much I've spent on each followed by a sum of all them from the Price field. I'm really struggling with this and the help function isn't much help!

Any ideas???

Thanks

Matt

 

[checoturco]

i dont know if i understood well you question but if you wanna sum the total price of eache category you can do a query with agrupate function, like this.

SELECT category,sum(price) from <table_name> group by category;

if you wanna the category and each price and then the sum of category, i just know the functions cube and rollup of the SQL.

checoturco

 

[daherb]

in the report you can create an unbound field and use the following code to obtain the data.

=Sum([Price])

put this field in the report footer and it will add up all your price entries.

with regards to setting the dates to show i would enter something like a date field to the table. that way in a query you can create the following criteria code

Between [Start Date] And [End Date] (enter this exactly)

then when you run the report the system will ask you to enter the start date such as 01/09/05 and the end date 30/09/05. then the report will only show the information for september without having to re-create queries month after month.

 

[mattyb]

Daherb

Thanks for replying. Unfortunately I can already sum all the price. What I want to do is only sum each individual category. Example, this is what I currently have
Category Price
Food 3.00
Food 2.00
Going out 40.00
Going out 40.00
Total 85.00

What I want is this though

Total Food 5.00
Total Going out 80.00

Total 85.00

Does this make more sense?

Ps Thanks for the date thing, a lot better than what I was doing!!!!

 

[Pat Hartman]

It is best to do totalling and grouping in reports. When you attempt to do them in queries, you are restricted to a single level.

From your example, it looks like you are including some detail item, such as date or ID, that is preventing the amounts from aggregating by category.

 

[mattyb]

Okay so.......

.... yes I have been including the date (but not id). So do I use the date in the query to get the information for the report but then not include it in the report itself? How does that work.

Really sorry about this but I'm a complete novice!

 

[Rich]

The Report wizard will build most of this for you, just add a grouping level for Category, the wizard will give you the option of Summing each group

 

[mattyb]

nearly there now.......

So I have 2 different things I want

1) A query that I can enter Start date and end date instead of creating new queries
2) A report that sums (groups) the categorys I want

However how do I get a report that does the above but for a specific period.

This is my last question (probably) then I'll go away.

Matt

 

[Pat Hartman]

Create the query and use it as the RecordSource for the form rather than a table.

 

[mattyb]

Thank you all

who replied to me. Much appreciated guys

Matt