Unanswered: How to reset the counter if the dates does not match?
I know this question may sound silly to some but I need advice or guidance in how to do this.
I am trying to create a method so that it can be used while creating new purchase orders. See below my example:
There are two tables (Temp_POCounter and Temp_PurchaseOrder). The function of Temp_POCounter is to count the # of times a purchase order was created and give the current date. The function of Temp_PurchaseOrder is to capture all of the information in order to create a purchase order using the information from Temp_POCounter to create the Purchase Order #.
The link between the two tables is a field called PO_Count.
The business rule for creating a new purchase order is if the current date (from Temp_PurchaseOrder) does not match the last given date (from Temp_POCounter), then the table should be cleared out and the count should restart back to '1' and post the new date.
How to reset the counter if the dates does not match?
Originally Posted by Sinndho
You do not supply the definitions of both tables, which is important for being able to help you.
To make it clearer:
Both tables are ms access database tables and they are defined by:
Temp_POCounter's primary function is to give the end user a count of how many POs were created in a day. When the end user go to the add new purchase order form, the process should review this table first and check for the last date given. If the date matches the current date, then we should add '1' to PO_Count field. Then we will use that value and include it in the actual Purchase Order # field (Temp_PurchaseOrder table). If the date does not match the current date, then we wish to clear the records and restart at '1' and insert the new date.
To summarize this - the table will constantly be removing records and starting over each day.
Temp_PurchaseOrder's primary function is to gather information from the end user in order to create new purchase order(s). The key field (PO_OrderNo) is created by taking the current date and the PO_Count field (from Temp_POCounter). The format is "mmdd[PO_Count]-yy". So this table relies on Temp_POCounter table in creating the Order No.
I was not asking for a functional description of the tables but for their actual definition: Column names, data types, indexes, constraints.
Sorry about that Sinndho.
PO_Count Number Indexed (yes, includes dups)
PO_CountDate Date/Time (Short Date)
Temp_Purchase Order (it is alot of fields, just going to give you a gist of it)
PO_Order_No Text Indexed (yes, no dups)
PO_Order_Date Date/Time (Short Date)
Vendor Name Text (lookup to ROR.Vendor table)