Automatic Accounts Recievable [Is my project accomplishable?]

[josekreif]

Hey guys, I'm working on a a project for a company. Right now they are entering all kinds of data by hand daily in Excel. Such as AR, Delivery, Collection, and Payments.

The man in charge thinks that the AR report can be generated automatically using information from the other sheets. I've started out thus far using a total query for Delivery to pull together the total for each invoice per customer.

Now I notice the Delivery Report only contains information starting from Jan of the current year. So all open invoices of last year won't be included. They would have to be manually added by me somehow, but that's not a good idea. When I turn this database over to the end user, they should not have to make any changes to the database whatsoever. So come 2016, 2015 information will be taken care of automatically. They will only need to update the linked Excel spreadsheets (Delivery and Collection) and use Access to Update AR information and export the reports needed.



I'm also seeing a problem where as if a customer receives the same invoice number within 2 years, it will cause major issues since I am grouping by Customer and Invoice Number and using the first invoice date for that invoice. Grouping by year and or month won't work since a customer making multiple orders on a single invoice can occur over a few days, so end of month or year can result in a invoice spanning the either of the two.

Therefore a 2015 invoice number should not be used again for the same customer until 2017, if I'm thinking correctly. However, the delivery report only contains a single years invoices, so this may not be a issue.


Is this project possible or not. The man in charge seems to think it's possible to have to never maintain the AR report again. I'm skeptical.
I would consider myself a intermediate access user, having taken dense Access classes and have a 2 year degree in software programming. So you won't have to dumb things down for me.

[ItsMe]

Access is an RDBMS. If you plan on using Access, you need to transfer operations from a flat file to a relational database. To do this, you will have to create an Entity Relationship Diagram (ERD). You will need to create tables to store the data in and define Key fields for the entities. You will also need to define the attributes for the various entities, among many other things.

What you would like to accomplish is not impossible but it is not easy and not every business is capable of the evolution from Excel to relational database.

[josekreif]

I uderstand relationship, however the data between the spreadsheets is inconsistent.

[ssanfu]

I do not understand what your question is. If you question is "Is my project accomplishable?", I would say "probably" or "possibly". I just don't know enough about your business.

"AR, Delivery, Collection, and Payments". OK, got that.
But then you say "They will only need to update the linked Excel spreadsheets (Delivery and Collection) and use Access to Update AR information and export the reports needed." So is the data staying in Excel or is it imported into Access?

I'm also seeing a problem where as if a customer receives the same invoice number within 2 years

How can an invoice number be used twice? How is it assigned?

It seems you are working with a hybrid Excel-Access system. Why not an all Access solution? What about your network? Are there multiple Excel spreadsheets on multiple computers? Where is the Access dB? Is it split?


Please tell us more about what you are trying to do like you were standing in line at Burger King talking to a stranger.

[JeroenMioch]

Sorry for barging in but you can acomplish basicly everything in Access. The only limit you have is the number of users that access the database at the same time i guess.
Why ar the end users still working with spreadsheets ? Can't they enter data in Access itself ?
I started out with turning excel sheets into Access tables and building the application from there on.
Invoice numbers should never be the same in my opinion. They are like social security numbers of personell numbers and thus unique. You can use Letters in front of the invoices numbers to identify different customers.

So if your customer is for example Shell you use SHL-0001 for the first invoice, SHL-0002 for the second etc etc.
Then when you build your query's and reports your reference for each customer is the letters.
You can also add dates in the invoice names as well and use those in your queries and reports. Like SHL-0001-23072015

If im telling you stuff you allready know then you can disregard my post :P

[josekreif]

I do not understand what your question is. If you question is "Is my project accomplishable?", I would say "probably" or "possibly". I just don't know enough about your business.

"AR, Delivery, Collection, and Payments". OK, got that.
But then you say "They will only need to update the linked Excel spreadsheets (Delivery and Collection) and use Access to Update AR information and export the reports needed." So is the data staying in Excel or is it imported into Access?

How can an invoice number be used twice? How is it assigned?

It seems you are working with a hybrid Excel-Access system. Why not an all Access solution? What about your network? Are there multiple Excel spreadsheets on multiple computers? Where is the Access dB? Is it split?


Please tell us more about what you are trying to do like you were standing in line at Burger King talking to a stranger.

Obviously some reports will need to be entered by a human. The guy in charge of this said company thinks to much time is being done entering data. He wants AR and Payments automated, and anything else in between. The 2 main employees at his company will keep their spreadsheet that they update daily, only instead of sending them to me so I can link them in my DB, they will store them on a drive on their network where I will have the completed DB.

[ssanfu]

How does the system work? Is it linked Excel sheets, data stored in Access,...?
Why not use an Access FE instead of Excel?

It is unclear how things happen/ what should happen.......please explain more.

[Perceptus]

If the data is 100% awesome, validated and triple checked. I would say you could have it all automated. If the data is flawed, you will never rid yourself of working with it.

[Mahendra1000]

Sometimes it is helpful to understand the "why" before helping with the "how"

[josekreif]

How does the system work? Is it linked Excel sheets, data stored in Access,...?
Why not use an Access FE instead of Excel?

It is unclear how things happen/ what should happen.......please explain more.

We have XLS and CSVs in a network drive and they are linked to the DB. VBA and Queries are used to perform calculations and sorting for outing reports such as Invoice, MTD and YTD Reports. FE could work, with a lot of work. I would need multiple FEs for each employee responsible for a single table's data.

If the data is 100% awesome, validated and triple checked. I would say you could have it all automated. If the data is flawed, you will never rid yourself of working with it.

It's just a mess since they have thousands of records already.
If I was making a clean slate for them it would be simple. I could set the rules and everything to make the data entry restricted to the best way possible.