By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,491 Members | 1,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,491 IT Pros & Developers. It's quick & easy.

URGENT: Numeric Calculations in Access - Access n00b Question

P: n/a
URGENT

I have an assignment to hand in tomorrow, I need to know how to perform
numeric calculations in access, I dont know very much about databases
so consider this a beginner's query!

Well basically this is what I'm aiming at, its nothing too complicated
really.

I have a table called RESOURCES which keeps track of all of the stock
the company has (e.g. how much paper, how many mugs, tshirts etc.). I
want the database to be able to automatically take 1 away from the
selected resource when a form is used to file a new order.

E.g.

I have a form asking me the details of the design, In the resources
table I have set the quantity of mugs as 100, i.e. I have 100 mugs in
stock and available.

I use the form and tell the database that I am making a design which
will use 1 mug.

The database then goes to the resource table and takes 1 away from the
available stock of mugs making the total mugs available 99.

I then the database to be able to do this automatically, when a
checkbox is ticked, when the form is submitted I want it to see which
text boxes are ticked and take the correct consequent resources from
the RESOURCE table.

I have Access 2003, I have attached the file incase anyone needs it,
note that the Forms have NOT been made only the resource, customers and
orders tables have been created.

If someone could tell me how to do this I'd be extremely greatful, this
is very URGENT I have to hand in the project on Monday!!!

I know I'm asking alot but I'd be really really greatful if someone
could help me out!!!

Cheers,

//Xeijin

Mar 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Xeijin wrote:
URGENT

I have an assignment to hand in tomorrow, I need to know how to perform
numeric calculations in access, I dont know very much about databases
so consider this a beginner's query!

Well basically this is what I'm aiming at, its nothing too complicated
really.

I have a table called RESOURCES which keeps track of all of the stock
the company has (e.g. how much paper, how many mugs, tshirts etc.). I
want the database to be able to automatically take 1 away from the
selected resource when a form is used to file a new order.

E.g.

I have a form asking me the details of the design, In the resources
table I have set the quantity of mugs as 100, i.e. I have 100 mugs in
stock and available.

I use the form and tell the database that I am making a design which
will use 1 mug.

The database then goes to the resource table and takes 1 away from the
available stock of mugs making the total mugs available 99.

I then the database to be able to do this automatically, when a
checkbox is ticked, when the form is submitted I want it to see which
text boxes are ticked and take the correct consequent resources from
the RESOURCE table.

I have Access 2003, I have attached the file incase anyone needs it,
note that the Forms have NOT been made only the resource, customers and
orders tables have been created.

If someone could tell me how to do this I'd be extremely greatful, this
is very URGENT I have to hand in the project on Monday!!!

I know I'm asking alot but I'd be really really greatful if someone
could help me out!!!

Cheers,

//Xeijin

I haven't a clue about your checkbox names, the text field names, etc.

Let's say you have on your form a textbox called Qty that displays the
current Qty on hand. You have another text box called QtyUsed that
indicates the number of mugs to use.

You could create a SQL statement similar to
If msgbox "Are you sure you want to update the Qty On Hand?", _
vbYesNo,"Confirm TableUpdate) = vbYes Then

strSQL = "UPDATE TableName " & _
"SET TableName.Qty = " & _
"[TableName].[Qty] - " & Me.QtyUsed & " " & _
"WHERE TableName.ID = " & Me.ID
Currentdb.Exectute strSQL
Endif

Is your form static as far as the items to be listed/updated? For
example are there tic boxes for each item to be used? Then you can use
a bunch of if/endifs

If Me.CheckMug = True then
strSQL = "UPDATE TableName " & _
"SET TableName.Qty = " & _
"[TableName].[Qty] - " & Me.QtyUsed & " " & _
"WHERE TableName.ID = " & Me.CheckMugID
Currentdb.Exectute strSQL
Endif

If Me.CheckShirt = True then
strSQL = "UPDATE TableName " & _
"SET TableName.Qty = " & _
"[TableName].[Qty] - " & Me.QtyUsed & " " & _
"WHERE TableName.ID = " & Me.CheckShirtID
Currentdb.Exectute strSQL
Endif
etc
Mar 26 '06 #2

P: n/a
Br
Xeijin wrote:
URGENT

I have an assignment to hand in tomorrow,
Left it a bit late haven't you?

Personally I don't think this forum is for people to do your homework for
you.....

<>
... I have attached the file incase anyone needs it,


Do not attach files unless you are posting to a binaries group.

<>
--
regards,

Br@dley
Mar 27 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.