Query Criteria

[Guest]

How can I setup a query to pull year to date budget info? I have a table (tbl
budget) that contains budget information by project. I have "month" as a
field name so each project is enter 12 times in the table (1 for each month).
I will need to obtain year to date budget info throughout the year next year.
I am not sure how to specify the query to pull a certain time line as the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month = [tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name];

 

[Guest]

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and time.

 

[Guest]

How would this work if my fiscal year is October through September?

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and time.
--
Cheers,
Shane Devenshire

How can I setup a query to pull year to date budget info? I have a table (tbl
budget) that contains budget information by project. I have "month" as a
field name so each project is enter 12 times in the table (1 for each month).
I will need to obtain year to date budget info throughout the year next year.
I am not sure how to specify the query to pull a certain time line as the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month = [tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name];

 

[Guest]

Hi,

Here's one example
WHERE (((Table1.Date)>=#9/1/2007# And (Table1.Date)<=#11/30/2007#))
--
Thanks,
Shane Devenshire

How would this work if my fiscal year is October through September?

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and time.
--
Cheers,
Shane Devenshire

How can I setup a query to pull year to date budget info? I have a table (tbl
budget) that contains budget information by project. I have "month" as a
field name so each project is enter 12 times in the table (1 for each month).
I will need to obtain year to date budget info throughout the year next year.
I am not sure how to specify the query to pull a certain time line as the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month = [tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID Pook],
[tbl budget].[Project Name];

 

[Pieter Wijnen]

that's a short year <g>

Pieter

Hi,

Here's one example
WHERE (((Table1.Date)>=#9/1/2007# And (Table1.Date)<=#11/30/2007#))
--
Thanks,
Shane Devenshire

How would this work if my fiscal year is October through September?

Hi,

on the criteria row in a query you would type <=Now()

or the SQL equivalent:

WHERE (((Table1.Date)<=Now()))

NOW is the built in function that picks up the computer's data and
time.
--
Cheers,
Shane Devenshire


:

How can I setup a query to pull year to date budget info? I have a
table (tbl
budget) that contains budget information by project. I have "month"
as a
field name so each project is enter 12 times in the table (1 for each
month).
I will need to obtain year to date budget info throughout the year
next year.
I am not sure how to specify the query to pull a certain time line as
the
year progresses.

SELECT [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID
Pook],
[tbl budget].[Project Name], Sum([tbl budget].[Budget Amount]) AS
[SumOfBudget Amount]
FROM [tbl budget] INNER JOIN [tbl period] ON [tbl budget].Month =
[tbl
period].Month
GROUP BY [tbl budget].Group, [tbl budget].Subgroup, [tbl budget].[ID
Pook],
[tbl budget].[Project Name];

 

[raskew via AccessMonster.com]

Hi -

Both Month and Date are reserved words and shouldn't be used as field names.
To avoid problems in the future, you might ought to rename these fields now.

Bob

that's a short year <g>

Pieter

Hi,

[quoted text clipped - 36 lines]

Pook],
[tbl budget].[Project Name];

 

[Guest]

Hi Pieter,

Just an example.

Bob

Generally reserve words don't allow there use anywhere other than as they
are defined, however, I tested the above and it did work. Now I have to
agree that using these word is a bad idea but it does seem to work in my
tests.

--
Cheers,
Shane Devenshire

Hi -

Both Month and Date are reserved words and shouldn't be used as field names.
To avoid problems in the future, you might ought to rename these fields now.

Bob

that's a short year <g>

Pieter

Hi,

[quoted text clipped - 36 lines]

Pook],
[tbl budget].[Project Name];

 

[raskew via AccessMonster.com]

Shane -

Not surprised that your tests work. Problem is that somewhere down the line
you may develop a function that makes use of a reserved word used as a field
name and it will just confuse Access to death and you'll end up with
erroneous results and a massive correction project. That's why I suggested
you not use reserved words -- both Month and Date are the names of functions.

Good luck.

Bob

Hi Pieter,

Just an example.

Bob

Generally reserve words don't allow there use anywhere other than as they
are defined, however, I tested the above and it did work. Now I have to
agree that using these word is a bad idea but it does seem to work in my
tests.

Hi -

[quoted text clipped - 12 lines]

Pook],
[tbl budget].[Project Name];

 

[Pieter Wijnen]

It works, I never implied anything else, but you must be damn sure to always
prefix or [] when you use them

Pieter

Hi Pieter,

Just an example.

Bob

Generally reserve words don't allow there use anywhere other than as they
are defined, however, I tested the above and it did work. Now I have to
agree that using these word is a bad idea but it does seem to work in my
tests.

--
Cheers,
Shane Devenshire

Hi -

Both Month and Date are reserved words and shouldn't be used as field
names.
To avoid problems in the future, you might ought to rename these fields
now.

Bob

that's a short year <g>

Pieter

Hi,

[quoted text clipped - 36 lines]
Pook],
[tbl budget].[Project Name];