Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2005
    Posts
    26

    Unanswered: Need help in creating db

    Hello, i need help and maybe some tips or suggestions how to create my new db.First thing i need to do is to create "Invoices DB".
    Emplyees in our company will fill in various invoices, and data from these invoices will be stored somewhere (in table i suppose).There should be some kind of Form, where all that information our employees fill in.
    Now i don't know exactly how i should create table and form...which way to go.This "invoice form" should look smth like this:

    -Product - Category - Price - Quantity - TotalPrice

    --Book------A --------10$-------2---------20$
    --Pencil-----B---------2$--------1---------2$
    --Paper-----C---------5$--------1---------5$

    --------------------------------------Sum: 27$
    --------------------------------------Discount 10%
    --------------------------------------Total 24.3$


    Also "Date, ClientID" and other standart fields.

    Now what should i do.... create seperate field for each position in this form (e.g. field1 - Product, field2 - Category, field3 - "Book" , field4- "pencil" and so on) and put all information from this specific invoice to one record, or just create main 5 fields at the top (Product,Category,Price,Quantity ,TotalPrice) and for each new entered product my form should create new record.

    In first version, i will have to create many fields and there will be big mess...

    In second version, everything seems more compact, but there might be some problems with entering (new data). If i enter it at first, i will fill it and print it.But if i need to create one more (new) invoice, i 'll open my form but there will already be entered information, and i'll have to delete everything? This is were i need to figure out what to do...how to make entering new invoice similar to entering new records in standart forms. E.g. i open window, and i see blank fields, where i fill them and print.Then i close, information is stored in table, and next time i open my invoice form, i see again new window with empty fields, where i can create another new invoice and so on...


    Maybe you can help me here,

    Thank you.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Look at the orders form of the Northwind database, that should give you some ideas.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by Teddy
    Look at the orders form of the Northwind database, that should give you some ideas.
    Yes, i did look there, and as i understand there is form and subform in it.Can i make without subform?

    How do i make it look empty everytime i open it? Because now in Orders form ,when i open it, i already see previously entered records.
    Last edited by Fprot2; 11-16-05 at 13:03.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    dataentry = true
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by Teddy
    dataentry = true
    Thanks, it works better now.

    Another question, how do i create various calculations for different prices in this invoice.I want to make discount field, BUT i want to make exceptions for certain product categories. E.g. whenever i enter discount percentage, it calculates discount for all products, except the ones that are from category "A".

    As I understand here are some conditions, where should i specify them...?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    that is a significantly more complex question. I would tinker around with that northwind database for a while until you understand how it works, then try to customize your own and let us know which specific issues you run into...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by Teddy
    that is a significantly more complex question. I would tinker around with that northwind database for a while until you understand how it works, then try to customize your own and let us know which specific issues you run into...

    OK, i did write in VBA simple calculations that are made when you fill some info.But these calculations are made for each record (in my case - each line) seperately.And when i want to see total numbers, i see only for that particular record (product). How to write in VBA, to do calculations from several records, rather than everything in one?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's what section footers are for...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by Teddy
    That's what section footers are for...
    Could you specify exactly what is it and where?

    Thanks

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Calling "=Sum([YourField])" in a section footer will sum for the entire section. You're probably best off looking up section headers/footers in the help file. there is a help topic titled "Calculate a total or other aggregate values" that you may find useful.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Nov 2005
    Posts
    26
    Quote Originally Posted by Teddy
    Calling "=Sum([YourField])" in a section footer will sum for the entire section. You're probably best off looking up section headers/footers in the help file. there is a help topic titled "Calculate a total or other aggregate values" that you may find useful.

    OK I did it, seems to work now.

    One more question, I have this continuous form, where I see all records in one form.I did section footer sum, and it works, but sum values are updated only when i press on new record (to fill in new record).

    Is it possible to make so that Total Sum result would be updated when i navigate in one record, rather than going to other record...?

    Now i use Afterupdate function.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The record doesn't update until after you navigate away from it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •