Query Question

[Guest]

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

 

[Guest]

I am not running a project, i am running a MDB

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am not running a project, i am running a MDB

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

Look at this link for sample queries

http://office.microsoft.com/en-ca/assistance/HA011201381033.aspx

I am not running a project, i am running a MDB

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

That works a treat, however I do have another problem, If i wanted to only
include records between specific dates, the Between function doesn't appear
to work with an error message of "you tried to execute a query that does not
include the specified expression"Date" as part of an aggregate function"

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am not running a project, i am running a MDB

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am not running a project, i am running a MDB

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

Try this

SELECT Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
Where Appointments.Date Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]
GROUP BY Appointments.FirstName, Appointments.SurName

Use the where so you wont include the date in the resault, other wise you
will get a line for each date

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I am not running a project, i am running a MDB

:

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

Excellent, Worked perfectly, Thanks for your help, very much appreciated
A************************

Try this

SELECT Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
Where Appointments.Date Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]
GROUP BY Appointments.FirstName, Appointments.SurName

Use the where so you wont include the date in the resault, other wise you
will get a line for each date

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I am not running a project, i am running a MDB

:

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

Glad I could help, Good luck with your project

Excellent, Worked perfectly, Thanks for your help, very much appreciated
A************************

Try this

SELECT Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
Where Appointments.Date Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]
GROUP BY Appointments.FirstName, Appointments.SurName

Use the where so you wont include the date in the resault, other wise you
will get a line for each date

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));


:

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I am not running a project, i am running a MDB

:

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

I wouldn't mind having the two dates that I enter showing up at the top of
the report for the query though....lol

Glad I could help, Good luck with your project

Excellent, Worked perfectly, Thanks for your help, very much appreciated
A************************

Try this

SELECT Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
Where Appointments.Date Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]
GROUP BY Appointments.FirstName, Appointments.SurName

Use the where so you wont include the date in the resault, other wise you
will get a line for each date

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));


:

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I am not running a project, i am running a MDB

:

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

In your report header, insert a textbox and set its ControlSource
to:
="Between " & [Enter a from Date in dd/mm/yyyy] & " and " & [Enter a To Date
in dd/mm/yyyy]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I wouldn't mind having the two dates that I enter showing up at the top of
the report for the query though....lol

Glad I could help, Good luck with your project

Excellent, Worked perfectly, Thanks for your help, very much appreciated
A************************

:

Try this

SELECT Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
Where Appointments.Date Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]
GROUP BY Appointments.FirstName, Appointments.SurName

Use the where so you wont include the date in the resault, other wise you
will get a line for each date

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));


:

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I am not running a project, i am running a MDB

:

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy

 

[Guest]

Again, very much appreciated

In your report header, insert a textbox and set its ControlSource
to:
="Between " & [Enter a from Date in dd/mm/yyyy] & " and " & [Enter a To Date
in dd/mm/yyyy]

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck

I wouldn't mind having the two dates that I enter showing up at the top of
the report for the query though....lol

Glad I could help, Good luck with your project

:

Excellent, Worked perfectly, Thanks for your help, very much appreciated
A************************

:

Try this

SELECT Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
Where Appointments.Date Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]
GROUP BY Appointments.FirstName, Appointments.SurName

Use the where so you wont include the date in the resault, other wise you
will get a line for each date

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

My SQL says

SELECT Appointments.Date, Appointments.FirstName, Appointments.SurName,
Sum([Appointments.Time]) AS SumOfTime
FROM Appointments
GROUP BY Appointments.FirstName, Appointments.SurName
HAVING (((Appointments.Date) Between [Enter a from Date in dd/mm/yyyy] And
[Enter a To Date in dd/mm/yyyy]));


:

In your MDB select the query TAB > select create New
In the query design view select in the menu bar > View > SQL view
Copy the SQL I gave you and paste in the query SQL
Change the TableName to your table name
Assuming that the fields name you gave us has the right names, if not change
them
Run the query

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I am not running a project, i am running a MDB

:

Try this, ceating a group by query with sum on the minutes

SELECT Surname, FirstName, Sum([Minutes]) AS SumOfMinutes
FROM TableName
GROUP BY Surname, FirstName

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have a database that records Date, Surname, FirstName, Number of Minutes

If a person is entered twice for example:
Date Surname FirstName Minutes
19/12/2005 Other Andrew 150
21/12/2005 Other Andrew 200
22/12/2005 Other Andrew 120

What i am trying to achieve is a report that would display the name once
(and display other names only once) but give a total of the minutes for each
name, is there any way of doing this?

Help would be appreciated, been driving me crazy