Syntax Error in Select Query

[BurtonBach]

I am working to create some reports that find and calculate round times and costs related to trucking. The data is gathered from a tracking system and I cannot control the structure of the table.

I am attempting to benchmark off of some queries that were written by a college that I no longer have access to for a similar application but I am running in to some syntax errors and do not know why.

Here is my query so far:

SELECT x_logs.alias, x_logs.time, x_logs.date, x_logs.status, x_logs.address, [x_logs.time]-(Select Max([x_logs.time])FROM x_logs AS DUP WHERE
x_logs.alias = DUP.x_logs.alia s AND x_logs.date = DUP.x_logs.date AND x_logs.status = DUP.x_logs.stat us
WHERE ((x_logs.date) Between "06/15/07" And "06/30/07"));


I imagine there will be questions in order to help me however I am not sure what to list here in advance. Please ask any questions and I will happily answer. I can even post some of the table I'm working with if that will help, although I am not sure how to do so.

Thanks in advance!

[MikeTheBike]

Hi

There are a number of think wrong with the query, but more information is required on what you are trying to achieve.

Are you really trying to subtract the Max time from the time, this will give negative time !?

I assume this is all based on one table.

My best (wild) guess is

SELECT x_logs.alias, x_logs.time, x_logs.date, x_logs.status, x_logs.address, [x_logs.time]-DUP.MaxTime FROM x_logs.alias INNER JOIN [Select x_logs.alias, x_logs.status, Max([x_logs.time] as MaxTime FROM x_logs GROUP BY .x_logs.alias, x_logs.status]. AS DUP ON
x_logs.alias = DUP.alias AND x_logs.status = DUP.x_logs.stat us
WHERE ((x_logs.date) Between #06/15/07# And #06/30/07#));

I must stress this is olny a guess and much more information is required.


MTB

[BurtonBach]

Thank you very much for the reply.

I am getting a Syntax Error in the From Clause.

Please let me know what additional information is necessary. I will attempt to add some information, but any questions that help me know what you need to know would be appreciated.

The table comes from a automated data collection system that I have linked into using ODBC. I believe its foxpro based. Is there a way to paste into this posting some of the table?

Anyway, the records are created as the trucks travel about. A group of anywhere from a couple of records to ten or so will constitute one round trips worth of information. It records when the arrive at the material source, leave the material source, arrive at the job location, and leave the job location. It also records the job number they are working on.

In the end I am looking for the length of time each part of the round trip took. So I want the report to show me how long they took to travel to the material source, how long they were there, how long they took to travel to the job, how long they were at the job, and how long the whole round trip totaled.

Does that help any? Do you know why I'm getting the Syntax Error in the From Clause?

Thanks Again!

[MikeTheBike]

Hi

Must dash now untill Monday, but what is the Error Message (or is just Syntax error on from clause).

If this info/query in from one table can you list the relevant fields ie

I assume Date is a date/time with just the date; and Time is a date/time with just the time (do trips start one day and finish the next?)

How do you differentiate between trucks (which field); how do you differentiate between jobs and trips (which field) etc?


MTB

[BurtonBach]

Thanks Mike,

If anyone else can throw in some thoughts before Mike's return it would be appreciated.

The relevant fields are: alias (Truck # (which shows up actuall as 'Truck 3' or 'Truck 1)), time (hh:mm:ss), date (mm:dd:yy), status (Arrive Job, Leave Job, etc) and address. These are all on the same table named 'x_logs'.

Trips do not generally start one day and end the next.

The way we get the job number is in the status field. The real sticker with this query is that we have multiple records per status per trip. The system checks the truck every 15 minutes unless manually told to do so sooner or unless the driver enters additional information, i.e., enters the job number. Every job's trips may take a different amount of time so the number of records per a trip changes quite a bit and the number of records per status changes. So my approach has been to try to calculate off of the first record of each status type. For example if a the first record comes in for the Arrive Job status I consider that the actual arrive job time, not when 15 minutes later the system checks automatically and posts another Arrive Job record which it will continue to do until the driver indicates that he has left the job in which case a Leave Job record is produced. So then I need the time between the first Arrive Job record of this set of records and the first Leave Job.

I hope thats not too much information, but I'm trying to guess at what will need to be known to help with the query.

Thanks again!

[BurtonBach]

I forgott to answer the question about the error. It only says 'Syntax Error in From Clause'

[MMcCarthy]

There was a problem with the brackets on the subselect. Access does this when you edit a query containing a subselect. Some bug in the compiler. Mike probably copied and pasted without realising.

Try it now ...
[CODE=sql]
SELECT x_logs.alias, x_logs.time, x_logs.date, x_logs.status, x_logs.address, [x_logs.time]-DUP.MaxTime
FROM x_logs.alias INNER JOIN
(Select x_logs.alias, x_logs.status, Max([x_logs.time]) as MaxTime FROM x_logs GROUP BY .x_logs.alias, x_logs.status) AS DUP
ON x_logs.alias = DUP.alias
AND x_logs.status = DUP.x_logs.stat us
WHERE ((x_logs.date) Between #06/15/07# And #06/30/07#));
[/CODE]

[BurtonBach]

Thank you for joining in on my question. I am definately pushing the edge of my query abilities here and learning a lot.

I attempted your answer and I'm getting the following:

"Syntax error in JOIN operation."

In addition to thoughts on why that is happening I would like to aske the following:

I started this whole project attempting to benchmark off of this query developed by a co-worker (that I no longer have access to) for a similar project:

SELECT Transactions.Transaction_Idx, Transactions.TicketNum, Transactions.Customers_Customer_ID, Transactions.Jobs_Job_ID, Transactions.Trucks_Truck_ID, Transactions.Transactions_TonsNetWeight, Transactions.VoidTkt, Transactions.ModifiedTkt, Transactions.Reprinted, Transactions.Simulated, Transactions.Prior_Ticket, Transactions.Transactions_DateTimeTransaction, [Transactions_DateTimeTransaction]-(Select Max([Transactions_DateTimeTransaction]) FROM Transactions AS DUP WHERE
Transactions.Customers_Customer_ID = DUP.Customers_Customer_ID  AND Transactions.Jobs_Job_ID = DUP.Jobs_Job_ID AND Transactions.VoidTkt = DUP.VoidTkt AND Transactions.Trucks_Truck_ID = DUP.Trucks_Truck_ID AND DUP.Transactions_DateTimeTransaction < Transactions.Transactions_DateTimeTransaction) AS CycleTime INTO mktblCycleTime
FROM Transactions
WHERE (((Transactions.TicketNum)>"0") AND ((Transactions.Jobs_Job_ID)="63222") AND ((Transactions.VoidTkt)=0) AND ((Transactions.Transactions_DateTimeTransaction) Between #6/2/2006 1:0:0# And #6/2/2006 23:0:0#))
ORDER BY Transactions.Transactions_DateTimeTransaction;

It was from this that I came up with the query above to start with. I am noticing that this query doesn't have the inner join that MTB was kind enough to suggest above. How is his approach different from the one my co-worker came up with? Which is better in your opinion?

[MMcCarthy]

Try it now ...

[CODE=sql]
SELECT x_logs.alias, x_logs.time, x_logs.date, x_logs.status, x_logs.address, [x_logs.time]-DUP.MaxTime
FROM x_logs INNER JOIN
(Select x_logs.alias, x_logs.status, Max([x_logs.time]) as MaxTime FROM x_logs GROUP BY .x_logs.alias, x_logs.status) AS DUP
ON x_logs.alias = DUP.alias
AND x_logs.status = DUP.x_logs.stat us
WHERE ((x_logs.date) Between #06/15/07# And #06/30/07#));
[/CODE]

[MMcCarthy]

The query you posted really doesn't look like it would work. It's over complicated at least. That kind of subquery is not designed to be handled by Access.

I've tried to simplify the existing query but it is way to complicated.

Can you tell me what you want your query to do exactly. What is it's purpose and what criteria/rules do you need to apply. Just explain in plain English and we'll try to work it out from there.

[MikeTheBike]

Hi

Yes I did miss the missing bracket (and any other omissions!), but my main concern is that in any case it will not return the required result. ie the elapse time between arrival and leaving.

Therefore, based on x_logs having the following fields

Truck# (Text)
Job (Text)
date (date/Time)
time (date/time)
Status (Text)

and Status values are "Arrive" and "Leave"

then

This calculates the elapse time (assuming arrive and leave are the same day)

SELECT
Arrive.[Truck#],
Arrive.Job,
Arrive.date,
Arrive.time, [Leave].[time],
([Leave].[time]-[Arrive].[time])*24 AS [ElapseTime (Hrs)]
FROM
x_logs AS Arrive
INNER JOIN
(SELECT
x_logs.[Truck#],
x_logs.Job,
x_logs.date, x_logs.time
FROM x_logs
WHERE (((x_logs.statu s)="Leave"))) AS Leave
ON (Leave.time > Arrive.time)
AND (Arrive.[Truck#] = Leave.[Truck#])
AND (Arrive.Job = Leave.Job)
AND (Arrive.date = Leave.date)
WHERE (((Arrive.statu s)="Arrive"))

However, this assumes that only "Arrive" and "Leave" are valid Status values, and, therefore, that the table composite PK is composed of all the field except the time field. This I believe not to be the case.

I also assume that only one visit to each location is made by each truck on a given date?

Therefore, what are valid status values and how do they relate to locations/jobs ?

Hope this makes some kind of sense.


MTB

[BurtonBach]

Thank you very much to both of you for attempting to help me.

I am sorry for the delay in posting a reply. The last couple days here at the office have been rough.

I attempted to describe things thoroughly above but maybe I didn't do well so I am going to try again.

Here is a text past of the relevent columns in my table (this is just a small clip from one trucks returns for part of a day where the field names correspond as follows: 'vehicle_nu'=15 02, 'alias'=Truck2, 'time'=07:10:09 , 'date'=06/22/07, and 'status'=Leave Plant, etc.):

vehicle_nu alias time date status

1502 Truck 2 07:10:09 06/22/07 Leave Plant
1502 Truck 2 07:25:09 06/22/07 Leave Plant
1502 Truck 2 07:27:06 06/22/07 Arrive Job
1502 Truck 2 07:55:10 06/22/07 Arrive Job
1502 Truck 2 08:10:10 06/22/07 Leave Job
1502 Truck 2 08:40:28 06/22/07 Arrive Plant
1502 Truck 2 08:40:10 06/22/07 Arrive Plant
1502 Truck 2 08:47:38 06/22/07 Badge = 140553
1502 Truck 2 09:34:00 06/22/07 Leave Plant
1502 Truck 2 10:07:44 06/22/07 Leave Plant
1502 Truck 2 10:14:23 06/22/07 Leave Plant
1502 Truck 2 10:25:13 06/22/07 Leave Plant
1502 Truck 2 10:40:13 06/22/07 Arrive Job
1502 Truck 2 11:10:14 06/22/07 Arrive Job
1502 Truck 2 11:46:28 06/22/07 Arrive Job
1502 Truck 2 12:06:54 06/22/07 Arrive Job
1502 Truck 2 12:17:15 06/22/07 Arrive Job
1502 Truck 2 12:21:55 06/22/07 Arrive Job
1502 Truck 2 12:36:31 06/22/07 Leave Job
1502 Truck 2 12:59:29 06/22/07 Arrive Plant

The system logs an entry every 15 min and whenever the driver changes status, so some statuses are duplicated because it keeps the previous status until the status changes. Thus the first time of each status is the one I'm interested in.

The time lapse between the first leave plant and first arrive job was 17 min.
The time lapse between the first arrive job and the first leave job was 43 min.
The time lapse between the first leave job and the first arrive plant is 30 min.
The time lapse between the first arrive plant and the second leave plant is 54 min.

And so on...

I need a query that will compute all these time lapses for me for each round trip and then I can compare these numbers to what they should be and total them etc.

The Badge = status field is the job number for that round trip. This needs to be tied to the query somehow so that we can know what the round trip time should have been.

Is all this possible?

Also, I don't mean to ask all of you to do all my work for me, if you can help me finish the query language for one aspect of this so that I can then learn the theory and write the rest to apply to things how I need it to that will be great. I hope this added information is helpful. I am happy to answer any further questions that would clarify anything.

[MMcCarthy]

I'm not sure if it is just the data you gave but how can truck 1502 leave the plant at 7:10 and again at 7:25.

Secondly the structure of your table is not ideal I would change it as follows:

tblTruck
vehicle_nu (primary key)
alias

tblTransport
transportID (primary key, can be autonumber)
vehicle_nu (foreign key reference to primary key of tblTruck)
LeavePlant (date/time datatype formated to hold General Date (date and time))
ArrivePlant
LeaveJob
ArriveJob

This will give you much better control over the status of all vehicles

[BurtonBach]

I cannot change the structure of the table, I am getting this through an ODBC into the back end of a proprietary softare. The table shows up in my Access database as a linked table.

The reason that there are two Arrive Plant records is because the truck actually arrived at the plant on the first record. The second record is created by the automatic check that usually occurs every 15 min and it keep the status that was there before. If a truck stayed at a location for an extended period of time is conceivable that there would be a half dozen records of the same status created by the automatic checks and I would only be interested in the first one.

[MMcCarthy]

I cannot change the structure of the table, I am getting this through an ODBC into the back end of a proprietary softare. The table shows up in my Access database as a linked table.

The problem here is there is no way to identify the different journeys by a truck. You won't solve this with a query. The only solution I can think of would involve a lot of vba code.

Unfortunately, I don't have the time to help you with that but maybe one of the other experts can.