How to bring forward the running qty to the next day (1 Viewer)

[NurulScare]

Helloo guys!

I need help, im newbie in access.
Actually i want to create WIP Qty (Qry_WIPQty) based on Production output minus FG go to logistic (FGRS). (The relationship between Table P01_ and P03_FGRS).
1. I already Follow step as per qryUnion and QryFinal
2. However I figure some error, the qty not bring forward to the next day.
3. Ex as below:

ex1: part 3211-BZ020A, 10-Aug have output, and part Out (FGRS), So the WIP qty become 180. So 11-Aug bring forward qty become 180
ex2: part 48622-BZ190-H, 10-Aug have output 300, no FGRS. then 11-Aug have Output qty in 200pcs. output become 500. no FGRS. so the qty bring forward to 12-Aug 500.

I want my query to look like this. huhu Thank you for your help ya. I attached my database as well.

Another question, is it possible to make my queries look like this table? like column heading. if not, i need to copy to excel and do pivot table to get this kind of table.

THANK YOUUU..

 

[plog]

I think you need to put this issue aside and work on your tables. The process of setting up tables in a database is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

I would read thru that page, work a few tutorials and then apply what you learn to your database. Here's the big issues I see with yours:

1. Sets of fields - In P01... you have a bunch of fields prefixed with "DTX " where X is a number (e.g. [DT1 Code], [DT1 Time (Min)], [DT1 Desc]). When you feel the need to do that, its time for a new table to hold all that data. All that DTX data needs to go into its own table along with the primary key of P01...

2. Data stored in field names - you have a ton of [...Qty] fields in P01... (e.g. [Coil Plan Qty], [Mat Qty (Shts)], [Plan Qty], etc.) . The word before "Qty" should be stored in your tables as data, not as a field. All those fields need to go into a new table that has both a [Quantity] and a [QuantityType] field. In that [QuantityType] field is where you will store the word before the "Qty" in all those fields in P01.. currently.

3. Redundant data - I see a ton of tables with [Part No] and [Part Name]. If that data is directly related (1 Part No has only 1 Part Name and vice versa), then that data should be stored in just 1 place and the primary key of that table should go into the other tables, not both those fields. I don't see a Parts table, but thats what you need. Also, if Item Group (or any other field) is similarly related to the part, it should not be stored all over the place.

4. No primary keys - I don't see any autonumbers and I don't see any other fields marked as primary keys of your tables. They serve a purpose:

SQL PRIMARY KEY Constraint

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

That's the big stuff, you should also make sure you are using the right field types (I see a lot of short text fields and seperate fields for dates/times), are only using alphanumeric characters in table and field names (lots of spaces in your names, that's gonna be a pain later on).

I suggest you read up on normalization, work thru a few tutorials, apply what you learn to your database, complete the Relationship Tool in Access and post that database back here so we can work through it to get your tables set up correctly.

 

[NurulScare]

I think you need to put this issue aside and work on your tables. The process of setting up tables in a database is called normalization:

Database normalization - Wikipedia

en.wikipedia.org

I would read thru that page, work a few tutorials and then apply what you learn to your database. Here's the big issues I see with yours:

1. Sets of fields - In P01... you have a bunch of fields prefixed with "DTX " where X is a number (e.g. [DT1 Code], [DT1 Time (Min)], [DT1 Desc]). When you feel the need to do that, its time for a new table to hold all that data. All that DTX data needs to go into its own table along with the primary key of P01...

2. Data stored in field names - you have a ton of [...Qty] fields in P01... (e.g. [Coil Plan Qty], [Mat Qty (Shts)], [Plan Qty], etc.) . The word before "Qty" should be stored in your tables as data, not as a field. All those fields need to go into a new table that has both a [Quantity] and a [QuantityType] field. In that [QuantityType] field is where you will store the word before the "Qty" in all those fields in P01.. currently.

3. Redundant data - I see a ton of tables with [Part No] and [Part Name]. If that data is directly related (1 Part No has only 1 Part Name and vice versa), then that data should be stored in just 1 place and the primary key of that table should go into the other tables, not both those fields. I don't see a Parts table, but thats what you need. Also, if Item Group (or any other field) is similarly related to the part, it should not be stored all over the place.

4. No primary keys - I don't see any autonumbers and I don't see any other fields marked as primary keys of your tables. They serve a purpose:

SQL PRIMARY KEY Constraint

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

That's the big stuff, you should also make sure you are using the right field types (I see a lot of short text fields and seperate fields for dates/times), are only using alphanumeric characters in table and field names (lots of spaces in your names, that's gonna be a pain later on).

I suggest you read up on normalization, work thru a few tutorials, apply what you learn to your database, complete the Relationship Tool in Access and post that database back here so we can work through it to get your tables set up correctly.

Thank you for your explanation. because I really dont have basic about this. and need to do project for my company. so I just do it based on my own way. I realize, many things I still dont understand regarding the structure and etc. especially the importancy of using Primary key.

as your no 2 explanation, because The table P01_ comes from Form. if I separate table by Qty, can I link into 1 same form. so many unclear for me. I need to learn more. Thank you