Results 1 to 6 of 6
  1. #1
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    reducing query cardinality

    Hello everyone
    in the attached database, query qryProva will produce two records, one with costi_diretti, one with costi_indiretti for the same commessa (project):



    ID_commessa Fatturato costi_diretti costi_indiretti
    PROVA A_1 300 10
    PROVA A_1 300 50

    I would like to have only one record as in the following example:

    ID_commessa fatturato costi_diretti costi_indiretti
    PROVA A_1 300 10 50


    Is it possible? Thanks a lot for your help.
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Code:
    SELECT qryFatturato.ID_commessa, qryFatturato.Fatturato, Sum(IIf(qryConsumi.diretto=-1,qryConsumi.costi)) AS costi_diretti, Sum(IIf(qryConsumi.diretto=0,qryConsumi.costi)) AS costi_indiretti
    FROM qryFatturato INNER JOIN qryConsumi ON qryFatturato.PK_Commesse = qryConsumi.PK_Commesse
    GROUP BY qryFatturato.ID_commessa, qryFatturato.Fatturato;

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    See the new query - which uses a cross tab as a source.
    Attached Files Attached Files
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    Costs analysis

    Dear all
    I attach again the DB because when I join two queries (qryConsumi and qryCostiPersonale) in one query, qryCosts, costs are someway doubled.
    How can I solve this issue? The idea is always the same: to list all costs on one line per PK_commesse (that is, per single project).
    Thanks for your help
    Attached Files Attached Files

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Can you demonstrate what you want as an output - I can't see how those two queries will tie up?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Quote Originally Posted by Minty View Post
    Can you demonstrate what you want as an output - I can't see how those two queries will tie up?
    The output should look exactly as is. Unfortunately, that is not the correct mathematical result. Direct costs should be 10, not 20; while indirect costs should be 50, not 100. The basic query, qryConsumi, gets them right, but the join query, qryCosts, does not; and it seems to multiply the results.
    Thanks for any input

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

Similar Threads

  1. Reducing rows in result
    By lawman in forum Queries
    Replies: 1
    Last Post: 12-08-2020, 06:13 PM
  2. Replies: 13
    Last Post: 07-22-2016, 03:09 PM
  3. About ERD, primary key, and cardinality?
    By wannabesmart in forum Access
    Replies: 2
    Last Post: 04-25-2014, 08:44 AM
  4. Reducing code
    By Mclaren in forum Programming
    Replies: 7
    Last Post: 03-16-2011, 02:45 PM
  5. one-to-many-or-none table relationship? (minimum cardinality)
    By racecar333 in forum Database Design
    Replies: 2
    Last Post: 02-24-2011, 07:11 AM

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