Noob help please (1 Viewer)

[anthonyphillips]

Ok guys I have a quick question.

The Background
I have created a very simple database so that I can enter takings from the shop on a daily basis. i have put income and expenditure into the same table then created seperate forms for data entry.

The questions
I am looking for a way of auto summing AM takings and PM takings and output a value into the total takings box.
In turn i then want to take that value per day, sum it for the week, then have the total takings less total expenditure to give total profit or loss figures.

Is this possible.

thanks

Anthony

 

[DCrake]

When you save a transaction to your table you will need a date/time field that has the default value set to Now() for all new records. This will record that date and time that the transaction took place. You can then use this field to calculate what part of the day the transaction took place.

If you are storing the income and expenditure in the same transaction field you will also need to be able to identify what type or transaction is was. such as adding a field called TransType and coding the entry as per the transaction type. This way you can use a query to isolate each type.

David

 

[anthonyphillips]

Sorry my question wasn't clear.

I have a 3 fields
AM Takings - So in here we enter total AM takings (£100.00)
PM Takings - So in here we enter total PM takings (£100.00)
Total Takings - This field will sum the AM and PM takings (£200.00)

Thanks Anthony

 

[DCrake]

You don't need a third field for total takings as this can be calculated from the other two in a query. So in your query you would group by date/week/month summing the am takings and pm takings and have a further column that sums both the am and pm takings together.

David

 

[AngelaP]

Thanks for this. I am a newbie and this thread helped me a lot!

 

[mousemat]

David is right in what he says.

You really only need one table and one form. The form will allow you to enter EITHER income or expenditure transactions. The date & Time will be automatically recorded in the table in a field called transDateTime.

Table will be along the lines of the follwing:

ID
TransType Either Income or Expenditure Dropdown List
Amount
TransDateTime Default Value = Now()

In order to get the values for the morning and afternoon, a simple query will tell you.

 

[anthonyphillips]

ok i will explain this as best I can but please remember I am a very very nooby access bloke.

I have one table
I have one form
I have several fields in the table of which three of these are AM Takings, PM Takings and then total takings.

I want to achieve something that is (or so i thought) simple and that is to have the values that are entered into the AM takings, we'll say £100 and then the value in PM Takings, again we'll say £100 to add together and show £200 in the total takings box.

Thanks

Anthony

 

[mousemat]

Have a look at the sample database I have created for you.

Its very basic in design and there are much much better ways of doing it, but at least it shows the basic queries you need.

The totals form will currently show all dates, not just one specific date so you will need to think about that.

Database saved as 2002 - 2003 format