Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    MickyB is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2019
    Location
    UK
    Posts
    25
    did anyone manage to have a look at the database?

  2. #17
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    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?

  3. #18
    MickyB is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2019
    Location
    UK
    Posts
    25
    Quote Originally Posted by davegri View Post
    I did. Noticed that coding is entirely macros. Are you willing to accept help with VBA code?

    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.

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    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!

    Quote Originally Posted by MickyB View Post
    Wow... I have no idea what that means. the zip file is 2.49mb
    For zip-files the Max size limit in this forum is 2 Mb! For other file types less (200 - 500 kB)!

    Quote Originally Posted by MickyB View Post
    1. to auto input the total in the table.
    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!
    Quote Originally Posted by MickyB View Post
    2. to create a report of job totals and expenses with a total balance
    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.

  5. #20
    MickyB is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2019
    Location
    UK
    Posts
    25
    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

  6. #21
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    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?

  7. #22
    MickyB is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2019
    Location
    UK
    Posts
    25
    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.

  8. #23
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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]

  9. #24
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    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?

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-11-2016, 07:30 AM
  2. Help required to set up database
    By dmcg9760 in forum Access
    Replies: 15
    Last Post: 09-15-2015, 09:13 AM
  3. Replies: 2
    Last Post: 06-12-2015, 07:03 PM
  4. Horses database help required
    By arnie in forum Database Design
    Replies: 8
    Last Post: 04-26-2011, 08:34 PM
  5. Simple database required
    By Steve in forum Database Design
    Replies: 2
    Last Post: 03-09-2010, 06:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums