Results 1 to 12 of 12
  1. #1
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133

    Post Perform calculations in the query

    Hello everyone
    I have a problem with ( sum ) in query
    When I want to make (+) and ( - ) in query , it doesn't give me

    for example : I have (sum of sales , sum of buy , sum of return from costumers , sum of return to production company and sum of damage)
    I want to make Total for all of them

    I used (IIF with " is null " ) but It's not gives me right choose

    for every these tables , I made special query .

    I used : IIF([sum of buy] is null ;0; [sum of buy] + IIF([sum of return from costumers] is null ;0;[sum of return from costumers] - IIF([sum of sales] is null ;0; [sum of sales] - IIF([sum of return to production company] is null ;0; [sum of return to production company] - IIF([sum of damage] is null ;0;[sum of damage])))))
    even my report Store show me correct



    I mean When I buy something and enter in buy invoice , it's appear in Store .
    Click image for larger version. 

Name:	Capture1.PNG 
Views:	33 
Size:	54.0 KB 
ID:	25918

    it's gives me this sentence .

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps it would be better to replace the "IIf"s with the Nz() function. Something like:
    Total:Nz([sum of buy],0) + Nz([sum of return from costumers],0) - Nz([sum of sales],0) - Nz([sum of return to production company],0) - Nz([sum of damage],0)
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133
    Thank you so much for your help ,, solved my problem .

  4. #4
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133
    But I want When I enter data in buy invoice , It's Automatically appear in Store ,,, because Maybe I don't have Damage or costumers don't return those things I sold him before even my Inventory appear .
    for example : When I buy pipe (40*80*1.50 mm 10 quantities ) it's Automatically appear in Inventory

    Goods name Buy Sale Stay

    40*80*1.50 10 0 10

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by Mehvan View Post
    But I want When I enter data in buy invoice , It's Automatically appear in Store ,,, because Maybe I don't have Damage or costumers don't return those things I sold him before even my Inventory appear .
    for example : When I buy pipe (40*80*1.50 mm 10 quantities ) it's Automatically appear in Inventory

    Goods name Buy Sale Stay

    40*80*1.50 10 0 10
    I'm sorry, but as much as I would like to help I do not understand the problem.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  6. #6
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133
    Okay
    I will try to explain to you as much as I can
    I have 5 invoices ( sale , buy , costumers return , return to products companies and damage )
    sale invoice ( ID , Goods name , quantity and Price )
    buy invoice ( ID , Goods name , Quantity and Price )
    costumers return invoice ( ID , Goods name , Quantity and Price )
    return to products companies invoice ( ID , Goods name , Quantity and Price )
    damage invoice ( ID , Goods name , Quantity and Price )
    I would like to make relationship between them in Query ,, even I make report to inventory When I look at my inventory I know I have how many cartoons of cigarettes in inventory .
    for example : I bought 30 cartoons of MarLboro ,, I will enter in buy invoice , When I enter in buy invoice It Automatically appear in inventory
    I don't want to appear all of these invoices in inventory , only I want to know How many cartoons buy and How many sales .
    I bought 30 cartoons of Malboro and I entered in buy invoice but it wasn't appear in report

    I made 3 queries for these 5 invoices :
    I made relation ( buy invoice and costumers return ) because it will come to my inventory
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	25 
Size:	22.1 KB 
ID:	25933
    and I made relation ( sale invoice , damage and return to products companies ) because it will go out from inventory
    Click image for larger version. 

Name:	Capture1.PNG 
Views:	25 
Size:	23.2 KB 
ID:	25934
    also I made relation between to these queries
    Click image for larger version. 

Name:	Capture3.PNG 
Views:	25 
Size:	20.3 KB 
ID:	25932

    and I used this : Nz(,0) you wrote to me before , But I don't know why it wasn't appear
    I entered 30 cartoons in buy invoice but it's not appear

    Click image for larger version. 

Name:	Capture4.PNG 
Views:	25 
Size:	20.6 KB 
ID:	25935
    Last edited by Mehvan; 09-27-2016 at 04:35 AM.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From the postings, it sounds to me you might have a table structure design error. I would need to see the dB.

    In query "sub Import", there could be a spelling error: you have "Qauntity" (the "a" and "u" are swapped)

    I don't understand what you are trying to do, but it doesn't make sense to link the queries on the PK fields like you have.
    I might use a union query....

    It all depends on the table structure.........

  8. #8
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133
    thank you so much for your help
    I have inventory project for industrial iron company , and I'm an accountant there
    I made 5 invoices and also I made 5 invoices when I use forms I will use them for Subforms

    I tried to add DB but I can't , Appeared a problem during upload DB , When I used "attachment" to add it .
    I don't know why , maybe because I'm new here , I don't have many information about this website .

    excuse me , How I will add dB , if don't be disturb to you ?

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do a "Compact and Repair" (in the menu,under DATABASE TOOLS), then Zip it. Max file size for a zipped file is 2 mb>

  10. #10
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133

    Post

    thank you so much

    I will try to add it , I will be glad to can help me
    office.zip

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am not trying to be mean, but these are my thoughts...

    After looking at your dB, my advise would be to step back and start over.

    I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.

    You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."

    This site has a lot of example databases:
    http://www.databaseanswers.org/data_models/index.htm

    You might find one that is close to what you are trying to do that cam be modified or the table structures will help you design your own.
    Unfortunately, I don't know your business and I don't have the time to learn it.



    Some suggestions:
    Read/learn about normalization.
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin object names with a number.

    Do not use look up FIELDS, multi-value fields or calculated fields in tables.

    Use an autonumber field for the table PK field. (I do)


    Some sites about autonumbers:
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp


    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm



    Post back if/when you want the table structure to be looked at.
    There are many people here that are very good at dB design......

    Good luck with your project.......

  12. #12
    Mehvan's Avatar
    Mehvan is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2016
    Posts
    133
    Thank you so much for your help
    I will try to search on those website you sent to me , I think they will be useful for me for me
    because I don't have many information about Microsoft Access ,, This is my first project I will make by Microsoft Access
    and I began with hard project , I think because of that it's hard for me to learn Microsoft Access

    I will be glad to meet someone here to can help me sometimes When He has a free time
    you're right , maybe my tables have a problems
    I thought Tables are easy to make but they been not in that idea I thought about them .

    In the first I should learn How to make correct Tables without problems .

    I didn't made Forms in that DB I sent to you before , because I thought I should make correct Queries even I be able to make Forms .

    because I'm working at the industrial iron company as an accountant , I don't have enough time to come this website often ,, but I will try to do my best even I be able to learn Microsoft Access ,, and I should be patient because gradually I will be better with help people in this website .

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 01-06-2016, 10:08 PM
  2. Replies: 6
    Last Post: 11-21-2014, 05:35 PM
  3. Replies: 1
    Last Post: 10-08-2012, 03:35 PM
  4. Query to perform percentage
    By mari_hitz in forum Queries
    Replies: 4
    Last Post: 04-10-2012, 10:56 AM
  5. Replies: 2
    Last Post: 04-18-2011, 02:46 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