Problem with SQL statement


G

Guest

This is a re-post of my original for which I have hit a wall

I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry

my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

copied the following into a query in SQL view and saved

SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);

I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window

If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Help!
 
Ad

Advertisements

J

Jason Lepack

See comments inline.

This is a re-post of my original for which I have hit a wall

I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry

my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved

SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);

I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window

B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID

That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:

SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);

Cheers,
Jason Lepack

PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
G

Guest

Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.

Posted the last code as instructed

'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'

Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.

Can I assume I replace every startdate with dateout?

Jason Lepack said:
See comments inline.

This is a re-post of my original for which I have hit a wall

I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry

my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved

SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);

I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window

B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID

That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:

SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);

Cheers,
Jason Lepack

PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
J

Jason Lepack

Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.

Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.

Posted the last code as instructed

'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'

Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.

Can I assume I replace every startdate with dateout?

Jason Lepack said:
See comments inline.
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
G

Guest

Ok lets start again

here are my tables

Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries

Example

EquipID 1 came in and out in Jan then again in Mar

A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug

Jason Lepack said:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.

Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.

Posted the last code as instructed

'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'

Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.

Can I assume I replace every startdate with dateout?

Jason Lepack said:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
J

Jason Lepack

If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.

This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.

SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);



Ok lets start again

here are my tables

Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries

Example

EquipID 1 came in and out in Jan then again in Mar

A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug

Jason Lepack said:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement
Help!
Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
Ad

Advertisements

G

Guest

That works perfectly many many thanks

If at all possible could you explain the coding very breifly - if not no
worries :)

Jason Lepack said:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.

This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.

SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);



Ok lets start again

here are my tables

Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip

I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries

Example

EquipID 1 came in and out in Jan then again in Mar

A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug

Jason Lepack said:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
J

Jason Lepack

Any time!! I prefer it when people want to understand what they're
doing.

SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);

This query is actually made up of two queries.

The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID

(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)

This query selects the most recent DateOut for each piece of
equipment.

So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.

SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);

The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)

Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.

The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.

I've seen some rediculous SQL and it's tough to read. Picture this:

tblSupercalafragilisticexpialidocious:
filed1
field2
field3

tblAnotherreallylongstupidname
field1
field2
field3

SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3

That looks absolutley rediculous and it took me forever to copy and
paste that together.

It looks a lot better with aliases:

SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3

Any other questions about how this works, just post back and I'd be
glad to answer them.

Cheers,
Jason Lepack

That works perfectly many many thanks

If at all possible could you explain the coding very breifly - if not no
worries :)

Jason Lepack said:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries
Example
EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement
Help!
Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
G

Guest

ok grasped the basics there - tried to add a little more and stumbled so
mailed some data

Jason Lepack said:
Any time!! I prefer it when people want to understand what they're
doing.

SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);

This query is actually made up of two queries.

The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID

(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)

This query selects the most recent DateOut for each piece of
equipment.

So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.

SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);

The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)

Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.

The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.

I've seen some rediculous SQL and it's tough to read. Picture this:

tblSupercalafragilisticexpialidocious:
filed1
field2
field3

tblAnotherreallylongstupidname
field1
field2
field3

SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3

That looks absolutley rediculous and it took me forever to copy and
paste that together.

It looks a lot better with aliases:

SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3

Any other questions about how this works, just post back and I'd be
glad to answer them.

Cheers,
Jason Lepack

That works perfectly many many thanks

If at all possible could you explain the coding very breifly - if not no
worries :)

Jason Lepack said:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries

EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
J

Jason Lepack

Ok, the first thing I did was remove the lookup fields (I changed the
lookup back to text box). They can be more a source of confusion than
anything else. If you want that functionality then add it on a form
once you create those.

For now, what exactly do you want to Output in this query?

Start with this query:
SELECT
Cust.Customer,
Cust.Address,
Cust.Town,
Cust.Pcode,
Equip.Make,
Equip.Model,
Equip.Serial,
Job.DateIn,
Job.DateOut
FROM
Cust
INNER JOIN Equip
ON Cust.CustID = Equip.CustID
INNER JOIN Job
ON Equip.EquipID = Job.EquipID
INNER JOIN
(
SELECT
Max(DateOut) AS LastOutDate,
EquipID
FROM Job
GROUP BY EquipID
) AS B
ON (Job.DateOut = B.LastOutDate)
AND (Job.EquipID = B.EquipID);

