query

[Guest]

I have 6 colums. Event Date/RH/QV/GH/Aud/QVGH.

Each one is a room rental, with a yes/no answer. I want to know how many
times a month do each room be rented. what would my formula be for this
query?

Thank You.

 

[Duane Hookom]

You should first normalize your data with a union query:
SELECT [Event Date] As EventDate, "RH" as Room
FROM [have 6 colums]
WHERE RH = True
UNION ALL

SELECT [Event Date], "QV"
FROM [have 6 colums]
WHERE QV = True
UNION ALL
SELECT [Event Date], "GH"
FROM [have 6 colums]
WHERE GH = True
UNION ALL
SELECT [Event Date], "Aud"
FROM [have 6 colums]
WHERE Aud = True
UNION ALL
SELECT [Event Date], "QVGH"
FROM [have 6 colums]
WHERE QVGH = True;

You can then create a totals query that groups by Year(EventDate),
Month(EventDate), and Room while counting Room.

Long term, you should consider changing your table structure.

 

[Guest]

The following will sum the yes's (a yes value = -1) for all records between
user-inputted start and end dates. If you need to show multiple months at
once, then you will need to add a field to the SELECT statement
"Month(Table.Date) AS Month" and add a GROUP BY Month statement.

SELECT Sum(Abs([RH])) AS SUMRH, Sum(Abs([QV])) AS SUMQV, Sum(Abs([GH])) AS
SUMGH, Sum(Abs([AUB])) AS SUMAUD, Sum(Abs([QVGH])) AS SUMQVGH
FROM Table
WHERE (((Table.DATE) Between [ENTER START DATE] And [ENTER END DATE]));

 

[Guest]

my database looks like this
Event Date Time Slot Reception Hall Quidi Vidi Room Gibbett Hill Room
01/11/2005 3 No Yes No No No
04/11/2005 1 Yes No No No No
04/11/2005 1 No No No Yes No
17/11/2005 2 No No No Yes Yes
18/11/2005 3 No No No Yes Yes
19/11/2005 3 No No No No Yes
22/11/2005 3 No No No No Yes

I want to know how many times the Reception Hall, Quid Vidi, etc. used in
each month.
I'm unsure how to do this in the query.

Thank you for your help.

You should first normalize your data with a union query:
SELECT [Event Date] As EventDate, "RH" as Room
FROM [have 6 colums]
WHERE RH = True
UNION ALL

SELECT [Event Date], "QV"
FROM [have 6 colums]
WHERE QV = True
UNION ALL
SELECT [Event Date], "GH"
FROM [have 6 colums]
WHERE GH = True
UNION ALL
SELECT [Event Date], "Aud"
FROM [have 6 colums]
WHERE Aud = True
UNION ALL
SELECT [Event Date], "QVGH"
FROM [have 6 colums]
WHERE QVGH = True;

You can then create a totals query that groups by Year(EventDate),
Month(EventDate), and Room while counting Room.

Long term, you should consider changing your table structure.

--
Duane Hookom
MS Access MVP
--

I have 6 colums. Event Date/RH/QV/GH/Aud/QVGH.

Each one is a room rental, with a yes/no answer. I want to know how many
times a month do each room be rented. what would my formula be for this
query?

Thank You.

 

[Duane Hookom]

I understand your table. Did you understand my reply? Did you try to create
a union query that normalizes your records? If so, your result is a simple
totals query as I suggested.

--
Duane Hookom
MS Access MVP

my database looks like this
Event Date Time Slot Reception Hall Quidi Vidi Room Gibbett Hill Room
01/11/2005 3 No Yes No No No
04/11/2005 1 Yes No No No No
04/11/2005 1 No No No Yes No
17/11/2005 2 No No No Yes Yes
18/11/2005 3 No No No Yes Yes
19/11/2005 3 No No No No Yes
22/11/2005 3 No No No No Yes

I want to know how many times the Reception Hall, Quid Vidi, etc. used in
each month.
I'm unsure how to do this in the query.

Thank you for your help.

You should first normalize your data with a union query:
SELECT [Event Date] As EventDate, "RH" as Room
FROM [have 6 colums]
WHERE RH = True
UNION ALL

SELECT [Event Date], "QV"
FROM [have 6 colums]
WHERE QV = True
UNION ALL
SELECT [Event Date], "GH"
FROM [have 6 colums]
WHERE GH = True
UNION ALL
SELECT [Event Date], "Aud"
FROM [have 6 colums]
WHERE Aud = True
UNION ALL
SELECT [Event Date], "QVGH"
FROM [have 6 colums]
WHERE QVGH = True;

You can then create a totals query that groups by Year(EventDate),
Month(EventDate), and Room while counting Room.

Long term, you should consider changing your table structure.

--
Duane Hookom
MS Access MVP
--

"Melissa needing help!!" <[email protected]>
wrote in message

I have 6 colums. Event Date/RH/QV/GH/Aud/QVGH.

Each one is a room rental, with a yes/no answer. I want to know how
many
times a month do each room be rented. what would my formula be for
this
query?

Thank You.

 

[John Spencer]

Stealing JJewell's code and modifying it to take care of the month
requirement.

