Hi all, I seem to be having a brain fart as I can't figure my way around this without using a series of update queries. I know there has to be a better way. Here's the scenario:
I have a customer that needs an order number for invoicing purposes. They issue blanket orders once per month and they cover everything in that month based on their bill code. However they won't accept duplicate truckno+orderno. To make matters worse, my customer wants to bill weekly. Currently I have an order table that the user enters the order number, bill code, expiration date when the blanket orders are issued. If they enter a second order number for the same bill code+expiration a numeric field is updated from 0 to 1 to 2 etc.
I have a form where the user enters the start and end date that they want to bill. I have a make-table query that get's all the records from tblMain that are in that date range & puts them in tmp1. There's another make table query that gets all the records from tblMain where the billcode matches a billcode in the orderno table & the starting billdate is >=(expiration - 31) & ending billdate is <=Expiration into tmp2. (I chose unique values so I just get the transaction numbers 1 time)
So I have all the transaction nos for the month & the transaction nos, truckno, billcode & orders for the week. I need to cycle through the weekly records & add the appropriate order numbers for each billcode. If a truckno+orderno duplicated then I can't update it and need to use the next orderno if there is one, if not I need to flag the record. Then I need to cycle through the orders for the month and see if there are duplicate truckno+orderno records that were used in a previous week. If there are, I can't bill it until I request additional order numbers from the customer.
Example:
Ordertbl Orderno billcode expiration
123 xyz 8/17/06
124 xyz 8/17/06
125 abc 8/17/06
126 abc 8/17/06
maintbl Orderno orddate billcode Trans# truckno
null 7/26 xyz 1 9
null 7/26 xyz 2 9
123 7/17 xyz 3 9
null 7/26 xyz 4 7
null 7/26 xyz 5 7
null 7/26 abc 6 9
null 7/26 abc 7 7
billing from 7/25 - 7/31
I need to make trans# 1.orderno = 124 because 123 was used with xyz on truck 9 the previous week. I need to flag trans# 2 because I don't have any more orders for xyz for truck 9. I need trans# 4 to use order 123 because it wasn't previously used for truck 7 and trans# 5 should be order 124 because truck 7 used order 123 already. Transactions 6 & 7 should both be order 125.
All of this data manipulation is being done after the fact as I get the orders that expire 8/17 on 8/18 or so.
I feel like I'm being long-winded and not very clear. Any help is appreciated.
I have a customer that needs an order number for invoicing purposes. They issue blanket orders once per month and they cover everything in that month based on their bill code. However they won't accept duplicate truckno+orderno. To make matters worse, my customer wants to bill weekly. Currently I have an order table that the user enters the order number, bill code, expiration date when the blanket orders are issued. If they enter a second order number for the same bill code+expiration a numeric field is updated from 0 to 1 to 2 etc.
I have a form where the user enters the start and end date that they want to bill. I have a make-table query that get's all the records from tblMain that are in that date range & puts them in tmp1. There's another make table query that gets all the records from tblMain where the billcode matches a billcode in the orderno table & the starting billdate is >=(expiration - 31) & ending billdate is <=Expiration into tmp2. (I chose unique values so I just get the transaction numbers 1 time)
So I have all the transaction nos for the month & the transaction nos, truckno, billcode & orders for the week. I need to cycle through the weekly records & add the appropriate order numbers for each billcode. If a truckno+orderno duplicated then I can't update it and need to use the next orderno if there is one, if not I need to flag the record. Then I need to cycle through the orders for the month and see if there are duplicate truckno+orderno records that were used in a previous week. If there are, I can't bill it until I request additional order numbers from the customer.
Example:
Ordertbl Orderno billcode expiration
123 xyz 8/17/06
124 xyz 8/17/06
125 abc 8/17/06
126 abc 8/17/06
maintbl Orderno orddate billcode Trans# truckno
null 7/26 xyz 1 9
null 7/26 xyz 2 9
123 7/17 xyz 3 9
null 7/26 xyz 4 7
null 7/26 xyz 5 7
null 7/26 abc 6 9
null 7/26 abc 7 7
billing from 7/25 - 7/31
I need to make trans# 1.orderno = 124 because 123 was used with xyz on truck 9 the previous week. I need to flag trans# 2 because I don't have any more orders for xyz for truck 9. I need trans# 4 to use order 123 because it wasn't previously used for truck 7 and trans# 5 should be order 124 because truck 7 used order 123 already. Transactions 6 & 7 should both be order 125.
All of this data manipulation is being done after the fact as I get the orders that expire 8/17 on 8/18 or so.
I feel like I'm being long-winded and not very clear. Any help is appreciated.
