Question Paper Usage - stock

[joe90]

Firstly can I thank you for reading this and helping in anyway you can.
I have 2 tables (below), I am trying to find the best/correct way to return the desired results. I work for a small printers . I have been set a task to create (what I thought would be relatively simple) a database that will show what paper we are using on what jobs etc. This should be straight forward (I thought) as we only have a handful of different stocks/paper that we use and purchase a similar amount each month.
We get a delivery of paper, say a pallet with 18000 sheets, which could be used on numerous jobs. The process would be: on delivery of any stock it is labelled with the PO_Number, and the database is updated of what has been delivered to us. Then, as and when any operator uses the stock they would simply look at the label, and enter to the DB how much they have used, job number, stock type, size, and the PO Number and GSM(Weight) being the important ones. . What I would like is to create a report showing what quantity is remaining of any stock type, and what it had been used on?

​

I have 2 tables as below.
IN_TBL
IN_ ID
DATE_OF_ORDER
QUANTITY_IN
SHEET_SIZE
GSM
PO_IN

OUT_TBL
OUT_ID
DATE_OF_USAGE
QUANTITY_USED
JOB_NUMBER
PO_OUT
PO_IN
I am confused as to if I should try and create queries and relationships to retrieve the results or to do a calculation within the report fields somehow.
Again thank you for any advice you can give. It really is much appreciated.
Jo

 

[David Eagar]

I think you need to redo your tables, you have duplicate data, which is not good.

if you have a Paper_tbl you can list all your papers, gsm etc and use this in the Stock_tbl where you can record PO's, print job usage and current stock

 

[joe90]

Thank you for your response..

What would you suggest? as in table structure, and field names for tables.

I really appreciate your help..


Jo

 

[David Eagar]

I am thinking along the lines of - attached

 

[joe90]

Thank you again for the speedy reply.

I'm going to have a little play...

I will post a reply..

 

[Boro]

Look Joe

Design of data bases is not a job which You could learn throught forums. It is serious job which need serious approuch. A lot of learning hours with help of proffesionals if
possible or with many, many misstakes if You are trying to do it youreself through help system of the software. If You are learning through doing job for someone, both
You and that firm could have many problems. I don;t want to unencourage You. Think of this as a sort of good advice. Regards.

 

[joe90]

ty boro for your input...

I am asking a question. its no big deal...its not like if i dont fully learn how to manipulate access to fulfill my expectations I lose my job or anything,... this is something I have set myself to do......so thankyou anyway...but softly surely will be nice. not brick wall end I cant do it!....if that makes sense...it does to me.

Thanks anyway...

I'll crack on if thats ok Boro

 

[Boro]

Yes it's OK if considered as a hobby