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

MS Access Stock Control

P: n/a
Hi Guys,

I am doing a stock list of Printer tonors, with the following tables
Printer_tonor_Stock Tonors Printers
----------------------------- ------------- --------------
RecordID TonorID PrinterID
PrinterID Tonor Printer Name
TonorID Colour Serial Number
Date Stock Model
DepartmentID

I have done a form with th Printer_Tonor_Stock Table, basically what I
want is, when I fill out a record (combo box of TonorID), i want a
query (or 2) to say if the Printer_tonor_Stock.tonorID =
Tonors.TonorID, then Tonors.Stock should = Tonors.Stock-1.

How can I put this into a query pleeease Help!! (the results will be
shown at the bottom of the Printer_Tonor_Stock form). Also please
excuse my bad database design as I am just a lowly Helpdesk guy :o(

Thanks in advance

Phreake

Mar 30 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
I would change your database design slightly Phreake,

You should not be storing stock as a number. It should be a calculated
field.

Try the following site for example data-models:

http://www.databaseanswers.org/data_models/index.htm

They don't have an exact match but any stock control model should suffice.

Failing that try butchering the Tradewinds example database which ships with
access.

HTH John.


"Phreake" <ph*****@hotmail.co.uk> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Hi Guys,

I am doing a stock list of Printer tonors, with the following tables
Printer_tonor_Stock Tonors Printers
----------------------------- ------------- --------------
RecordID TonorID PrinterID
PrinterID Tonor Printer Name
TonorID Colour Serial Number
Date Stock Model
DepartmentID

I have done a form with th Printer_Tonor_Stock Table, basically what I
want is, when I fill out a record (combo box of TonorID), i want a
query (or 2) to say if the Printer_tonor_Stock.tonorID =
Tonors.TonorID, then Tonors.Stock should = Tonors.Stock-1.

How can I put this into a query pleeease Help!! (the results will be
shown at the bottom of the Printer_Tonor_Stock form). Also please
excuse my bad database design as I am just a lowly Helpdesk guy :o(

Thanks in advance

Phreake

Mar 30 '06 #2

P: n/a
Many thanks John

Mar 30 '06 #3

P: n/a
Many thanks John

Mar 30 '06 #4

P: n/a
Sorry Phreake, I should have said Northwind, not Tradewind...

Let us know how you get on.
"Phreake" <ph*****@hotmail.co.uk> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
Many thanks John

Mar 30 '06 #5

P: n/a
Hi John,

I have tried the Northwinds and the link that you sent me, but both
haven't really explained properly. A colleague has said that I need
two queries one to link the the two tables and one to decrease the
stock. Do you know where I can get sample sql scripts to point me in
the right direction? (northwinds only has Macros)

Thanks in advance

Phreake

Mar 30 '06 #6

P: n/a
Sending you an e-mail to ph*****@hotmail.co.uk which contains an access 97
DB.

It has three tables and a query.

Hope it points you in the right direction,

John.

P.S. File is zipped and password is Phreake
"Phreake" <ph*****@hotmail.co.uk> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
Hi John,

I have tried the Northwinds and the link that you sent me, but both
haven't really explained properly. A colleague has said that I need
two queries one to link the the two tables and one to decrease the
stock. Do you know where I can get sample sql scripts to point me in
the right direction? (northwinds only has Macros)

Thanks in advance

Phreake

Mar 30 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.