How do I use query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
 
Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)
 
Melissa said:
How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.
Click to expand...


SELECT ColumnA, Count(*) As CountA
FROM thetable
GROUP BY ColumnA
 
Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa

Ofer said:
Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)

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



Melissa needing help!! said:
How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
Click to expand...
Click to expand...
 
It is possible, but it will display the count for every record that has YES
in the [Reception Hall] field.
You'll need to create two queries and a third one that join them both
together or by using one query with Dcount which it's not recomnded, it will
take the query a long time to come up, depending on the amount of records in
the table.

If you need help, please post the SQL you already have that count the number
of records


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



Melissa needing help!! said:
Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date [Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa

Ofer said:
Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)

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



Melissa needing help!! said:
How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa
 
Here is what my table looks like:

Event Date Reception Hall Quidi Vidi Room Gibbett Hill
Room Auditorium
01/10/2005 No No No Yes
03/10/2005 No Yes No No
31/10/2005 Yes No No No

Here is what my SQL looks like. this is working BUT it is counting all the
rooms as one, instead of telling me that the Quidi Vidi was Rented only once,
it's telling me the quidi Vidi room was booked 3 times. What is the next
step I need to do so it will only count that one room. I hope you understand
my question.
SELECT Count([Facility Table].[Quidi Vidi Room]) AS [CountOfQuidi Vidi Room]
FROM [Facility Table]
WHERE ((([Facility Table].[Event Date]) Between #10/1/2005# And #10/31/2005#))
HAVING (((Count([Facility Table].[Quidi Vidi Room]))=True)) OR
(((Count([Facility Table].[Quidi Vidi Room]))=Yes));

Thank You again for your help.
Melissa

Ofer said:
It is possible, but it will display the count for every record that has YES
in the [Reception Hall] field.
You'll need to create two queries and a third one that join them both
together or by using one query with Dcount which it's not recomnded, it will
take the query a long time to come up, depending on the amount of records in
the table.

If you need help, please post the SQL you already have that count the number
of records


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



Melissa needing help!! said:
Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date [Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa

Ofer said:
Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)

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



:

How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
Click to expand...
Click to expand...
Click to expand...
 
Here is what my table looks like:

Event Date Reception Hall Quidi Vidi Room Gibbett Hill
Room Auditorium
01/10/2005 No No No Yes
03/10/2005 No Yes No No
31/10/2005 Yes No No No
Click to expand...

Your table structure is incorrect. Storing data - a room name - in a
fieldname is simply BAD DESIGN and will be the source of no end of
problems.

A much better approach would be to have a table with fields EventDate
and RoomID, linked to a table of Rooms (one row per room). You can
then use a very simple totals query, grouping by roomID, to list the
number of times that room has been used.

John W. Vinson[MVP]
 
Well, yes, dor this one case it is possible:

SELECT ColumnA, Month([Date]), Count(*) As CountA
FROM thetable
GROUP BY ColumnA, Month([Date])

But to do this for other columns too, you had best listen to
John's words of wisdom.
 
Try this

SELECT Format([Event Date],"mmmm") AS MuMonth, Sum([Reception Hall]*-1) AS
CountOfReceptionHall, Sum([Quidi Vidi Room]*-1) AS CountOfQuidiVidiRoom,
Sum([Gibbett Hill Room]*-1) AS CountOfGibbettHillRoom, Sum([Auditorium]*-1)
AS CountOfAuditorium
FROM [Facility Table]
GROUP BY Format([Event Date],"mmmm")

And I have to agree with John, you need to change the structure of the table


Melissa needing help!! said:
Here is what my table looks like:

Event Date Reception Hall Quidi Vidi Room Gibbett Hill
Room Auditorium
01/10/2005 No No No Yes
03/10/2005 No Yes No No
31/10/2005 Yes No No No

Here is what my SQL looks like. this is working BUT it is counting all the
rooms as one, instead of telling me that the Quidi Vidi was Rented only once,
it's telling me the quidi Vidi room was booked 3 times. What is the next
step I need to do so it will only count that one room. I hope you understand
my question.
SELECT Count([Facility Table].[Quidi Vidi Room]) AS [CountOfQuidi Vidi Room]
FROM [Facility Table]
WHERE ((([Facility Table].[Event Date]) Between #10/1/2005# And #10/31/2005#))
HAVING (((Count([Facility Table].[Quidi Vidi Room]))=True)) OR
(((Count([Facility Table].[Quidi Vidi Room]))=Yes));

Thank You again for your help.
Melissa

Ofer said:
It is possible, but it will display the count for every record that has YES
in the [Reception Hall] field.
You'll need to create two queries and a third one that join them both
together or by using one query with Dcount which it's not recomnded, it will
take the query a long time to come up, depending on the amount of records in
the table.

If you need help, please post the SQL you already have that count the number
of records


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



Melissa needing help!! said:
Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date [Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa

:

Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)

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



:

How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
Click to expand...
Click to expand...
Click to expand...
 
,Hi John,

I understand about the structure of the table, but what would I do if more
than one room is rented at one time. So what your saying my table should
look like:

Date Room ID
10/1/2005 RH
10/10/2005 QV On this date the AUD was also rented.?

Thanks for Your help.
 
Hi Ofer,

Thank you very much I have gotten what I am looking for. The only other
question I do have is. I have the month now I want to do the month and Year.
Such as, October 2005. Again Thank you so much.

Melissa

Ofer said:
Try this

SELECT Format([Event Date],"mmmm") AS MuMonth, Sum([Reception Hall]*-1) AS
CountOfReceptionHall, Sum([Quidi Vidi Room]*-1) AS CountOfQuidiVidiRoom,
Sum([Gibbett Hill Room]*-1) AS CountOfGibbettHillRoom, Sum([Auditorium]*-1)
AS CountOfAuditorium
FROM [Facility Table]
GROUP BY Format([Event Date],"mmmm")

And I have to agree with John, you need to change the structure of the table


Melissa needing help!! said:
Here is what my table looks like:

Event Date Reception Hall Quidi Vidi Room Gibbett Hill
Room Auditorium
01/10/2005 No No No Yes
03/10/2005 No Yes No No
31/10/2005 Yes No No No

Here is what my SQL looks like. this is working BUT it is counting all the
rooms as one, instead of telling me that the Quidi Vidi was Rented only once,
it's telling me the quidi Vidi room was booked 3 times. What is the next
step I need to do so it will only count that one room. I hope you understand
my question.
SELECT Count([Facility Table].[Quidi Vidi Room]) AS [CountOfQuidi Vidi Room]
FROM [Facility Table]
WHERE ((([Facility Table].[Event Date]) Between #10/1/2005# And #10/31/2005#))
HAVING (((Count([Facility Table].[Quidi Vidi Room]))=True)) OR
(((Count([Facility Table].[Quidi Vidi Room]))=Yes));

Thank You again for your help.
Melissa

Ofer said:
It is possible, but it will display the count for every record that has YES
in the [Reception Hall] field.
You'll need to create two queries and a third one that join them both
together or by using one query with Dcount which it's not recomnded, it will
take the query a long time to come up, depending on the amount of records in
the table.

If you need help, please post the SQL you already have that count the number
of records


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



:

Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date [Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa

:

Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)

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



:

How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
Click to expand...
Click to expand...
Click to expand...
 
You need to change
Format([Event Date],"mmmm")

To
Format([Event Date],"mmmm yyyy")

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



Melissa needing help!! said:
Hi Ofer,

Thank you very much I have gotten what I am looking for. The only other
question I do have is. I have the month now I want to do the month and Year.
Such as, October 2005. Again Thank you so much.

Melissa

Ofer said:
Try this

SELECT Format([Event Date],"mmmm") AS MuMonth, Sum([Reception Hall]*-1) AS
CountOfReceptionHall, Sum([Quidi Vidi Room]*-1) AS CountOfQuidiVidiRoom,
Sum([Gibbett Hill Room]*-1) AS CountOfGibbettHillRoom, Sum([Auditorium]*-1)
AS CountOfAuditorium
FROM [Facility Table]
GROUP BY Format([Event Date],"mmmm")

And I have to agree with John, you need to change the structure of the table


Melissa needing help!! said:
Here is what my table looks like:

Event Date Reception Hall Quidi Vidi Room Gibbett Hill
Room Auditorium
01/10/2005 No No No Yes
03/10/2005 No Yes No No
31/10/2005 Yes No No No

Here is what my SQL looks like. this is working BUT it is counting all the
rooms as one, instead of telling me that the Quidi Vidi was Rented only once,
it's telling me the quidi Vidi room was booked 3 times. What is the next
step I need to do so it will only count that one room. I hope you understand
my question.
SELECT Count([Facility Table].[Quidi Vidi Room]) AS [CountOfQuidi Vidi Room]
FROM [Facility Table]
WHERE ((([Facility Table].[Event Date]) Between #10/1/2005# And #10/31/2005#))
HAVING (((Count([Facility Table].[Quidi Vidi Room]))=True)) OR
(((Count([Facility Table].[Quidi Vidi Room]))=Yes));

Thank You again for your help.
Melissa

:

It is possible, but it will display the count for every record that has YES
in the [Reception Hall] field.
You'll need to create two queries and a third one that join them both
together or by using one query with Dcount which it's not recomnded, it will
take the query a long time to come up, depending on the amount of records in
the table.

If you need help, please post the SQL you already have that count the number
of records


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



:

Thank you for your response. It's work but, I want a column that tells me
the total times the Reception Hall was used in the month of Oct. It would
like to see something like this:

Date [Reception Hall Total # of time per month
01/10/2005 YES 2
11/10/2005 YES
13/10/2005 NO

Is this possible?

melissa

:

Try this

Select Format(DateField,"mmm") As MonthName, Count([Reception Hall]) As
CountOfYes
From TableName
Where [Reception Hall] = True
Group By Format(DateField,"mmm")
Order By Month(DateField)

========================================
If you want per Year

Select Year(DateField) As MyYear, Format(DateField,"mmm") As MonthName,
Count([Reception Hall]) As CountOfYes
From TableName
Where [Reception Hall] = True
Group By Year(DateField) ,Format(DateField,"mmm")
Order By Year(DateField) ,Month(DateField)

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



:

How do I do a query to find out, How many times colum A (Reception Hall) has
been used in one month. colum A (Reception hall ) is a YES/NO field. Date
field is called (Event Date).

Example:
Date Reception Hall
01/10/2005 YES
11/10/2005 YES
13/10/2005 NO

So my answer would be: Reception Hall = 2 becuase twice in the month of
October it was rented. How do I do this in a query so it will give me the
answer I am looking for.

Thank You for your help.
Click to expand...
Click to expand...
Click to expand...
 
,Hi John,

I understand about the structure of the table, but what would I do if more
than one room is rented at one time. So what your saying my table should
look like:

Date Room ID
10/1/2005 RH
10/10/2005 QV On this date the AUD was also rented.?
Click to expand...

Simply add another record:

10/10/2005 QV
10/10/2005 AUD

What's being stored in this table is the information "This room was
rented on this date". If you can rent six rooms on the same date, fine
- add six records, all with the same date, one for each room!

If you want to *see* a report laid out like your current table, you
can create a Crosstab Query which will do so. That's a data display
issue, which should NOT be confused with a data storage design!

John W. Vinson[MVP]
 

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

query 4
How can I make a query use a combobox? 2
Query to search by month .. how do I format??? 4
query 7
Dsum grouped by 9
Excel date intervals look up 1
Split data in field on character return 0
last date in query 2

Back
Top