Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations dencom on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sort By Calculated field

Status
Not open for further replies.

psperry2

Programmer
Nov 7, 2007
141
US
My SQL statement Does not sort by Name. Is there another way to do this?

PARAMETERS prmEnd DateTime, prmStart DateTime;
INSERT INTO ztrptPendingScheduled ( ISInvID, Name, ContractID, Contract, StepID, StepName, StepDateStart, StepDateEnd, StepStatus )
SELECT tblPersons_steps.ISInvID, [InvLName] & ", " & [InvFname] AS Name, tblPersons.ContractID, TrefContract.Contract, tblPersons_Steps.StepID, trefSteps.StepName, tblPersons_Steps.StepDateStart, tblPersons_Steps.StepDateEnd, tblPersons_Steps.StepStatus
FROM trefSteps INNER JOIN ((trefContract INNER JOIN tblPersons ON trefContract.ContractID = tblPersons.ContractID) INNER JOIN tblPersons_Steps ON tblPersons.InvID = tblPersons_Steps.ISInvID) ON trefSteps.StepID = tblPersons_Steps.StepID
ORDER BY 2;
 
Using the ORDER BY clause in an append query seems irrelevant to me ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
You are doing an INSERT so ordering is not really relevant.

It doesn't matter in what order records are inserted and any order of insertion has no bearing on how the records are retrieved when you look at the "ztrptPendingScheduled" table after the insert.
 
I am trying to use this temp table as a source of records for a report. I guess I could index the name field so that the report would group everything for one person together.
 
Create a query and base your report on that.
Code:
Select ISInvID, [Name], ContractID, Contract, StepID, StepName, StepDateStart, StepDateEnd, StepStatus 

From ztrptPendingScheduled

Order By 2
 
OK I changed the SQL and will not sort but I am now getting a syntax error I don't understand. Been looking at this for 10 minutes and can't see anything to fix.

Syntax error (missing operator) in query expression 'tblPersons_Steps.StepID=trefSteps.StepID
INNER JOIN tblPersons ON tblPersons.InvID = tblPersons_Steps.ISInvID INNER JOIN trefContract ON trefContract.ContractID = tblPersons.ContractID'.

PARAMETERS prmEnd DateTime, prmStart DateTime;
INSERT INTO ztrptPendingScheduled ( ISInvID, Name, ContractID, Contract, StepID, StepName,
StepDateStart, StepDateEnd, StepStatus )
SELECT tblPersons_steps.ISInvID, [InvLName] & ", " & [InvFname] AS Name, tblPersons.ContractID,
TrefContract.Contract, tblPersons_Steps.StepID, trefSteps.StepName, tblPersons_Steps.StepDateStart,
tblPersons_Steps.StepDateEnd, tblPersons_Steps.StepStatus
FROM tblPersons_Steps INNER JOIN trefSteps ON tblPersons_Steps.StepID = trefSteps.StepID
INNER JOIN tblPersons ON tblPersons.InvID = tblPersons_Steps.ISInvID
INNER JOIN trefContract ON trefContract.ContractID = tblPersons.ContractID
WHERE tblPersons_Steps.StepStatus IN ('Pending','Scheduled') AND
tblPersons_Steps.StepDateStart >= prmEnd AND tblPersons_Steps.StepDateEnd <= prmEnd;
 
Access loves parentheses
Code:
FROM [red](([/red]tblPersons_Steps INNER JOIN trefSteps ON tblPersons_Steps.StepID = trefSteps.StepID[red])[/red]
INNER JOIN tblPersons ON tblPersons.InvID = tblPersons_Steps.ISInvID[red])[/red]
INNER JOIN trefContract ON trefContract.ContractID = tblPersons.ContractID
 
I already tried that, I get "Syntax error in From Clause" and can't save the query with the Parenthesis
 
This is syntactically correct but does not give me any records.

PARAMETERS prmEnd DateTime, prmStart DateTime;
INSERT INTO ztrptPendingScheduled ( ISInvID, Name, ContractID, Contract, StepID, StepName, StepDateStart, StepDateEnd, StepStatus )
SELECT tblPersons_steps.ISInvID, [InvLName] & ", " & [InvFname] AS Name, tblPersons.ContractID, TrefContract.Contract, tblPersons_Steps.StepID, trefSteps.StepName, tblPersons_Steps.StepDateStart, tblPersons_Steps.StepDateEnd, tblPersons_Steps.StepStatus
FROM trefSteps INNER JOIN ((TrefContract RIGHT JOIN tblPersons ON trefContract.ContractID = tblPersons.ContractID) INNER JOIN (TrefStatus RIGHT JOIN tblPersons_Steps ON trefStatus.StatusID = tblPersons_Steps.StepStatus) ON tblPersons.InvID = tblPersons_Steps.ISInvID) ON trefSteps.StepID = tblPersons_Steps.StepID
WHERE tblPersons_Steps.StepStatus IN ('Pending','Scheduled') AND
tblPersons_Steps.StepDateStart >= prmEnd AND tblPersons_Steps.StepDateEnd <= prmEnd;
 
