query problem

  • Thread starter Thread starter Ron DeGregorio
  • Start date Start date
R

Ron DeGregorio

I have a table with the following fields: [JobNumber], [Task], [StartDate].
There are about 10 JobNumbers with about several hundred Tasks each.
I would like to write a query that returns only the first 50 of the Tasks
ordered by ascending StartDates for each of the 10 JobNumbers.
This would be used to write a report that would be only 1 page in length for
each of the 10 Job Numbers (10 pages total).
Anyone have any ideas?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT JobNumber, Task, StartDate
FROM table1 As T
WHERE Task In (SELECT TOP 50 Task FROM table1
WHERE JobNumber = T.JobNumber
ORDER BY StartDate DESC)
ORDER BY JobNumber, StartDate

Change table1 to your table's name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk23AIechKqOuFEgEQLmiwCgsa88q/NnsVpjkKHKzVTqeZomIg0An3ye
KmNDkFOfogRauWNde7iD7XDV
=pYxX
-----END PGP SIGNATURE-----
 
Actual fields are: JobNumber = Job_Code, Task = Task_Name, StartDate =
Start_Date
Actual table is: ExportTrackingTable

When I entered the SQL stmt that you suggested making the required field
name changes Access changed it to the following:

SELECT ExportTrackingTable.Job_Code AS Expr1, ExportTrackingTable.Task_Name
AS Expr2, ExportTrackingTable.Start_Date AS Expr3
FROM ExportTrackingTable AS T
WHERE (((T.Task_Name) In (SELECT TOP 50 Task_Name FROM ExportTrackingTable
WHERE Job_Code = T.Job_Code
ORDER BY Start_Date DESC)))
ORDER BY T.Job_Code, T.Start_Date;
When I run the query, Access asks for all three fields as parameters (which
I left blank) and returns several thousand blank rows. Any suggestions?

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Something like this:

SELECT JobNumber, Task, StartDate
FROM table1 As T
WHERE Task In (SELECT TOP 50 Task FROM table1
WHERE JobNumber = T.JobNumber
ORDER BY StartDate DESC)
ORDER BY JobNumber, StartDate

Change table1 to your table's name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk23AIechKqOuFEgEQLmiwCgsa88q/NnsVpjkKHKzVTqeZomIg0An3ye
KmNDkFOfogRauWNde7iD7XDV
=pYxX
-----END PGP SIGNATURE-----


Ron said:
I have a table with the following fields: [JobNumber], [Task], [StartDate].
There are about 10 JobNumbers with about several hundred Tasks each.
I would like to write a query that returns only the first 50 of the Tasks
ordered by ascending StartDates for each of the 10 JobNumbers.
This would be used to write a report that would be only 1 page in length for
each of the 10 Job Numbers (10 pages total).
Anyone have any ideas?
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should have changed the names in the SQL view instead of the QBE
view. Access set it up the way it thought it should be, which was
wrong. You just have to use the table alias instead of the full table
name.

Try this:

SELECT T.Job_Code, T.Task_Name, T.Start_Date
FROM ExportTrackingTable AS T
WHERE T.Task_Name In (SELECT TOP 50 Task_Name
FROM ExportTrackingTable
WHERE Job_Code = T.Job_Code
ORDER BY Start_Date DESC)
ORDER BY T.Job_Code, T.Start_Date

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk9s3YechKqOuFEgEQIJBgCcDIGrIDIyB3S/Yza0K3m+7xc9mlYAoJbT
zhMu+Q2WBqcymfVh/yZBwNbl
=a/fq
-----END PGP SIGNATURE-----

Ron said:
Actual fields are: JobNumber = Job_Code, Task = Task_Name, StartDate =
Start_Date
Actual table is: ExportTrackingTable

When I entered the SQL stmt that you suggested making the required field
name changes Access changed it to the following:

