473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MS Access Stock Control

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
6 7577
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
Many thanks John

Mar 30 '06 #3
Many thanks John

Mar 30 '06 #4
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Cady Steldyn | last post by:
Example: Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty ------------------------------------------------------------------ 12/09/2003 | A100 | 20 | 0 ...
10
by: Michel | last post by:
Hi, We are from Belgium and are looking for someone who could write us a point-of-sale (POS) software for a DVD-film shop. I like it to be in MS-Access with some VBA or just VB. The...
4
by: Michael John | last post by:
Dear Oracle Developers, my task is to make up a Oracle View from a Pivot table in MS Access. Given are two tables to join: T_FIRM: FIRM_ABBR VARCHAR2(3 BYTE), FIRM_LONG ...
22
by: script-learner | last post by:
I currently own a fruit and veg business and wish to compuiterise my manual methods in order to reduce labour costs. I spoke to my son who is a whiz on computers and he had a good chat with me and...
17
by: DaveG | last post by:
Hi all I am planning on writing a stock and accounts program for the family business, I understand this is likely to take close to 2 years to accomplish. The stock is likely to run into over a...
13
by: royaltiger | last post by:
I am trying to copy the inventory database in Building Access Applications by John L Viescas but when i try to run the database i get an error in the orders form when i click on the allocate...
7
by: ddecoste | last post by:
I have a need to add a visual representation to some data in Access. I need to draw a matix of squares inside another square. I have all the data that I need in a record in Access. The data...
0
by: aman909 | last post by:
Im trying to use some vb code in a form which simply changes font colours. It is for a stock control system. The stock control system includes three items: colour, stock and minimum stock. The...
0
by: MrDeej | last post by:
Hello! I am working on a project wich should make our stock control much easier. My idea is to use a Motorola MC70 (PDA with 320x240, scanner, WIFI and windows mobile) to connect to an SQL...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.