Transtype = L is Labor, there are many other different Transtypes
Transtype = L is Labor, there are many other different Transtypes
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?
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.
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
Here is the sql in a formatted view (via Poorsql). It might help in deciphering things.
This sql does not look like the questions/activity in the thread.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;
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???
Yes, but with just those 3 fields, im getting negative numbers from QTY
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).
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;
still need help with this
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.
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"???