"Access" Databases

[dcaccounting]

Ok I am now pullying my hair out!

I have built a database, but I am trying to put in a formula much like you would in excel... is it working, NO!!! Grrr

Is there any microsoft access wizards out there???

Bascially im trying to say "find this box/cell then whatever is entered in there divided it by "?" and then times it by "?"

=sum(boxname/"?")*"?"

HELP!



Regards

Dean

 

[Stephen]

Whereabouts are you trying to do this in Access - in a form designer or a query? (You don't want to do this in a table, as you'd store 'derived data' - not normally a good idea.)

 

[Whistle Ink]

I didn't know you could stuff like that in Access!

 

[creospace]

Maybe an web based databse would suit you more, certianly better than access? This is my specialist area but I confess I know little about access.

Or why not try here?

http://www.access-programmers.co.uk/forums/

 

[dcaccounting]

I am doing it in a table.

Think i'll try the link posted, thanks. Failing that, im chucking it out the window :lol:

Regards

Dean

 

[Stephen]

I think it unlikely you actually want to do this in the table. Broadly, databases should store the underlying values, but not the result of any calculations on the data. That way, it's more efficient. (There are occassions where you might want to store derived data, but they're quite specific and technical.)

What I think you might want to do is store the basic data in a table, then create a query. You can use the query design view: select the table, then the fields, then in the columns you can add a formula something like Col1 / 3 * 4. Don't use the word SUM, as that has a specific meaning in Access that is quite different to Excel.

 

[Enigma121]

Dean,

Mathematical functions can be stored within a database. In MS Access this is known as a "Query" other databases call this a view

This represents a way that data is presented which is derived from the original data. Using a "Query" for example you can add together two table columns and subtract a third to produce a final result for each row.

You use an "Expression" again an MS Access term, to store the specific calculation that you want.

Does this help your understanding here?

 

[dcaccounting]

You use an "Expression" again an MS Access term, to store the specific calculation that you want.

Does this help your understanding here?

Yes, yes thats it. It is in table but then put to design mode.... then i click on the cell I want to put in an "expression" called "default value" click on the [...] box and this is where I am putting the formula.

Not working though :cry:

Regards

Dean

 

[DuaneJackson]

What expression have you entered?

Feel free to email me the database with a outline of what you're trying to do, I'll get a chance to look at it later this evening once the littl 'un is in bed. Should only take a couple of minutes.

duane at keyone.co.uk

 

[dcaccounting]

Hopefully you have mail.

Now started learning "pivot tables" that might also do what I want

Regards

Dean

 

[Enigma121]

Dean, the problem is you need to use a Query and not a table. Tables just store raw data, queries can carry out all sorts of calculations on top of this....

 

[DuaneJackson]

The problem is that the report used a long SQL expression as it's data source when really should have used a Query.

I've updated it and sent it back to you with an explanation Dean.

Hope it helps!

 

[dcaccounting]

I know why I play with numbers now!

Regards

Dean

 

[Enigma121]

I know why I play with numbers now!

Regards

Dean

Sweet.

Now just explain it to the folks that think we should do database design for a buck 'o' five an hour...

 

[webster]

i'm not sure if this helps, but its a good guide:
http://databases.about.com/library/weekly/aa122400a.htm

=Sum([Figures])

 

[webster]

I'm sure this may help:

http://databases.about.com/library/weekly/aa122400a.htm
http://databases.about.com/cs/tutorials/
http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm

 

[BusinessIdeas]

No not pivot tables You just need to make a new query from the table and add a new calc field or if you want a constant you could make a new table with the constant in and add it to the query then you make a new field in the query with a : after the title as in newfield: [nameofexistingfield]*[constantfield] obviously use the names that you have named the field not my names then the query will multiply the two fileds it can be a little more complicated in that the fields format have to be correct. i hope this helps