Page 3 of 3 FirstFirst 123
Results 31 to 41 of 41
  1. #31
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    Transtype = L is Labor, there are many other different Transtypes

  2. #32
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Your linking fields cannot be right, you should have a key field in projmast that links to field Project I am thinking in JBCSTLOG. Is each record in projMast unique for a specific project?

  3. #33
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    At 32 posts in and we are still guessing at your set up and requirement????
    This represents a communication problem more than a database/query issue.

    Post the SQL for the latest query.
    Describe what is "wrong" with the result you get.

    Or
    Post a zipped copy of the database--remove anything confidential/private first.

  4. #34
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    SELECT projmast.NUMBER, projmast.PROJPODATE, [Status Codes].Description, [Product Codes].Description, projmast.NAME, projmast.SERIALNUM, projmast.DESCRIPTIO, projmast.SALESMAN, projmast.TECHNICIAN, projmast.TOTAL_PROJ, projmast.TOTALCOST, projmast.TOTAL_PART, projmast.TOTAL_LBR, projmast.HOURSLABOR, projmast.DATEIN, projmast.DATEDUE, projmast.START_DATE, projmast.PRODUCT, extendt6.TOTALBILLD, [TOTAL_PROJ]-[extendt6]![TOTALBILLD] AS Remaining, JBCSTLOG.QTY, [HOURSLABOR]-[JBCSTLOG]![QTY] AS [Hrs remaining]
    FROM ([Product Codes] INNER JOIN ((projmast INNER JOIN [Status Codes] ON projmast.STATUS = [Status Codes].[Service Codes]) LEFT JOIN extendt6 ON projmast.NUMBER = extendt6.PROJNUM) ON [Product Codes].[Service Codes] = projmast.PRODUCT) INNER JOIN JBCSTLOG ON projmast.HOURSLABOR = JBCSTLOG.QTY
    WHERE (((projmast.STATUS)="A") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="B") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="C") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="D") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="E") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="F") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="H") AND ((JBCSTLOG.TRANSTYPE)="L"))
    GROUP BY projmast.NUMBER, projmast.PROJPODATE, [Status Codes].Description, [Product Codes].Description, projmast.NAME, projmast.SERIALNUM, projmast.DESCRIPTIO, projmast.SALESMAN, projmast.TECHNICIAN, projmast.TOTAL_PROJ, projmast.TOTALCOST, projmast.TOTAL_PART, projmast.TOTAL_LBR, projmast.HOURSLABOR, projmast.DATEIN, projmast.DATEDUE, projmast.START_DATE, projmast.PRODUCT, extendt6.TOTALBILLD, [TOTAL_PROJ]-[extendt6]![TOTALBILLD], JBCSTLOG.QTY, [HOURSLABOR]-[JBCSTLOG]![QTY]
    HAVING (((projmast.PRODUCT)<>"X" Or (projmast.PRODUCT)="S" Or (projmast.PRODUCT)="W"))
    ORDER BY projmast.SALESMAN;





    The QTY is the only thing outputted wrong. Its not the right totals for each project number.


    It only shows 33 records but there should be around 70 some

  5. #35
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is the sql in a formatted view (via Poorsql). It might help in deciphering things.
    Code:
    SELECT projmast.NUMBER
        ,projmast.PROJPODATE
        ,[Status Codes].Description
        ,[Product Codes].Description
        ,projmast.NAME
        ,projmast.SERIALNUM
        ,projmast.DESCRIPTIO
        ,projmast.SALESMAN
        ,projmast.TECHNICIAN
        ,projmast.TOTAL_PROJ
        ,projmast.TOTALCOST
        ,projmast.TOTAL_PART
        ,projmast.TOTAL_LBR
        ,projmast.HOURSLABOR
        ,projmast.DATEIN
        ,projmast.DATEDUE
        ,projmast.START_DATE
        ,projmast.PRODUCT
        ,extendt6.TOTALBILLD
        ,[TOTAL_PROJ] - [extendt6] ! [TOTALBILLD] AS Remaining
        ,JBCSTLOG.QTY
        ,[HOURSLABOR] - [JBCSTLOG] ! [QTY] AS [Hrs remaining]
    FROM (
        [Product Codes] INNER JOIN (
            (
                projmast INNER JOIN [Status Codes] ON projmast.STATUS = [Status Codes].[Service Codes]
                ) LEFT JOIN extendt6 ON projmast.NUMBER = extendt6.PROJNUM
            ) ON [Product Codes].[Service Codes] = projmast.PRODUCT
        )
    INNER JOIN JBCSTLOG ON projmast.HOURSLABOR = JBCSTLOG.QTY
    WHERE (
            ((projmast.STATUS) = "A")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
        OR (
            ((projmast.STATUS) = "B")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
        OR (
            ((projmast.STATUS) = "C")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
        OR (
            ((projmast.STATUS) = "D")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
        OR (
            ((projmast.STATUS) = "E")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
        OR (
            ((projmast.STATUS) = "F")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
        OR (
            ((projmast.STATUS) = "H")
            AND ((JBCSTLOG.TRANSTYPE) = "L")
            )
    GROUP BY projmast.NUMBER
        ,projmast.PROJPODATE
        ,[Status Codes].Description
        ,[Product Codes].Description
        ,projmast.NAME
        ,projmast.SERIALNUM
        ,projmast.DESCRIPTIO
        ,projmast.SALESMAN
        ,projmast.TECHNICIAN
        ,projmast.TOTAL_PROJ
        ,projmast.TOTALCOST
        ,projmast.TOTAL_PART
        ,projmast.TOTAL_LBR
        ,projmast.HOURSLABOR
        ,projmast.DATEIN
        ,projmast.DATEDUE
        ,projmast.START_DATE
        ,projmast.PRODUCT
        ,extendt6.TOTALBILLD
        ,[TOTAL_PROJ] - [extendt6] ! [TOTALBILLD]
        ,JBCSTLOG.QTY
        ,[HOURSLABOR] - [JBCSTLOG] ! [QTY]
    HAVING (
            (
                (projmast.PRODUCT) <> "X"
                OR (projmast.PRODUCT) = "S"
                OR (projmast.PRODUCT) = "W"
                )
            )
    ORDER BY projmast.SALESMAN;
    This sql does not look like the questions/activity in the thread.

    What happened to this rather simple criteria
    Transtype = "L" and status in ("A","B","C""D","E","F","G","H")

    We seem to be going in circles.

    Please clarify what you are trying to do and the query you are using/having issues with.

    ?? Did you try Bulzie's approach as posted in # 19???

  6. #36
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    Yes, but with just those 3 fields, im getting negative numbers from QTY

  7. #37
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    Just to reiterate what exactly we're trying to accomplish. We're basically trying to duplicate all the field columns and information this particular spreadsheet tells us because everything is manually entered by a technician, and thus we are switching to using Access to be able to get automated reports from our databases. The question right now is how to obtain the correct QTY (which is posted labor hours) based on different project numbers (NUMBER) when job status (STATUS is either A,B,C,D,E,F,G,H - excluding all other alphabets) and when Transtype is L (excluding other alphabets).

    Click image for larger version. 

Name:	Capture2542354.jpg 
Views:	11 
Size:	42.7 KB 
ID:	29477


    SELECT projmast.NUMBER, projmast.PROJPODATE, [Status Codes].Description, [Product Codes].Description, projmast.NAME, projmast.SERIALNUM, projmast.DESCRIPTIO, projmast.SALESMAN, projmast.TECHNICIAN, projmast.TOTAL_PROJ, projmast.TOTALCOST, projmast.TOTAL_PART, projmast.TOTAL_LBR, projmast.HOURSLABOR, projmast.DATEIN, projmast.DATEDUE, projmast.START_DATE, projmast.PRODUCT, extendt6.TOTALBILLD, [TOTAL_PROJ]-[extendt6]![TOTALBILLD] AS Remaining, JBCSTLOG.QTY, [HOURSLABOR]-[JBCSTLOG]![QTY] AS [Hrs remaining]
    FROM ([Product Codes] INNER JOIN ((projmast INNER JOIN [Status Codes] ON projmast.STATUS = [Status Codes].[Service Codes]) LEFT JOIN extendt6 ON projmast.NUMBER = extendt6.PROJNUM) ON [Product Codes].[Service Codes] = projmast.PRODUCT) INNER JOIN JBCSTLOG ON projmast.HOURSLABOR = JBCSTLOG.QTY
    WHERE (((projmast.STATUS)="A") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="B") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="C") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="D") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="E") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="F") AND ((JBCSTLOG.TRANSTYPE)="L")) OR (((projmast.STATUS)="H") AND ((JBCSTLOG.TRANSTYPE)="L"))
    GROUP BY projmast.NUMBER, projmast.PROJPODATE, [Status Codes].Description, [Product Codes].Description, projmast.NAME, projmast.SERIALNUM, projmast.DESCRIPTIO, projmast.SALESMAN, projmast.TECHNICIAN, projmast.TOTAL_PROJ, projmast.TOTALCOST, projmast.TOTAL_PART, projmast.TOTAL_LBR, projmast.HOURSLABOR, projmast.DATEIN, projmast.DATEDUE, projmast.START_DATE, projmast.PRODUCT, extendt6.TOTALBILLD, [TOTAL_PROJ]-[extendt6]![TOTALBILLD], JBCSTLOG.QTY, [HOURSLABOR]-[JBCSTLOG]![QTY]
    HAVING (((projmast.PRODUCT)<>"X" Or (projmast.PRODUCT)="S" Or (projmast.PRODUCT)="W"))
    ORDER BY projmast.SALESMAN;

  8. #38
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    Click image for larger version. 

Name:	C25424apture.PNG 
Views:	11 
Size:	58.0 KB 
ID:	29480Click image for larger version. 

Name:	C25463456apture.PNG 
Views:	11 
Size:	60.0 KB 
ID:	29481

  9. #39
    newuserthatneedsassistanc is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2017
    Posts
    66
    still need help with this

  10. #40
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My guess is that you have to break the speadsheet into "relational tables" based on your knowledge of the business. You can rarely take an excel sheet and use it directly as an Access table.
    If you have a clear understanding of your business, and can describe that on paper in simple English, then that would help readers understand more of what you are trying to do.
    Based on the description, your knowledge of the business and the the spreadsheet, you would define the entities and relationships. You could model and test these to ensure your model supports your business.

    I would also suggest that, instead of working with a large, relatively complex query, start with something smaller (perhaps 1 or 2 tables) get it working and gradually add to it. Check thee query and results with each iteration. Understand the SQL and add complexity one step at a time.

    Good luck.

  11. #41
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm with orange on this. Your two main tables appear to be designed like a spreadsheet (short and wide) instead of a database (tall and narrow).

    For instance, the table "projmast" (project master?) looks like it could be broken up into at least 4 tables (from what is visible).

    And I would have table "extendt6" with 10 fields:

    extendID_PK (Autonumber) (PK)
    ProjNumID_FK (Number - Long) (FK link to table "projmast")
    c_count
    c_Text
    c_PSale
    c_SSale
    c_LSale
    c_Tax
    c_Total (probably wouldn't have this field. It can be calculated)
    c_Sequence (this would be the sequence the data belonged to. In the image, you show "c1_" through "c4_". So this would hold the 1, 2, 3, 4, ..., n)



    Also, why does the table "Product Codes" have a PK field of "Service Codes"???

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-23-2016, 12:16 PM
  2. Importing Information From Microsoft Word to Microsoft Access
    By Runlynch in forum Import/Export Data
    Replies: 9
    Last Post: 01-17-2016, 06:21 PM
  3. Replies: 13
    Last Post: 10-06-2015, 09:12 AM
  4. Replies: 1
    Last Post: 09-13-2011, 01:52 PM
  5. Replies: 0
    Last Post: 10-13-2010, 03:28 PM

Tags for this Thread

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