How to allow users to assign account (from lookup field) to expenses from table

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?  
BBluAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not sure what you are asking for here but it looks like you need a "Junction" table (Many-to-many)

The lookup would be in the form, not in the table/query
0
BBluAuthor Commented:
I'll look into that.  Is that the same thing as a union query?
0
Jeffrey CoachmanMIS LiasonCommented:
No

A Union Query simply stacks one table on top of another.

A Junction (Association, Intersection, Many-To-Many, Linking, Mapping, Marry, ...etc) table is the tables that sits between to tables and represents a many-to may relationship.

ex:
tblStudents
StudentID
Studentname

tblClasses
ClassID
ClassName

tblStudentClasses
StudentClassesID
StudentID
ClassID

Here tblStudentClasses is the junction table because one student can have many classes, and also one class will have many students.

Does this sound like your scenario?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

BBluAuthor Commented:
I think the problem is that the main table with which I'm working does not have a unique key/id, a cardinal sin in creating a relational database application.  I've decided to just allow the employees to work from the original records via a form.
0
Jeffrey CoachmanMIS LiasonCommented:
so?... problem solved?
0
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.