SELECT ExportTrackingTable.Job_Code AS Expr1, ExportTrackingTable.Task_Name
AS Expr2, ExportTrackingTable.Start_Date AS Expr3
FROM ExportTrackingTable AS T
WHERE (((T.Task_Name) In (SELECT TOP 50 Task_Name FROM ExportTrackingTable
WHERE Job_Code = T.Job_Code
ORDER BY Start_Date DESC)))
ORDER BY T.Job_Code, T.Start_Date;
When I run the query, Access asks for all three fields as parameters (which
I left blank) and returns several thousand blank rows. Any suggestions?
Click to expand...

< SNIP >
 
I am having a great deal of trouble getting your SQL statement to return
what I want. Maybe my explanation of what I wanted was not clear because I
tried to simplify it. The following SQL statement returns the information
that I need.

SELECT T.[Hull#], T.Task_Name, T.Job_Code, T.Dept,
tblDepartments.DeptDescription, T.Start_Date, T.ID, T.Percent_Comp,
T.Finish_Date, T.Total_Slack, T.Critical
FROM tblDepartments INNER JOIN ExportTrackingTable AS T ON
tblDepartments.DeptNumber = T.Dept
WHERE (((T.Job_Code)<>0) AND ((T.Dept)<>0 And (T.Dept)<>15) AND
((T.Percent_Comp)<1))
ORDER BY T.[Hull#], T.Start_Date, T.ID;

How could I alter it to return only the first N number of records for each
[Hull#] in the order of ascending Start_Dates? There are a total of 10
different Hull#s with as few as 13 and as many as 196 records each. Any
help would be much appreciated.
Ron
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Its usually a good idea to give the actual query at the beginning of
your request. It saves a lot of back and forth.

Here's my final try:

SELECT T.[Hull#], T.Task_Name, T.Job_Code, T.Dept,
D.DeptDescription, T.Start_Date, T.ID, T.Percent_Comp,
T.Finish_Date, T.Total_Slack, T.Critical

FROM tblDepartments As D INNER JOIN ExportTrackingTable AS T
ON D.DeptNumber = T.Dept

WHERE T.[Hull#] In (SELECT TOP 50 [Hull#]
FROM ExportTrackingTable
WHERE Dept = D.DeptNumber
AND Job_Code <> 0
AND Dept NOT IN (0, 15)
AND Percent_Comp < 1
ORDER BY Start_Date)

ORDER BY T.[Hull#], T.Start_Date, T.ID;

I needed a linking column between the main query and the correlated
subquery. I used the Dept 'cuz you used it in the INNER JOIN link.
This may be wrong. I'd need to see some sample data (real stuff) from
ExportTrackingTable to determine which column(s) to correctly use.

In the subquery, if you sort the Start_Date Ascending it will show the
earliest dated records, not the most recent records. If you want to
show the most recent records use "ORDER BY Start_Date DESC" in the
subquery.

NOTE: You can't easily edit this query in the QBE view. Always use the
SQL view when changing it.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlCItIechKqOuFEgEQKuGQCfa4l8MiZfMFBIOj8JUFxpBO5tdCgAni3f
83W1RGb3aihsrE7U1sGmfQNL
=Xmqf
-----END PGP SIGNATURE-----

Ron said:
I am having a great deal of trouble getting your SQL statement to return
what I want. Maybe my explanation of what I wanted was not clear because I
tried to simplify it. The following SQL statement returns the information
that I need.


How could I alter it to return only the first N number of records for each
[Hull#] in the order of ascending Start_Dates? There are a total of 10
different Hull#s with as few as 13 and as many as 196 records each. Any
help would be much appreciated.
Ron

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should have changed the names in the SQL view instead of the QBE
view. Access set it up the way it thought it should be, which was
wrong. You just have to use the table alias instead of the full table
name.

Try this:

SELECT T.Job_Code, T.Task_Name, T.Start_Date
FROM ExportTrackingTable AS T
WHERE T.Task_Name In (SELECT TOP 50 Task_Name
FROM ExportTrackingTable
WHERE Job_Code = T.Job_Code
ORDER BY Start_Date DESC)
ORDER BY T.Job_Code, T.Start_Date
Click to expand...
Click to expand...
 
Back
Top