Windows 11 
Access 2016
Performing Supplier Invoice Audit - Help Requested
Background
Hello,
My company leadership asked me to design a process where we can place all the data from our supplier portal into a database. I already had Microsoft Access 2016, so I thought I would try it. I am learning Access and SQL, but still consider myself somewhat of a novice.
So far, I have imported the data for closed invoices from the supplier portal into a table in Access, and I have imported contract pricing data from our supplier into a separate table.
The Closed Invoices table contains every line item from every invoice, and each line item has information such as item number, description, quantity, price, customer, invoice date, etc. Of note, each line item from an invoice is its own record in its own row, but all line items/records pertaining to a single invoice have a field displaying the same invoice number. Ex: Three items from one invoice, each is its own record, and all three display the invoice number.
The Contract Pricing table contains the item numbers, descriptions, contract price per unit, and the data that price became effective.
Goal
Here is what I am trying to accomplish: I want to design a query where I can list each instance where an item was purchased during a period of time, and also show what the contract price which was effective at that time. From there, I am looking to identify where the supplier overcharged us by a variance of 15%.
Additional Information
At this time, I do not have any primary keys. I suspect I will need to build a relationship between the tables but I am unsure how to proceed since item numbers and invoice numbers are duplicated throughout. Is there a way to nest all items under a specific invoice number so as to reduce the number of individual records displaying an invoice number? That sounds like a separate issue but could still be help.
Your assistance is greatly appreciated!
Reply With Quote
