Need help on query

[dinadvani via AccessMonster.com]

Hi,

I have just started some work on access and currently i am stuck on 1 query.
I have a list of accounts alongwith project start date in below format:

Sr. No. Account Project Start Date
1 ABC 1-Apr-05
2 ABC 1-Apr-06
3 ABC 1-Apr-04
4 Xyz 5-May-04
5 Xyz 16-Jun-06


Now I want a report that should produce the results as below

Sr. No. Account Date when First project started
1 ABC 1-Apr-04
2 Xyz 5-May-04

Please let me know if this is possible.

Thanks for your help

D

 

[dinadvani via AccessMonster.com]

Hi,

I have just started some work on access and currently i am stuck on 1 query.
I have a list of accounts alongwith project start date in below format:

Sr. No. Account Project Start Date
1 ABC 1-Apr-05
2 ABC 1-Apr-06
3 ABC 1-Apr-04
4 Xyz 5-May-04
5 Xyz 16-Jun-06

Now I want a report that should produce the results as below

Sr. No. Account Date when First project started
1 ABC 1-Apr-04
2 Xyz 5-May-04

Please let me know if this is possible.

Thanks for your help

D

Please help!!!! ASAP

 

[dneagle via AccessMonster.com]

Hi,

I have just started some work on access and currently i am stuck on 1 query.
I have a list of accounts alongwith project start date in below format:

Sr. No. Account Project Start Date
1 ABC 1-Apr-05
2 ABC 1-Apr-06
3 ABC 1-Apr-04
4 Xyz 5-May-04
5 Xyz 16-Jun-06

Now I want a report that should produce the results as below

Sr. No. Account Date when First project started
1 ABC 1-Apr-04
2 Xyz 5-May-04

Please let me know if this is possible.

Thanks for your help

D

Try this SQL. Change the table and field names to suit.

SELECT Min(tblProjects.ProjectID) AS MinOfProjectID, tblProjects.Account, Min
(tblProjects.StartDate) AS MinOfStartDate
FROM tblProjects
GROUP BY tblProjects.Account;

 

[dinadvani via AccessMonster.com]

Hi,

Thanks for your help.

But i am getting an error message"Syntax of sub query in this expression is
incorrect"

SELECT Min(tblHP.Sr Number) AS MinOfSr Number, tblHP.Client Name, Min
(tblHP.StartDate) AS MinOfStartDate

Table name is HP

Your immediate assistance will be appreciated.

Thanks
DA

Hi,

[quoted text clipped - 19 lines]

D

Try this SQL. Change the table and field names to suit.

SELECT Min(tblProjects.ProjectID) AS MinOfProjectID, tblProjects.Account, Min
(tblProjects.StartDate) AS MinOfStartDate
FROM tblProjects
GROUP BY tblProjects.Account;

 

[John Spencer]

You must surround field and table names that contain spaces with square
braces [ ]. This rule also applies to field names that contain other
non-alphanumeric characters (such as - / ; + () *).

Try the following query

SELECT Min([Sr Number]) AS MinOfSr Number
, [Client Name]
, Min (StartDate) AS MinOfStartDate
FROM HP
GROUP BY [Client Name]

Table name is HP

Hi,

Thanks for your help.

But i am getting an error message"Syntax of sub query in this expression
is
incorrect"

SELECT Min(tblHP.Sr Number) AS MinOfSr Number, tblHP.Client Name, Min
(tblHP.StartDate) AS MinOfStartDate

Table name is HP

Your immediate assistance will be appreciated.

Thanks
DA

Hi,

[quoted text clipped - 19 lines]

D

Try this SQL. Change the table and field names to suit.

SELECT Min(tblProjects.ProjectID) AS MinOfProjectID, tblProjects.Account,
Min
(tblProjects.StartDate) AS MinOfStartDate
FROM tblProjects
GROUP BY tblProjects.Account;

 

[dinadvani via AccessMonster.com]

Hi John,

Thanks for your reply.

But still the same error message"Check the subquery syntax and enclose
subquery in parenthisis"

I am trying this querry
SELECT Min([Sr Number]) AS MinOfSr Number,[Client Name], Min (Project Start
Date) AS MinOfProject Start Date FROM HP Tracker GROUP BY [Client Name]

Please Help...

Thanks,
DA

You must surround field and table names that contain spaces with square
braces [ ]. This rule also applies to field names that contain other
non-alphanumeric characters (such as - / ; + () *).

Try the following query

SELECT Min([Sr Number]) AS MinOfSr Number
, [Client Name]
, Min (StartDate) AS MinOfStartDate
FROM HP
GROUP BY [Client Name]

Table name is HP

Hi,

[quoted text clipped - 27 lines]

 

[John Spencer]

As I noted earlier you need to use [] around all field and table names that
contain spaces

SELECT Min([Sr Number]) AS [MinOfSr Number]
, [Client Name]
, Min ([Project StartDate]) AS [MinOfProject Start Date]
FROM [HP Tracker]
GROUP BY [Client Name]

The above could be in error on the FROM line if your table is named HP and
not HP Tracker.

"FROM HP Tracker " would be interpreted as "FROM HP as Tracker". That
assigns an alias of Tracker to an instance of the HP table.

Is this the entire query? You say the error message is check subquery
syntax. If this is a subquery, there are other considerations that could be
making this fail. Such as a subquery in a select clause can only return one
column. A subquery in the FROM clause cannot contain square brackets (at
least if you are using the native-to-Access JET engine).

Hi John,

Thanks for your reply.

But still the same error message"Check the subquery syntax and enclose
subquery in parenthisis"

I am trying this querry
SELECT Min([Sr Number]) AS MinOfSr Number,[Client Name], Min (Project
Start
Date) AS MinOfProject Start Date FROM HP Tracker GROUP BY [Client Name]

Please Help...

Thanks,
DA

You must surround field and table names that contain spaces with square
braces [ ]. This rule also applies to field names that contain other
non-alphanumeric characters (such as - / ; + () *).

Try the following query

SELECT Min([Sr Number]) AS MinOfSr Number
, [Client Name]
, Min (StartDate) AS MinOfStartDate
FROM HP
GROUP BY [Client Name]

Table name is HP

Hi,

[quoted text clipped - 27 lines]

FROM tblProjects
GROUP BY tblProjects.Account;