Take a look at it in Design View and see how the joins are set up and
how the data is selected.

Any more questions just post them back.

Cheers,
Jason Lepack

ok grasped the basics there - tried to add a little more and stumbled so
mailed some data

Jason Lepack said:
Any time!! I prefer it when people want to understand what they're
doing.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
This query is actually made up of two queries.
The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID
(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)
This query selects the most recent DateOut for each piece of
equipment.
So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)
Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.
The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.
I've seen some rediculous SQL and it's tough to read. Picture this:


SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3
That looks absolutley rediculous and it took me forever to copy and
paste that together.
It looks a lot better with aliases:
SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3
Any other questions about how this works, just post back and I'd be
glad to answer them.
Cheers,
Jason Lepack
That works perfectly many many thanks
If at all possible could you explain the coding very breifly - if not no
worries :)
:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries
Example
EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement
Help!
Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
G

Guest

That gave a syntax error but woking on it

Jason Lepack said:
Ok, the first thing I did was remove the lookup fields (I changed the
lookup back to text box). They can be more a source of confusion than
anything else. If you want that functionality then add it on a form
once you create those.

For now, what exactly do you want to Output in this query?

Start with this query:
SELECT
Cust.Customer,
Cust.Address,
Cust.Town,
Cust.Pcode,
Equip.Make,
Equip.Model,
Equip.Serial,
Job.DateIn,
Job.DateOut
FROM
Cust
INNER JOIN Equip
ON Cust.CustID = Equip.CustID
INNER JOIN Job
ON Equip.EquipID = Job.EquipID
INNER JOIN
(
SELECT
Max(DateOut) AS LastOutDate,
EquipID
FROM Job
GROUP BY EquipID
) AS B
ON (Job.DateOut = B.LastOutDate)
AND (Job.EquipID = B.EquipID);

Take a look at it in Design View and see how the joins are set up and
how the data is selected.

Any more questions just post them back.

Cheers,
Jason Lepack

ok grasped the basics there - tried to add a little more and stumbled so
mailed some data

Jason Lepack said:
Any time!! I prefer it when people want to understand what they're
doing.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
This query is actually made up of two queries.
The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID
(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)
This query selects the most recent DateOut for each piece of
equipment.
So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)
Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.
The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.
I've seen some rediculous SQL and it's tough to read. Picture this:


SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3
That looks absolutley rediculous and it took me forever to copy and
paste that together.
It looks a lot better with aliases:
SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3
Any other questions about how this works, just post back and I'd be
glad to answer them.
Cheers,
Jason Lepack
That works perfectly many many thanks
If at all possible could you explain the coding very breifly - if not no
worries :)
:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries

EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
Ad

Advertisements

J

Jason Lepack

Yeah, access is picky about joins with subselects apparently, as well
as brackets, here's how I got it to work.

Save the subselect as a query by itself (getLastOutDate)

Go into query designer.

Add Cust, Equip, Job, and getLastOutDate to the query.

Connect getLastOutDate.EquipID to Job.EquipID
Connect Job.DateOut to getLastOutDate.LastOutDate
Connet Equip.custID to Cust.CustID
Connect Equip.EquipId to Job.EquipID

and select the fields that you want to display (not the id fields
preferably)

Let me know how it goes.

Cheers,
Jason Lepack

That gave a syntax error but woking on it

Jason Lepack said:
Ok, the first thing I did was remove the lookup fields (I changed the
lookup back to text box). They can be more a source of confusion than
anything else. If you want that functionality then add it on a form
once you create those.
For now, what exactly do you want to Output in this query?
Start with this query:
SELECT
Cust.Customer,
Cust.Address,
Cust.Town,
Cust.Pcode,
Equip.Make,
Equip.Model,
Equip.Serial,
Job.DateIn,
Job.DateOut
FROM
Cust
INNER JOIN Equip
ON Cust.CustID = Equip.CustID
INNER JOIN Job
ON Equip.EquipID = Job.EquipID
INNER JOIN
(
SELECT
Max(DateOut) AS LastOutDate,
EquipID
FROM Job
GROUP BY EquipID
) AS B
ON (Job.DateOut = B.LastOutDate)
AND (Job.EquipID = B.EquipID);
Take a look at it in Design View and see how the joins are set up and
how the data is selected.
Any more questions just post them back.
Cheers,
Jason Lepack
ok grasped the basics there - tried to add a little more and stumbled so
mailed some data
:
Any time!! I prefer it when people want to understand what they're
doing.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
This query is actually made up of two queries.
The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID
(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)
This query selects the most recent DateOut for each piece of
equipment.
So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)
Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.
The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.
I've seen some rediculous SQL and it's tough to read. Picture this:
tblSupercalafragilisticexpialidocious:
filed1
field2
field3
tblAnotherreallylongstupidname
field1
field2
field3
SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3
That looks absolutley rediculous and it took me forever to copy and
paste that together.
It looks a lot better with aliases:
SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3
Any other questions about how this works, just post back and I'd be
glad to answer them.
Cheers,
Jason Lepack
That works perfectly many many thanks
If at all possible could you explain the coding very breifly - if not no
worries :)
:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries
Example
EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement
Help!
Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
G

Guest

Ok this worked

Getlastdateout
SELECT Max(Job.DateOut) AS LastOutDate, Equip.EquipID
FROM Equip INNER JOIN Job ON Equip.EquipID = Job.EquipID
GROUP BY Equip.EquipID;

Then the main query
SELECT Cust.Customer, Equip.Make, Equip.Model, Equip.Serial,
LastDateOut.LastOutDate
FROM (Cust INNER JOIN Equip ON Cust.CustID = Equip.CustID) INNER JOIN
(LastDateOut INNER JOIN Job ON (LastDateOut.EquipID = Job.EquipID) AND
(LastDateOut.LastOutDate = Job.DateOut)) ON Equip.EquipID = Job.EquipID;


Tested and working so far all is good - great many thanks for your help :)

Jason Lepack said:
Yeah, access is picky about joins with subselects apparently, as well
as brackets, here's how I got it to work.

Save the subselect as a query by itself (getLastOutDate)

Go into query designer.

Add Cust, Equip, Job, and getLastOutDate to the query.

Connect getLastOutDate.EquipID to Job.EquipID
Connect Job.DateOut to getLastOutDate.LastOutDate
Connet Equip.custID to Cust.CustID
Connect Equip.EquipId to Job.EquipID

and select the fields that you want to display (not the id fields
preferably)

Let me know how it goes.

Cheers,
Jason Lepack

That gave a syntax error but woking on it

Jason Lepack said:
Ok, the first thing I did was remove the lookup fields (I changed the
lookup back to text box). They can be more a source of confusion than
anything else. If you want that functionality then add it on a form
once you create those.
For now, what exactly do you want to Output in this query?
Start with this query:
SELECT
Cust.Customer,
Cust.Address,
Cust.Town,
Cust.Pcode,
Equip.Make,
Equip.Model,
Equip.Serial,
Job.DateIn,
Job.DateOut
FROM
Cust
INNER JOIN Equip
ON Cust.CustID = Equip.CustID
INNER JOIN Job
ON Equip.EquipID = Job.EquipID
INNER JOIN
(
SELECT
Max(DateOut) AS LastOutDate,
EquipID
FROM Job
GROUP BY EquipID
) AS B
ON (Job.DateOut = B.LastOutDate)
AND (Job.EquipID = B.EquipID);
Take a look at it in Design View and see how the joins are set up and
how the data is selected.
Any more questions just post them back.
Cheers,
Jason Lepack
ok grasped the basics there - tried to add a little more and stumbled so
mailed some data
:
Any time!! I prefer it when people want to understand what they're
doing.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
This query is actually made up of two queries.
The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID
(We'll call this qryMaxOutDate - you can save it as a query and run it
and see what it returns)
This query selects the most recent DateOut for each piece of
equipment.
So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)
Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.
The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.
I've seen some rediculous SQL and it's tough to read. Picture this:


SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3
That looks absolutley rediculous and it took me forever to copy and
paste that together.
It looks a lot better with aliases:
SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3
Any other questions about how this works, just post back and I'd be
glad to answer them.
Cheers,
Jason Lepack
That works perfectly many many thanks
If at all possible could you explain the coding very breifly - if not no
worries :)
:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries

EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) but in reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try each and see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pasted the code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I have hit a wall
I need to pick up the last time the equipment was in using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
In which of these tables is the field [StartDate]? Is [StartDate]
actaully [DateIn]?
copied the following into a query in SQL view and saved
SELECT Job.JobID, Job.EquipID
FROM Job INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = Job.StartDate) AND (Job.EquipID = B.EquipID);
I have then two tables (showing in the query) Job and B, but B is not one of
my tables in my tables window
B is an alias for this Subselect:
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID
That way that result table can be referred to.
If I then run the query I get this message
"the specified field 'Job.StartDate' could refer to more than one table in
the FROM clause of your SQL statement

Try this and post back with the result:
SELECT J.JobID, J.EquipID
FROM Job AS J INNER JOIN (
SELECT Max(StartDate) AS LastInDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastInDate = J.StartDate) AND (J.EquipID = B.EquipID);
Cheers,
Jason Lepack
PS: We'll ignore the lookup fields for now, until you post back about
problems with them later...
 
Ad

Advertisements

J

Jason Lepack

You're welcome. Post back anytime, and we'll get you straightened
around. You've got a good plan laid out so far.

Ok this worked

Getlastdateout
SELECT Max(Job.DateOut) AS LastOutDate, Equip.EquipID
FROM Equip INNER JOIN Job ON Equip.EquipID = Job.EquipID
GROUP BY Equip.EquipID;

Then the main query
SELECT Cust.Customer, Equip.Make, Equip.Model, Equip.Serial,
LastDateOut.LastOutDate
FROM (Cust INNER JOIN Equip ON Cust.CustID = Equip.CustID) INNER JOIN
(LastDateOut INNER JOIN Job ON (LastDateOut.EquipID = Job.EquipID) AND
(LastDateOut.LastOutDate = Job.DateOut)) ON Equip.EquipID = Job.EquipID;

Tested and working so far all is good - great many thanks for your help :)



