Summing totals for a given week number

[not a code fan]

Hi

I am a beginner to Access and don't get on very well with Access terminology so please be gentle.

I have a database that stores shop sales details. There are 11 shops and some of the fields include Date, Week number, ladies clothes sales, mens clothes sales, etc. Sales data is entered for almost every day of the week so each shop will have 5 or 6 records for a week.

In a query i want to be able to filter a given week and show only one row for each shop for that week. I want that one row to be a total of all the records for that shop for that week. I also want to filter by year as previous years used the same week numbers.

So far i have done all of the above apart from total sales records for a week into one row.

Any easily explainable answers on offer?

 

[Rich]

Why are you storing the Week number when you already have the date of sale?

 

[not a code fan]

I have a week number field because it is the only way i know to group all daily records into weeks. Now that i can group daily records into weeks i want to sum them as described above.

 

[Rich]

You can get the week number in a query from the given date field, use a totals query to group by that field and Sum on Sales, you can also grab the Year from the original date field and restict the data to the current year, or any year that you have records for

 

[not a code fan]

I think you are describing what i have already done. Something hasn't worked though. I have attached a screenshot which shows what i have in design view. The result of the criteria in the design view gives almost what i want but instead of getting one week for each shop and it's total i get every day of the week.

If i take out the criteria which picks out only sales from a certain year, say 2004 i get one row for each shop but the total sales are not based on the year i want they are based on about 3 years of data that i have in the table.

Below is what i have with the criteria for the year in effect. As i said this not what i want, i want a total of the days to give a weekly figure for each shop.

Shop Name Date Week Number Totals SumOf1 Ladies
Barnstaple 05/04/2004 1.00 £154.75 £52.10
Barnstaple 06/04/2004 1.00 £179.05 £68.85
Barnstaple 07/04/2004 1.00 £163.09 £60.70
Barnstaple 08/04/2004 1.00 £138.85 £55.35
Barnstaple 10/04/2004 1.00 £189.95 £55.45
Bridgwater 05/04/2004 1.00 £135.05 £48.05
Bridgwater 06/04/2004 1.00 £142.75 £34.30
Bridgwater 07/04/2004 1.00 £120.15 £44.05
Bridgwater 08/04/2004 1.00 £175.60 £68.90
Bridgwater 10/04/2004 1.00 £299.80 £38.70

 

[Rich]

Having quickly looked at your table it is apparent that your structure is flawed, in simple terms you only need one field for the sale and another which defines what the item sold is, those item definitions would come from another lookup table.

 

[not a code fan]

To change the form to how you describe would mean some work, for which i haven't got time right now (but will look into later). Are you saying it is not possible to achieve what i want with with the query the way my data is structured?

Also if i were to use lookup boxes for each of the 10 categores it would add considerable time to entering the data as at the moment the category is predefined requiring me to merely enter the number and then press tab to move to the next category rather than choose the category type each time.

If my way is not suitable then are there any less laborious ways of entering data than the way you suggested?

 

[not a code fan]

Rich

Any more suggestions in light of my last post?