Strange. Access lets me save this
Code:
PARAMETERS prmEnd DateTime, prmStart DateTime;

INSERT INTO ztrptPendingScheduled ( ISInvID, [Name], ContractID, Contract, StepID, StepName, 
                                    StepDateStart, StepDateEnd, StepStatus )

SELECT PS.ISInvID, 
       [InvLName] & ", " & [InvFname] AS [Name], 
       P.ContractID, 
       C.Contract, 
       PS.StepID, 
       S.StepName, 
       PS.StepDateStart, 
       PS.StepDateEnd, 
       PS.StepStatus

FROM  ((tblPersons_Steps As PS 
        INNER JOIN trefSteps    As S   ON PS.StepID    = S.StepID )
        INNER JOIN tblPersons   As P   ON P.InvID      = PS.ISInvID )
        INNER JOIN trefContract As C   ON C.ContractID = P.ContractID

WHERE PS.StepStatus    IN ('Pending','Scheduled') 
  AND PS.StepDateStart >= [red]prmStart[/red]
  AND PS.StepDateEnd   <= prmEnd;

I changed the part in [red]red[/red] from prmEnd to prmStart.
 
What happens if you just run the select part as a query?

Could it be the "prmEnd" in both places in the WHERE clause?
 
That was a good catch on the PrmEnd being in the where clause twice. I was also able to save your version of the SQL statement GOLOM. After I run it I get zero records.

I looked at the source table, tblPersons_Steps and think I should be getting 3 records.
 
It takes quite a bit of analysis to be sure what you should be getting when you have several JOINs and some WHERE restrictions.

Were I doing it I would start doing breakdowns by running a query with just one JOIN, then pull that query into another query and do another JOIN, etc. At this point it is difficult to tell if the records that you think should be there are being eliminated by the JOINs in the From clause or by the constraints in the WHERE clause.
 
ok are you sure that those three records are also in trefSteps AND tblPersons AND trefContract. The records have to be in all 4 tables in order to be returned in the query.

Leslie

Have you met Hardy Heron?
 
I definitely have records that are Pending or Scheduled. I get zero records still. I simplfied the query to :

INSERT INTO ztrptPendingScheduled ( ISInvID, Name, ContractID, Contract, StepID, StepName, StepDateStart, StepDateEnd, StepStatus )
SELECT PS.ISInvID, [InvLName] & ", " & [InvFname] AS Name, P.ContractID, C.Contract, PS.StepID, S.StepName, PS.StepDateStart, PS.StepDateEnd, PS.StepStatus
FROM ((tblPersons_Steps AS PS INNER JOIN trefSteps AS S ON PS.StepID = S.StepID) INNER JOIN tblPersons AS P ON P.InvID = PS.ISInvID) INNER JOIN trefContract AS C ON C.ContractID = P.ContractID
WHERE PS.StepStatus IN ('Pending','Scheduled');
 
I created a new query to test just the inner SQL and still get no records.

SELECT PS.ISInvID, [InvLName] & ", " & [InvFname] AS Name, P.ContractID, C.Contract, PS.StepID, S.StepName, PS.StepDateStart, PS.StepDateEnd, PS.StepStatus
FROM ((tblPersons_Steps AS PS INNER JOIN trefSteps AS S ON PS.StepID = S.StepID) INNER JOIN tblPersons AS P ON P.InvID = PS.ISInvID) INNER JOIN trefContract AS C ON C.ContractID = P.ContractID
WHERE PS.StepStatus IN ('Pending','Scheduled');
 
I further simplfied the SQL to :

Select * from tblPersons_Steps WHERE tblPersons_Steps.StepStatus IN ('Pending','Scheduled');

And still I get no results. When I look through the tblPersons_Steps table I see dozens of records that are Pending or Scheduled
 
I got fooled by believing the table view. What is stored in the table is actually "Pend" for Pending and "Schd" for scheduled.
 
Try this
Code:
Select * from tblPersons_Steps 

WHERE [red]Trim$([/red]StepStatus[red])[/red] IN ('Pending','Scheduled');
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top