Forum Discussion
From Excel to Access - Match invoices
Hi, I used to use MS Access twenty+ years ago, but lost experience. I'm a heavy MS Excel user nowadays.
I do have an issue which I can solve in MS Excel, but don't know how to in MS Access.
Issue:
My wife's business (psychotherapy practice) has four revenue-streams: 1. contracted, 2. not-contracted, 3. not-insured, 4. businesses. For each client invoices with unique numbers are produced. For 1. contracted several hundred to 1.000+ lines result in one invoice(number). For the other streams, number of lines per invoice are limited from 1 to approx 10.
I downloaded the bankstatements on which payments for the invoices are received. Download was in .csv. Changed to .xls. Inserted an extra column to extract the invoice-number from the description line in the bankstatements.
So now I have 5 tables: 1th for incoming payments, 2 - 5 for each revenue-stream and .
In MS Excel I'm able to create a Pivot table where I get five columns: 1. Received payment per invoice-number and columns 2 - 5 for corresponding invoice-amount. Occasionally received amount differ from invoice-amount due to mistakes by payers. So I added a 6th column [difference between invoice amount versus received payment].
Works excellent in MS Excel. The non-matched line per revenue-column contains the open invoices. Double-clicking opens a new tab with list of these open invoices.
Although MS Excel seems to better in number-analyzing than MS Access, I wanted to see what possibilities MS Access offers compared to MS Excel.
Given the fact that I have 5 tables (1: incoming payments, 2 - 5: revenue-streams), how do I set up a similar query or report to match incoming payments with corresponding invoices? All tables contain a column with invoice-number.
Many thanks in advance!
- It depends a lot on how you want to present the data. You could, in theory, create a query with all five tables, joined on the invoice-number. However, it's not clear whether that invoice-number is unique in each table, and if not, what the primary key values would be. Also, it depends partly on whether there are matching records in all tables. That would determine whether you need inner or outer joins.
Basically, though, if you can provide sample data, someone could take a shot at helping you crete the query. Use made up data, but make it realistic.
Thanks.- Hi George, thank you for your reply. Don't know whether it is allowed here to attach .xls or one .zip file, but that would be great. I will prepare a real-life set - with blanked personal ID data.
- Yes,it is allowed to attach .xls or .zip file here.
Open full text editor then you can upload .zip file to.this forum.
- Having 4 tables for revenues is not a good idea. It is against the design rule saying that you should store information in field values and not in table or field names. So revenue type should be a field in your single revenue table. I think that will reduce your problem.
Also remember that designing a relational database is very different from building a spreadsheet.Thank you for your reply. Make sense, to have all revenue streams in one table instead of four. Yet they are produced this way by different modules in company's ERP. Design of tables is not fully comparable and compatible.
Will try to import the four original tables and combine them in one new one.