Jason Lepack said:
Yeah, access is picky about joins with subselects apparently, as well
as brackets, here's how I got it to work.
Save the subselect as a query by itself (getLastOutDate)
Go into query designer.
Add Cust, Equip, Job, and getLastOutDate to the query.
Connect getLastOutDate.EquipID to Job.EquipID
Connect Job.DateOut to getLastOutDate.LastOutDate
Connet Equip.custID to Cust.CustID
Connect Equip.EquipId to Job.EquipID
and select the fields that you want to display (not the id fields
preferably)
Let me know how it goes.
Cheers,
Jason Lepack
That gave a syntax error but woking on it
:
Ok, the first thing I did was remove the lookup fields (I changed the
lookup back to text box). They can be more a source of confusion than
anything else. If you want that functionality then add it on a form
once you create those.
For now, what exactly do you want to Output in this query?
Start with this query:
SELECT
Cust.Customer,
Cust.Address,
Cust.Town,
Cust.Pcode,
Equip.Make,
Equip.Model,
Equip.Serial,
Job.DateIn,
Job.DateOut
FROM
Cust
INNER JOIN Equip
ON Cust.CustID = Equip.CustID
INNER JOIN Job
ON Equip.EquipID = Job.EquipID
INNER JOIN
(
SELECT
Max(DateOut) AS LastOutDate,
EquipID
FROM Job
GROUP BY EquipID
) AS B
ON (Job.DateOut = B.LastOutDate)
AND (Job.EquipID = B.EquipID);
Take a look at it in Design View and see how the joins are set up and
how the data is selected.
Any more questions just post them back.
Cheers,
Jason Lepack
ok grasped the basics there - tried to add a little more and stumbled so
mailed some data
:
Any time!! I prefer it when people want to understand what they're
doing.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
This query is actually made up of two queries.
The first (inner) query is this:
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID
(We'll call this qryMaxOutDate - you can save it as a query andrun it
and see what it returns)
This query selects the most recent DateOut for each piece of
equipment.
So the next step is to select only the records from the Job table that
match both the Equipment ID and DateOut that we found in the other
query.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN qryMaxOutDate AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
The Inner Join takes the two datasets (Job and qryMaxOutDate) and
returns only the records from each set that contain all of the
criteria. (Matching EquipID and DateOut)
Instead of basing the main query on a second query, I just replaced
qryMaxOutDate with the SQL for that query, giving the final result.
The AS command is used to create an "Alias" (Another name to reference
it by) that helps to make the code more readable.
I've seen some rediculous SQL and it's tough to read. Picture this:
tblSupercalafragilisticexpialidocious:
filed1
field2
field3
tblAnotherreallylongstupidname
field1
field2
field3
SELECT
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
max(tblSupercalafragilisticexpialidocious.field2) AS MaxOffield2,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3,
FROM
tblAnotherreallylongstupidname
INNER JOIN tblSupercalafragilisticexpialidocious
ON tblAnotherreallylongstupidname.field1 =
tblSupercalafragilisticexpialidocious.field1
GROUP BY
tblSupercalafragilisticexpialidocious.field1,
tblAnotherreallylongstupidname.field1,
tblAnotherreallylongstupidname.field2,
tblSupercalafragilisticexpialidocious.field3,
tblAnotherreallylongstupidname.field3
That looks absolutley rediculous and it took me forever to copyand
paste that together.
It looks a lot better with aliases:
SELECT
B.field1,
A.field1,
max(B.field2) AS MaxOffield2,
A.field2,
B.field3,
A.field3,
FROM
tblAnotherreallylongstupidname AS A
INNER JOIN tblSupercalafragilisticexpialidocious AS B
ON A.field1 = B.field1
GROUP BY
B.field1,
A.field1,
A.field2,
B.field3,
A.field3
Any other questions about how this works, just post back and I'd be
glad to answer them.
Cheers,
Jason Lepack
That works perfectly many many thanks
If at all possible could you explain the coding very breifly - if not no
worries :)
:
If this doesn't work then you need to show me some sample data and
expected output because based on what you are desribing, this should
work. You could even email me your database if you'd like me to fix
it that way.
This query will show you the equipment ID and last date that it went
out, but it won't show items that never went out.
SELECT J.EquipID, B.LastOutDate
FROM Job AS J INNER JOIN (
SELECT Max(DateOut) AS LastOutDate, EquipID
FROM Job
GROUP BY EquipID) AS B
ON (B.LastOutDate = J.DateOut) AND (J.EquipID = B.EquipID);
Ok lets start again
here are my tables
Equip = EquipID, CustID (lookup of Custdata), Make, Model, Serial
Job = JobID, EquipID (lookup of Equip data), DateIn, DateOut
Cust = CustID, Address, Town, Zip
I am trying to have a query show equipment that was checked =>6months ago -
using the [datout] field from Job table
I therefore first have to limit the query to show the last [dateout] where
there are more than a single entry for EquipID - whilst still not ignoring
single entries
Example
EquipID 1 came in and out in Jan then again in Mar
A recall would be issued in July (using the Jan date) butin reality it
should be put back until Aug
:
Either it's supposed to be DateIn or DateOut. Try eachand see which
one gives the answer you expect.
Ok [start date] is not in any of my tables - I pastedthe code from a
previous response, which had [start date] as text I assumed it to be correct.
Its the last [dateout] I am interested in.
Posted the last code as instructed
'microsoft cant represent the join expression B.LastInDate = J.StartDate
in design view'
Proceeded regardless
Ran the query and and asks for parameter 'startdate' and it failed to produce.
Can I assume I replace every startdate with dateout?
:
See comments inline.
This is a re-post of my original for which I havehit a wall
I need to pick up the last time the equipment wasin using the dateout field
from the job table - for instance if equipment came in 3 times Jan/Feb/March,
I need the query only want the March entry
my tables
Equip = EquipID, CustID (lookup of Custdata),Make, Model, Serial
Job = JobID, EquipID (lookup of- Hide quoted text -

- Show quoted text -...

read more »
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top