MS Access Stock Control

[Phreake]

Hi Guys,

I am doing a stock list of Printer tonors, with the following tables
Printer_tonor_S tock 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_S tock 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_S tock.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_S tock form). Also please
excuse my bad database design as I am just a lowly Helpdesk guy :o(

Thanks in advance

Phreake

[John Ortt]

Re: MS Access Stock Control

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" <phreake@hotmai l.co.uk> wrote in message
news:1143714918 .673460.293590@ e56g2000cwe.goo glegroups.com.. .[color=blue]
> Hi Guys,
>
> I am doing a stock list of Printer tonors, with the following tables
> Printer_tonor_S tock 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_S tock 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_S tock.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_S tock form). Also please
> excuse my bad database design as I am just a lowly Helpdesk guy :o(
>
> Thanks in advance
>
> Phreake
>[/color]

[Phreake]

Re: MS Access Stock Control

Many thanks John

[Phreake]

Re: MS Access Stock Control

Many thanks John

[John Ortt]

Re: MS Access Stock Control

Sorry Phreake, I should have said Northwind, not Tradewind...

Let us know how you get on.


"Phreake" <phreake@hotmai l.co.uk> wrote in message
news:1143725600 .339587.224080@ j33g2000cwa.goo glegroups.com.. .[color=blue]
> Many thanks John
>[/color]

[Phreake]

Re: MS Access Stock Control

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

[John Ortt]

Re: MS Access Stock Control

Sending you an e-mail to phreake@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" <phreake@hotmai l.co.uk> wrote in message
news:1143731786 .394831.51960@i 39g2000cwa.goog legroups.com...[color=blue]
> 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
>[/color]