Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a MS Access Database that tracks clients, client regions and transaction types.
Users are keying in transactions and once done, the system needs to create letters to the client regions headquarters sorted by
Client Region (Region A, B etc) AND Transaction Type(TransType 1, TransType 2) eg.Region A:TransType 1, Region B:TransType 1, Region A:TransType 2.

I am working with VBA, SQL statements (ran through VBA), Word OLE Automation, ADODB Recordsets and Unique strings.
Could anyone advise on how I can structure my DB to track the Letters and all the details about the letters?

share|improve this question
add comment

closed as off-topic by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, Michael - sqlbot, Kin Feb 24 at 17:04

If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 0 down vote accepted
+Create a list of the transactions sorted by client regions and by transaction types    
+Load into a recordset and go to first record  
+Get the current values for client region and transaction type  
+Create a unique string  
+Create a letter and capture all necessary details in a letter table including the unique string  
+Run a select statement for LetterID for where unique string field is current unique string value  
+Update the transactions with New Letter ID and mark as batched  

+Loop through the whole recordset  
+Move to next record  
+Check if client regions and transaction types is the same.  
+If same, Update the transactions with current Letter ID and mark as batched  
+If different, create using above method.  
share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.