I'd like to replace a manual receipting process.  Currently it is manually written into a carbonless receipt book, retyped into excel to collate all receipts and then manually entered into an application.
The process of manually entering into the application is unavoidable due to db access limitations. There are still large productivity gains and ability for a more professional image in printing own receipts.

The question is should it be created in ACCESS or EXCEL or even Crystal Reports and the best process in doing so?  ODBC connection only choice.  

The specific criteria is:-
Each receipt must have unique uneditable consecutive numbers.
User selector of JOB NO or Debtor.  (this selector will determine fields to be extracted from db)
If JOB NO selected - User can enter or select from limited Job List.  Selection/entry of this Job should return the fields JOB NO, NAME, VALUE DUE. (VALUE DUE IN $ VALUE AND WORDING - eg, $400 & Four hundred dolllars only)
The user should also be able to add PAYMENT TYPE, which can be a simple drop down, also if a credit card surcharge is being applied.  This could be a separate field in which the user could type the value.  If the value in this field is >$0 then the total amount should change.  e.g. VALUE DUE $400 + C/C SURCHARGE $6.38 = TOTAL AMOUNT $406.38 & wording is changed to Four hundred & six dollars .38c only.  
If Debtor is selected then user should be able to enter payment type, create a list of invoice payments (we wont bother extracting this info from db at this stage)  i.e. inv 12345 and payment value in separate field.  A total payment value which again translate to words.

Once record has been printed and confirmed this record should be locked from any further editing.  

Once the record is locked - it should be able to be reported on such as all receipts from x date to x date.  Refer to attached for very simple examples of both receipts and reporting.

The main issues I'm struggling with is what is the best solution to create this seemingly simple solution, how to create / lock the consecutive numbers and write the record information post confirmation.

Thanks for assistance.
Who is Participating?

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

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.

John TsioumprisSoftware & Systems EngineerCommented:
Ms Access is the quickest and probably the best solution and as for the numbering issue the AutoNumber field type is what you need for your consecutive number sequence
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't do it directly in Crystal Reports, since CR is a reporting platform - at least that's what I've found. Perhaps there is some way to manage it entirely with CR, but if so I don't know it.

I'm not sure of the role Excel plays in your process now, but if one of your requirements is to disallow editing after the record is added, then it would seem Excel would not be the best choice.

That leaves Access, or some other programming environment (like .NET, PHP, etc). If you're comfortable with any particular programming environment, then that's probably the "best" solution for you.

If not, then as John suggests, Access would be probably the best fit.
Agree.  To confirm what Scott McDaniel said, Crystal is strictly a reporting tool.  Though there is a limited ability to update a database, getting information from the user is not really possible especially when that information changes with each record.

Though with some effort you could do it in Excel by using forms for inputs and protecting/hiding the sheets from the user, it makes more sense and would be much easier to do in Access which has the features that allow you to control record access.


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
Murphy1992Author Commented:
Great thanks, so create a form within Access?
How does Access record and lock the fields post printing and user confirmation?
You have to handle that in the code behind the forms or in the code that prints the receipt.

The main thing will be to not give the users direct access to the tables.  You need to control all input through the forms.

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 Excel

From novice to tech pro — start learning today.