advice - query?

[DeanRowe]

Hi,

Before I post on here I always try to look back through the posts for similar problems – however I don’t know where to start looking to solve my problem.

I use access to store and record the details of my sales. As well as the prices, I also store the date of the sale.

I want to be able to add up the value of the sales for each day so I can see how much we sold – but I don’t know how to go about it. I’ve tried performing a query and then applying a filter by form – however when I click on the “Date” column, the drop down selection displays a list of identical dates:

12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
12/05/07
11/05/07
11/05/07

rather than just one date:

12/05/07
11/05/07
10/05/07

And when I click on one of the dates it only shows the data for that individual record, rather than the data for all the records on that date.

Could anyone please nudge me in the right direction? Should I be looking to perform a normal query? Or do I need to work on a different type/advanced query?

Eventually my intention is to create a form which links the queries to a report so I can print out the data, however for now I’d be overjoyed to simply be able to display the values in a table.

Any help would be greatly appreciated!
Thank you for your time.

 

[Michael J Ross]

Put your date and sales columns in the query, and make it a totals query by clicking the sigma icon , under your date column select group by and under your sales column select sum.

Thus will give you the total sales per day.

 

[weilerdo]

RE:Advise - Query

Hey Dean, First off you said you are using a drop down to do the query? Are you using a form that has a combo box on it. What you are wanting is pretty simple with a straight query and using a form with a date chooser on it. If you do a group by in your query it should take care of what you want.


Don

 

[DeanRowe]

Put your date and sales columns in the query, and make it a totals query by clicking the sigma icon , under your date column select group by and under your sales column select sum.

Thus will give you the total sales per day.

Hi Michael,

I've created a query in design view, added the date and the sales columns, I then clicked the sigma icon and it added the totals row with "group by" entered for both "date" and "sales" columns. I then changed the "group by" on the "sales" column to sum.

Now when I run the query it brings up the list as usual, with various entries for dates rather than an individual one for each date - and when I try to "filter by form" it still brings up the list of multiple dates
ie.
10/05/07
10/05/07
10/05/07
09/05/07

Should I be approaching this in a different way rather than filter by form?

Don - I haven't gotten to the stage of a form yet, I've been trying to get the query to work first - "What you are wanting is pretty simple with a straight query and using a form with a date chooser on it. If you do a group by in your query it should take care of what you want." - what do you mean by "date chooser"? I'm pretty sure I'm being an idiot and missing something really simple.

One thing that I forgot to mention before - until recently I had the "date" field record the time as well as the date. I thought this might be the reason for all the trouble, so to try and avoid this I've entered a seperate "Time" field and put input masks in the relevant fields... however when I go to the table that holds all of the data the "date" field is still holding the time as well as the date (I've entered new records so this new data has been entered using the input masks, however these records are still showing the time with the date in the "date" field - when I say "showing", its only when I click each cell that the entry expands and shows the time, otherwise it appears to only hold the date)

I'm not sure whether I'm being an idiot and it's something I'm not doing with the query, or if it's because the time is being entered aswell, or both - any ideas?

Thanks for your help guys, I appreciate the time you've taken to help.

 

[DeanRowe]

"Now when I run the query it brings up the list as usual, with various entries for dates rather than an individual one for each date - and when I try to "filter by form" it still brings up the list of multiple dates"

Sorry I've just re-read the post and this line implies its doing something different - it's always displayed various entries for dates.

 

[Michael J Ross]

Hi

One thing that I forgot to mention before - until recently I had the "date" field record the time as well as the date.

Thats where your problem stems from its grouping by every single date and time.

Remove your date field from the query and create a new field like the following:

NewDate:Format([nameofyourdatefield],"dd/mm/yyyy") replacing nameofyourdatefield with the actual name of your field and the date format to mm/dd/yyyy if youre using american dates.

Do a group by on the new date field and you should end up with one record per day.