did anyone manage to have a look at the database?
did anyone manage to have a look at the database?
I did. Noticed that coding is entirely macros. Are you willing to accept help with VBA code?did anyone manage to have a look at the database?
Any help will be appreciated to get this database to do what i want it to do.
1. to auto input the total in the table.
2. to create a report of job totals and expenses with a total balance
might be messy but everything else seems to do what i require.
What i would really like to do is create an appointments database that creates appointments, saves customer details, jobs, payments, expenses etc...but a bit out of my league i think.
An explanation to davegri's post (post #17): I think most experienced users here do never use macros, but prefer VBA instead (In Access, those 2 are entirely different animals!). To make it short, with VBA you have detailed control over how Access interprets your commands - and with macros you haven't!
For zip-files the Max size limit in this forum is 2 Mb! For other file types less (200 - 500 kB)!
Saving totals (and generally other calculated values too, but sometimes it will be needed to set e.g. filters for forms) is a bad practice. It means, that everywhere where you enter any quantities, times, prices, etc., you have to recalculate all affected totals which may be affected - after every update in any table row or field, after every update, delete or insert query used on some table, etc. When you don't do this, or you have some error in your code which does this, then you can have entirely wrong data in your database. and when you discover this too late, it may be beyond repair! Have all source data saved in tables - and you always can calculated totals whenever you need them using source data only!
You base a the report on query - e.g. a query where a detailed list of all expenses is listed, with fields e.g. for client who ordered the job, for employee who did the work, for department the employee belongs to, for date the work was done, etc. When you design the report, you can determine by which of those fields are used for data grouping. For every such group, you can have header and footer in your report (you can have, but it is not obligatory). For every such group, you can set the report to calculate totals (e.g. counts or sums) for various fields, and print/display them in according header/footer. Also you can set the report to have report header and footer, and to calculate totals for entire report.
And you can have several different reports based on same query - with different design, or with different filters applied.
can anyone make the database i have created do the 2 tasks i have mentioned and then send me a copy of it?
1. to auto input the total in the table.
2. to create a report of job totals and expenses with a total balance
Where does Cost come from? Should there be Cost fields in either the job type or job description tables?
Where does Expenses come from? There is no table field at all for expenses.
Question, can there be more than one job per invoice?
Q. Where does Cost come from?
A. cost is manually input.
Q. Should there be Cost fields in either the job type or job description tables?
A. No as cost may be different on each job
Q. Where does Expenses come from? There is no table field at all for expenses.
A. Expenses are manually input from my expense receipts. The table is called Expense accounts. Basically i want to create a report that shows all invoice amounts and expenses that will calculate profit or loss.
Q. Question, can there be more than one job per invoice?
A. Yes...that is why i have set up [job type 1,2,3,4 and 5] and [job description 1,2,3,4 and 5] example... [job type1 - carpet cleaning] [Job Description1 - Living Room] ... [job type2 - upholstery cleaning] [Job Description2 - arm chair]
I am a novice and this is the way i got it to do what i wanted it to do.
You need to start with Normalized table design.
When you see fields with same name and a sequence number, you see missing tables/non normalized structure.
Yes...that is why i have set up [job type 1,2,3,4 and 5] and [job description 1,2,3,4 and 5] example... [job type1 - carpet cleaning] [Job Description1 - Living Room] ... [job type2 - upholstery cleaning] [Job Description2 - arm chair]
Database-davegri-v01.zip
This DB will calculate your totals from the form and update the table.
Could find no existing method to enter/calculate any expenses.
BTW, shouldn't COST be called PRICE?