SELECT Year([Event Date]) as YearNum, Month([Event Date]) as MonthNum
, Sum(Abs([RH])) AS SUMRH
, Sum(Abs([QV])) AS SUMQV
, Sum(Abs([GH])) AS SUMGH
, Sum(Abs([AUB])) AS SUMAUD
, Sum(Abs([QVGH])) AS SUMQVGH
FROM [YourTableName]
WHERE [Event Date] Between [ENTER START DATE] And [ENTER END DATE]
GROUP BY Year([Event Date]), Month([Event Date])

IF you are doing this in the query grid and can't translate the above, then
post back for detailed, tedious instructions. If you don't want to set a
date range, then remove the entire WHERE clause.

The following will sum the yes's (a yes value = -1) for all records
between
user-inputted start and end dates. If you need to show multiple months at
once, then you will need to add a field to the SELECT statement
"Month(Table.Date) AS Month" and add a GROUP BY Month statement.

SELECT Sum(Abs([RH])) AS SUMRH, Sum(Abs([QV])) AS SUMQV, Sum(Abs([GH])) AS
SUMGH, Sum(Abs([AUB])) AS SUMAUD, Sum(Abs([QVGH])) AS SUMQVGH
FROM Table
WHERE (((Table.DATE) Between [ENTER START DATE] And [ENTER END DATE]));

I have 6 colums. Event Date/RH/QV/GH/Aud/QVGH.

Each one is a room rental, with a yes/no answer. I want to know how many
times a month do each room be rented. what would my formula be for this
query?

Thank You.

 

[Guest]

hi Duane,

I'm sorry i don't understand what a union query is. If you could help me on
that-that would be great.

Melissa

I understand your table. Did you understand my reply? Did you try to create
a union query that normalizes your records? If so, your result is a simple
totals query as I suggested.

--
Duane Hookom
MS Access MVP

my database looks like this
Event Date Time Slot Reception Hall Quidi Vidi Room Gibbett Hill Room
01/11/2005 3 No Yes No No No
04/11/2005 1 Yes No No No No
04/11/2005 1 No No No Yes No
17/11/2005 2 No No No Yes Yes
18/11/2005 3 No No No Yes Yes
19/11/2005 3 No No No No Yes
22/11/2005 3 No No No No Yes

I want to know how many times the Reception Hall, Quid Vidi, etc. used in
each month.
I'm unsure how to do this in the query.

Thank you for your help.

You should first normalize your data with a union query:
SELECT [Event Date] As EventDate, "RH" as Room
FROM [have 6 colums]
WHERE RH = True
UNION ALL

SELECT [Event Date], "QV"
FROM [have 6 colums]
WHERE QV = True
UNION ALL
SELECT [Event Date], "GH"
FROM [have 6 colums]
WHERE GH = True
UNION ALL
SELECT [Event Date], "Aud"
FROM [have 6 colums]
WHERE Aud = True
UNION ALL
SELECT [Event Date], "QVGH"
FROM [have 6 colums]
WHERE QVGH = True;

You can then create a totals query that groups by Year(EventDate),
Month(EventDate), and Room while counting Room.

Long term, you should consider changing your table structure.

--
Duane Hookom
MS Access MVP
--

"Melissa needing help!!" <[email protected]>
wrote in message
I have 6 colums. Event Date/RH/QV/GH/Aud/QVGH.

Each one is a room rental, with a yes/no answer. I want to know how
many
times a month do each room be rented. what would my formula be for
this
query?

Thank You.

 

[Duane Hookom]

You take the sql statement that I provided in my first post and enter it
into the SQL view of a query. You would need to replace my table and field
names with the correct table and field names.

--
Duane Hookom
MS Access MVP
--

hi Duane,

I'm sorry i don't understand what a union query is. If you could help me
on
that-that would be great.

Melissa

I understand your table. Did you understand my reply? Did you try to
create
a union query that normalizes your records? If so, your result is a
simple
totals query as I suggested.

--
Duane Hookom
MS Access MVP


"Melissa needing help!!" <[email protected]>
wrote in message

my database looks like this
Event Date Time Slot Reception Hall Quidi Vidi Room Gibbett Hill Room
01/11/2005 3 No Yes No No No
04/11/2005 1 Yes No No No No
04/11/2005 1 No No No Yes No
17/11/2005 2 No No No Yes Yes
18/11/2005 3 No No No Yes Yes
19/11/2005 3 No No No No Yes
22/11/2005 3 No No No No Yes

I want to know how many times the Reception Hall, Quid Vidi, etc. used
in
each month.
I'm unsure how to do this in the query.

Thank you for your help.

:

You should first normalize your data with a union query:
SELECT [Event Date] As EventDate, "RH" as Room
FROM [have 6 colums]
WHERE RH = True
UNION ALL

SELECT [Event Date], "QV"
FROM [have 6 colums]
WHERE QV = True
UNION ALL
SELECT [Event Date], "GH"
FROM [have 6 colums]
WHERE GH = True
UNION ALL
SELECT [Event Date], "Aud"
FROM [have 6 colums]
WHERE Aud = True
UNION ALL
SELECT [Event Date], "QVGH"
FROM [have 6 colums]
WHERE QVGH = True;

You can then create a totals query that groups by Year(EventDate),
Month(EventDate), and Room while counting Room.

Long term, you should consider changing your table structure.

--
Duane Hookom
MS Access MVP
--

"Melissa needing help!!"
<[email protected]>
wrote in message
I have 6 colums. Event Date/RH/QV/GH/Aud/QVGH.

Each one is a room rental, with a yes/no answer. I want to know how
many
times a month do each room be rented. what would my formula be for
this
query?

Thank You.