I'm working on a better process for assigning Accounts to Charges we receive on our Company's American Express Card. Right now, my Accountant downloads the file monthly (we'd like to do this more often if we can create an efficient process). Unfortunately the file only contains Date, Description, and Amount and we often have multiple charges so can't create a Unique ID (purchase of flights for two people would have the same all three fields).
I have my Accountant upload this file into a Table in Access. I then run two queries that separate it into two categories: FedEx Shipping Charges (the majority) and NonFedex Charges.
My project question involves the NonFedEx portion. I'd like to create a way for users, preferably the Account Managers) to assign these charges to the appropriate Accounts (e.g. "American Metal", "Acme Publishing", etc.). My initial idea was to:
Run a "Make Table" query (T_NonFedExCharges) the first time, adding a blank (initially) field for AccountName.
2. I then edited the table I just made (T_NonFedExCharges) to make the added field (AccountName) a lookup to a table that we will maintain that contains all of the allowable account names.
3. Then I thought about changing the query to an "Update Query" that updated this table, so we didn't overwrite the lookup field.
The problem is there is no unique ID in the Amex Table and I can't create one from the data provided.
So my next idea was just to give the user direct access (via a query and form) to the end-user so that he/she could add the account from there. But I don't know of a way to add a lookup field in a query column.
Does anyone have any ideas or thoughts on how to approach this problem?