I have one table (tblOrders). The records in the table are primarily organized and sorted by a field called txtOrderID. There can be multiple records for an OrderID. Under OrderID, there is another sorting (grouping) level called txtProductID. There can be multiple ProductID's for each OrderID. There is also a third field in the table called txtProductIDCt which is used for counting purposes in other queries, etc... I am only inputting data and using data from the table directly...no forms or reports are being used.
txtOrderID = 11 (all the records below are associated with this ID)
txtProductID(1) = apple
txtProductID(2) = apple
txtProductID(3) = orange
txtProductID(4) = orange
txtProductID(5) = orange
txtProductID(6) = banana
1. First, I need to be able to determine how many txtProductID(s) I have per txtOrderID.
2. In the example above, for txtProductID = apple, I would want the txtProductIDCt field to be filled with a "1" for just one of the "apple" records and a "0" for all of the others. The placement of the "1" can be on any of the apple field records...it doesn't matter. As indicated previously, this field is used for counting at a later date (other queries, etc...). So, in summary, for txtOrderID 11, I would have one apple record where the count field is marked "1" and one apple record where the count field would be marked "0". In the case of txtOrderID 11 = Orange, I would have one orange record where the count field would be marked "1" and two orange records where the count field would be marked "0". In case of txtOrderID 11 = banana, I would only have one record where the count field would be marked "1" (since, I only have 1 banana record).
I realize that I could do a count query easily to determine the number of apple, orange and banana records individually, but the problem is that I need the actual count field flagged permanantly in my master table. If there is a way to do this "automatically" (via programming), obviously I would prefer that over having to go thru the table one-by-one to enter the flags individually. The table is rather large...it has about 600,000 records. If anyone has any suggestions for how I could do this, I would appreciate the help. Thanks in advance for your assistance.