Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Microsoft Access/Excel 2003/2007

Options
  • 16-03-2010 3:38pm
    #1
    Clauric
    Registered Users Posts: 610 ✭✭✭


    Hi, I am looking for some help with the following:

    I have a large Access (2003) database, of nearly 5000 lines, and about 30 entries to each line. I also have an Excel (2007) sheet that is related, but not linked to the information.

    With the information in the database, I need to create an invoice in MS Word, that lists the invoice number (a continuation of the numbers in Excel), and also a list of users of the service, and how far along in their contract they are. E.g. Company is Joe Bloggs, 3 users of the service, 2 users in year 2, and 1 user in year three). This information would need to automatically update if I run the program, and not generate any information if it has already been invoiced in the last 12 months. Also the amount of the invoice, and the invoice number would then need to be introduced into Excel sheet. The MS Word doc can be saved anywhere.

    Any help would be gratefully accepted.


Comments

  • Options
    #2
    mark renton
    Closed Accounts Posts: 2,696 ✭✭✭mark renton


    Just an idea - I'm not sure exactly of your setup for you your 5000 lines in Access, im assuming it is one table, if so then you could possibly create a second table from you excel document, ensuring relationship between the 2 tables you could then extract required reports from your database in Access - not well up on Access but I'm sure there is a sufficient reporting tool in there

    Is that 30 columns to your table?


  • Options
    #3
    amen
    Registered Users Posts: 2,781 ✭✭✭amen


    hmm a few observations here

    1: using invoives numbers in excel to generate a invoice number in a word docment based on data in Access seems a bit complicated. Why not create a new access table and take the data from excel and add it into Access
    2: I would write a Access query or maybe a VB module associated with a Button in Access. When run it generates the required invoice.
    3: these invoice details would then be stored in database in second new table with the date the invoice was created, invoice number and the invoice details.
    3A. The invoice create date is used when generating invoices to see if it 12 months since the last invoice was generated for this customer
    4: Once 2/3 has completed a report would pop up with all of the invoices for that run which could then be printed (you can basically format the report like a MS word doc)
    5: I would also have a Print Invoice button which would allow you to print an individual invoice based on the invoice number(in case a customer needs a reprint etc)
    6: I would also add a report to let you see all the invoice numbers or if you really an excel report Access lets you export data in excel format


    let us know how you get on


Advertisement