Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66

    How to total by each specific project number?


  2. #2
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    On the Total row, select SUM for the Qty field.

  3. #3
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    Did that before, it gives the incorrect numbers for QTY.

    Click image for larger version. 

Name:	Captur134234e.PNG 
Views:	14 
Size:	31.2 KB 
ID:	29507

  4. #4
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    Your problem is that you are joining to the Job Cost Log by status. You are going to return one record for each time that status appears in Job Cost Log, no matter what the Job is. Resulting in multiplicate results.

  5. #5
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    What should be linked then?

  6. #6
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    I would have to understand more about your data to answer that. With just a cursory glance, you have an awful lot going on in that table on the left side, I'm not sure your data is well normalized. I'm sorry to be so vague, but a database is like a building structure in that if you don't have a solid foundation laid out, nothing will work right.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Why is Status even in both of these tables? Makes no sense to link on these two fields.

    Should be linking on Project primary and foreign key identifiers. Is Number the primary key in projmast? Is it saved as the foreign key in JBCSTLOG table PROJECT field?

    Don't really even need projmast table in the query except for the NAME field (BTW, name and number are reserved words and should not use reserved words as names, better would be ProjName, ProjNum).

    SELECT [Name], PROJECT, TRANSTYPE, STATUS, Sum(Cost) AS SumCost FROM JBCSTLOG INNER JOIN JBCSTLOG.PROJECT ON projmast.Number GROUP BY [Name], PROJECT, TRANSTYPE, STATUS;

    Or build a report and use its Sorting & Grouping features with aggregate calcs in group and report footers. This will allow display of detail records as well as summary calcs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    This is the complete query on how I want it to look, but theres redundancy, and the Total:Sum is incorrect for the QTY field.

    The redundancy we see is not actually a redundancy, its the result of multiple manually entered entries, there are actually that many entries for each project


    Click image for larger version. 

Name:	C13452354apture.PNG 
Views:	7 
Size:	57.0 KB 
ID:	29532Click image for larger version. 

Name:	Captur2343e.jpg 
Views:	7 
Size:	118.3 KB 
ID:	29533Click image for larger version. 

Name:	Captu23421342342134re.PNG 
Views:	7 
Size:	156.7 KB 
ID:	29534Click image for larger version. 

Name:	Capt134134ure.PNG 
Views:	7 
Size:	6.4 KB 
ID:	29535

  9. #9
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    Attachment 29536Attachment 29537


    Everything in this standalone Query2 is absolutely correct; it must be a linking issue with the one above

  10. #10
    Preston is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2017
    Posts
    55
    You need to aggregate the multiple entries in a subquery first.

  11. #11
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    @Preston
    How do I do that? If you dont mind me asking

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Your attachment links generate 'invalid attachment' error.

    In what way is Sum incorrect? I am not seeing summarization by project. There must be a field included that is causing the records to be unique.

    If you want to retain detail info in the output did you even consider the suggestion of a report to accomplish the aggregate calcs?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    @June7, if you compare the output of SumofQTY in Query2 against the SumofQTY in the Actual query. The numbers are different. The output from Query2 should be the exact output we get from Actual query but we don't

    Attached are the images

    Click image for larger version. 

Name:	Capt134134ure.PNG 
Views:	6 
Size:	6.4 KB 
ID:	29538Click image for larger version. 

Name:	Captur254235e.PNG 
Views:	6 
Size:	26.3 KB 
ID:	29539

  14. #14
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    It must be a linking issue because once I start adding more fields and links to the Actual query it messes up

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Yes, so why are you adding more fields and links?

    Preston is suggesting you do a query that aggregates data of the 'many' side of relationship then join that query to other datasource (a table or another query).

    To better advise, I would have to examine db to learn its structure, get familiar with data, and run queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Project Specific Tables
    By tretcher in forum Access
    Replies: 2
    Last Post: 11-30-2016, 06:44 PM
  2. Retrieve total hours for a project
    By shod90 in forum Forms
    Replies: 1
    Last Post: 08-16-2016, 03:08 AM
  3. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  4. Replies: 6
    Last Post: 03-13-2013, 03:03 PM
  5. Replies: 1
    Last Post: 01-24-2013, 05:50 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