Need help calculating in access?

hi all,
im building a database for a take away and i have tables such as, CUSTOMER: cus id, ORDER: order id, cus id, order line id, date of purchase, time of purchase, ORDER LINE: order line id, order id, item id, quantity, ITEM: Item id, name, price..

i want to create reports which show for example the TOTAL sales for that day, week etc. based on all the order totals

ive created an invoice where i made an query to work out the sub total.. then in a report based on the query i calculated the total of the order "=sum([sub total])"

im just confused on how i would create reports on how to calculate total sales for the day or week.. like should i of done the whole order total in a query? If so how?

i hope this makes sense and can anyone who helps list how to do it in steps please. thank u everyone who answers

First off, you don't need [order line id] field in your order table: in fact you don't need [order line id] at all: you can create a combined key on [order id] and [item id].

Design a query with order and [order line] tables, and select field [date of purchase] and add a calculated field cost: [item price] * quantity: you don't have to select [item price] and [quantity] to use them in a calculated field.

In the criteria field under date, enter

month([date of purchase])=4

for April say, you change it for the month of your test data.

Now click on the 危 button in the tool bar: you should see a new row in the design grid called "total", with "Group By" entered as default.

Click on the Group By cell under Cost: it becomes a drop box: select Sum, and run the query: you should now get the totals for each day in the month of April.

Getting totals by the week are a little more complicated, and depends upon what you mean by 'week': add that info, and I'll see what I can do.

You can write complex calculations in VBA however will need some basic understanding of programming code.

The simplest way to do calculations in MS Access is using the calculation button within a query. Click the sigma on your toolbar (looks like an E) and you can choose to sum, count, average, or other calculations on your field.

Very useful!