Need help on query

  • Thread starter dinadvani via AccessMonster.com
  • Start date
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
 
D

dinadvani via AccessMonster.com

dinadvani said:
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
 
D

dneagle via AccessMonster.com

dinadvani said:
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;
 
D

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
[quoted text clipped - 19 lines]

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;
 
J

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
dinadvani via AccessMonster.com said:
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
[quoted text clipped - 19 lines]

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;
 
D

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

John said:
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
[quoted text clipped - 27 lines]
 
J

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).


dinadvani via AccessMonster.com said:
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

John said:
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
[quoted text clipped - 27 lines]
FROM tblProjects
GROUP BY tblProjects.Account;
 

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

Similar Threads


Top