[Srecak]

:)
This is probably fairly simple, but I don't really know Access as much as I'd like to!
I have a form in which I write in the number of items sold in a day:
PU D; PU P; PU T - are just some of them. At the bottom of the form, there should be a field which shows the total, merely as a sort of 'control' point. I have tried to put a 'sum' formula as a default value in an unbound field on the Form, but I don't get anything that would give me a sum whilst keying in numbers. Any suggestions on how I should do this?

Many thanks,
S

[ScottGem]

I would need to know more about your table structure to answer this. I don't understand what you mean by PU D; PU P; PU T.

If you are recording item sales, your table should look something like this:

RecordID (PK Autonumber)
ItemID (FK)
SalesDate
SalesQty

You could then use a DSUM to calculate the total for a date.

[Srecak]

Hi!

The structure is very simple. There is a recordID (PK autonumber), the itemID shows each item seperately: hence the PU D(document), PU P(parcel) and PU T (transit) which are three different items. Then there's the PU Total which could be Yr 'sales qty' which at the moment I am manually adding each sales item and logging this number into this field. Instead of that, I would like the 'total' to add it's self up automatically, if possible. DSUM?

Hope this clearifies Yr questions...

Srecak

[ScottGem]

Actually it doesn't. Can you list the fields? You should not be storing a total. Calculations can be done anytime and shouldn't be stored.

If you want, you can make a copy of your database, zip it and attach it here and I'll take a look.

[Srecak]

Hmm, it seems I'm dumber than I thought:D
Attached is the database itself. Please give me a hand!
Also what would be a good book on MS Access?

Thanks
Srecak

[ScottGem]

Its not a matter of dumber. It's a matter that database design is an art that has some very specific rules that should be adhered to to create an efficient design. The rules aren't hard to learn, but they need to be learned.

You need to redesign your database along the lines I've already suggested. Here are some things you need to change.

1) Do not use spaces (or non alpha characters) in object names. This will come back to haunt you. This article lists some of the common naming conventions:
General: Commonly used naming conventions

2) Do not use multiple Primary keys. This will cause you no end of problems when creating queries. Use an autonumber PK for all tables. Do not use text fields for PKs, they index more slowly so inhibit performance in joins

3) Do not use fields to describe values, use records instead. You have a fields for your items (PU D, P, T). Look at the table strucutre I suggested. You should have a field for Item so that each item is a record.

4) Don't store calculated values. You can perform your calcs on the fly and that insures the most accurate numbers.

5) Date is a reserved word in Access and should not be used as an object name.

Again, go back to my previous response which gave you the info you needed. Add an ITEM table, change the tables as indicated. Lookup DSum in Access help.

As for books, I use the Sybex Developer's Handbook. But that is a bit more advanced. If you need something basic the Dummies book is pretty good.

[Srecak]

Scott!
This is great! I shall put this into practice straight away, and send You updated version when I'm done.
Many thanks and I've also ordered the book,
Srecak

[ScottGem]

I'll be here, glad to assist